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

Topic

New Reply

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

Reply | Quote

Replies

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:

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

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:

sMyString = sMyString & ", " & YourMonthDateVariable

HTH

Reply | Quote

thanks – worked beautifully.

Reply | Quote

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

Reply | Quote