Creating sequential filenames 2

[bpeirson]

How can I get an Excel macro to create a unique filename for a a workbook generated from a template file.

The best situation is that file names created have a common prefix (from a cell or userform) followed by a number for how many files with that prefix have been previously saved.

The macro should also save the workbook after the filename has been generated.

Thanks.

 

[joerd]

I have written some code for you that should accomplish what you want. It uses the Dir command to get the filenames matching a certain pattern (Sht001.xls, Sht002.xls etc in my example). These are stored in a temporary worksheet (in case you have files out of sequence, like 1,2,4,5,8 ), and then adds 1 to the highest number. The active workbook is then saved with the new number.

Sub Main()
Dim F As String, i As Integer, n As Integer, wks As Worksheet

    'Initialize
    i = 1
    Set wks = ActiveWorkbook.Worksheets.Add 'dummy worksheet to hold the file list
    wks.Cells(i, 1).Value = F
    'Get the first filename that matches the pattern
    F = Dir("C:\Data\Excel\Sht*.xls", vbNormal)
    Do While F <> &quot;&quot; 'loop through all the files
        'store the filename in a sheet
        wks.Cells(i, 1).Value = F
        i = i + 1
        F = Dir     'get the next filename
    Loop
    n = i - 1       'n is the number of files found
    'sort the list of files
    wks.Range(Cells(1, 1), Cells(n, 1)).Sort _
        Key1:=wks.Cells(1, 1), Order1:=xlAscending, _
        OrderCustom:=1, Orientation:=xlSortRows, _
        Header:=xlNo, MatchCase:=False
    'retrieve the name of the highest numbered file
    F = wks.Cells(n, 1).Value
    i = Val(Mid(F, 4, 3))
    'clean up (throw away the temporary worksheet)
    Application.DisplayAlerts = False
    wks.Delete
    Application.DisplayAlerts = True
    'generate the new name (add 1 to the highest number and save
    F = &quot;C:\Data\Excel\Sht&quot; & Format(i + 1, &quot;000&quot;) & &quot;.xls&quot;
    ActiveWorkbook.SaveAs Filename:=F
End Sub

Feel free to modify it!

Cheers,
Joerd

Please see FAQ731-376 for tips on how to make the best use of Eng-Tips.

 

[bpeirson]

Thanks for the code.

I was not expecting that much help, it's really appreciated. I am currently teaching myself VBA and I guess that it would have taken me a few days to reach this point.

The book I have on VBA does not contain much about objects and events specific to Excel and the version of Excel I have was installed without a complete set of help files for VBA commands. This copy of Excel is at work so it's not easy to get the CD to install the extra help files.

 

[joerd]

You can try a search for VBAXL9.CHM to find the help file for vba in excel 2000 (or VBAXL8.CHM for XL97, VBAXL10.CHM for XL2003)

Cheers,
Joerd

Please see FAQ731-376 for tips on how to make the best use of Eng-Tips.