Creating sequential filenames
Creating sequential filenames
(OP)
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.
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.





RE: Creating sequential filenames
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 <> "" '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 = "C:\Data\Excel\Sht" & Format(i + 1, "000") & ".xls"
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.
RE: Creating sequential filenames
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.
RE: Creating sequential filenames
Cheers,
Joerd
Please see FAQ731-376 for tips on how to make the best use of Eng-Tips.