Rename Excel Workbook Based on Number of Files Macro

Let’s say you have a task where you want to include a control number in an Excel sheet and in the name of the file which is based on the total number of Excel files contained within a folder. If you’re not familiar with writing VBA macros for Excel, see my getting started guide here.

For this example, there are currently three Excel files saved in a folder with these names:

Doc-Control-01

Doc-Control-02

Doc-Control-03

We want to save our latest document using the name Doc-Control-04, but how does Excel know to use “04” at the end?

As with any complicated task, the first thing I do is break it down into smaller, more manageable steps.

1. The user selects the folder where to save the Excel documents
2. The macro saves the open workbook into the folder with a “ZZZ” placeholder in the filename
3. The macro counts the number of Excel files in the folder.
4. The macro adds the control number based on this count into the spreadsheet
5. The macro saves a copy of the workbook in the same folder but replaces the placeholder  “ZZZ” in the file name with the control number count
6. The macro deletes the first copy the Excel document

Here’s a rundown of the VBA code:

Sub Generate_Documents()

'Optimize Macro Speed

  Application.ScreenUpdating = False

  Application.EnableEvents = False

  Application.Calculation = xlCalculationManual

 

Dim Wb2 As Workbook

Set Wb2 = ThisWorkbook

ThisWorkbook.Sheets(1).Select

Wb2.Activate

'---The control number will be in cell G7. For now we input placeholder “ZZZ”

Wb2.ActiveSheet.Range("G7").Value = “ZZZ”

'----save the Excel document VBA----

'dialog box to save the spreadsheet

Dim InitialName As String

Dim fileSaveName As Variant

InitialName = “Control Number: “ & Wb2.ActiveSheet.Range("G7").Value

fileSaveName = Application.GetSaveAsFilename(InitialFileName:=InitialName, fileFilter:="Excel Files (*.xlsx), *.xlsx")

If fileSaveName = False Then

        MsgBox "File not saved. You must save the file!"

        Exit Sub

       ‘exit the program so we don’t delete the template file

        Else

        Wb2.SaveAs Filename:=fileSaveName

        End If

       

‘now the macro knows what folder to look in to count the files

'Next, create the Control number by counting excel files in the folder and rename

'count existing excel files in the save folder

'First, get the location WB2 was just saved in

Dim WB2Path As String

WB2Path = Wb2.Path

Dim FileCount As Integer

Dim FolderPath2 As String, path2 As String

FolderPath2 = Wb2.Path

path2 = FolderPath2 & "\*.xls*"

    Filename = Dir(path2)

FileCount = 0

    Do While Filename <> ""

       FileCount = FileCount + 1

        Filename = Dir()

    Loop

    'MsgBox FileCount

   

     

    '---control number. If less than 10 add a 0 to display 01, 02, etc.

If FileCount < 10 Then

Wb2.ActiveSheet.Range("G7").Value = "0" & FileCount

Else

Wb2.ActiveSheet.Range("G7").Value =  FileCount

End If

Dim OldName As String, newName As String

OldName = Wb2.FullName

‘use the replace function to automatically replace placeholder “ZZZ” with the document control number

newName = Replace(OldName, "ZZZ", Right(Wb2.ActiveSheet.Range("G7").Value, 2))

‘save a copy of the sheet with the control number in the file name

Wb2.SaveAs Filename:=newname

‘delete the old workbook

Kill OldName

'Reset Macro Optimization Settings

    Application.EnableEvents = True

    Application.Calculation = xlCalculationAutomatic

    Application.ScreenUpdating = True

End Sub

Let me know in the comments below if you have any questions how to rename a workbook based on the number of files in a folder.