×
INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS

Log In

Come Join Us!

Are you an
Engineering professional?
Join Eng-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Eng-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

Creating sequential filenames
2

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.

RE: Creating sequential filenames

2
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 <> "" '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

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

RE: Creating sequential filenames

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.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Eng-Tips Forums free from inappropriate posts.
The Eng-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Eng-Tips forums is a member-only feature.

Click Here to join Eng-Tips and talk with other members!


Resources