Results 1 to 4 of 4
  1. #1
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    austin, Texas, USA
    Posts
    1,029
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Using an Array for a File Rename (Office 2000 SR-1, Excel2000)

    I have been working on a complex Excel project that produces several new pages (with auto-generated names) based on user interaction. The intent of the workbook is to store X number of staffings and follow-up forms for a month of staffing activity. I have set up a Save Workbook macro to pull up the Save As... dialog box with a pre-written name for the workbook, which is StaffForms[MonthDate]. What I'd like to do is add the Case Numbers for all the initial staffings generated in the file name so people can tell what cases are stored in a particular workbook.

    There is a region on a sheet that has each case number listed in a single column. I'd like to iterate thru the case number block and write out a string like so: [case number], [case number], [case number]...[MonthDate]

    Any suggestions on how to do this? I can reference the array and loop thru it no problem but can't figure out how to pull out the cell contents and concatenate a string. CONCATENATE does not seem to exist in VB (doesn't come up in the Object Browser) and the Concatenate formula works sorta but you can place ", " or any other text stuff into the formula.


    TIA

  2. #2
    2 Star Lounger
    Join Date
    May 2002
    Location
    Midland, Michigan, USA
    Posts
    133
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Using an Array for a File Rename (Office 2000 SR-1, Excel2000)

    Here's what I understand: You wish to add the case numbers and MonthDate to your file name.

    Assuming the case numbers are located in the range A1:A12, I'd use this code to concatenate the case numbers:

    <pre>Sub Test()

    Dim sMyString As String
    Dim i

    sMyString = Cells(1, 1).Value
    For i = 2 To 12
    sMyString = sMyString & ", " & Cells(i, 1).Value
    Next i

    End Sub
    </pre>


    Now sMyString contains all of the case numbers separated by a comma and a space. For you, it is just a matter of appending MonthDate via something like:
    <pre>sMyString = sMyString & ", " & YourMonthDateVariable</pre>


    HTH

  3. #3
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Using an Array for a File Rename (Office 2000 SR-1, Excel2000)

    for example:
    Cell contents of A1 concantenated with cell contents of A2

    Combined = range("a1").value & range("a2").value

    The ampersand (&) is the concatenation operator in VB and in excel.

    Steve

  4. #4
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    austin, Texas, USA
    Posts
    1,029
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Using an Array for a File Rename (Office 2000 SR-1, Excel2000)

    thanks - worked beautifully.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •