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:
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.
- The user selects the folder where to save the Excel documents
- The macro saves the open workbook into the folder with a “ZZZ” placeholder in the filename
- The macro counts the number of Excel files in the folder.
- The macro adds the control number based on this count into the spreadsheet
- 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
- The macro deletes the first copy the Excel document
Here’s a rundown of the VBA code:
'Optimize Macro Speed
Application.ScreenUpdating = False
Application.EnableEvents = False
Application.Calculation = xlCalculationManual
Dim Wb2 As Workbook
Set Wb2 = ThisWorkbook
'---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 the program so we don’t delete the template file
‘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()
'---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
Wb2.ActiveSheet.Range("G7").Value = FileCount
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
‘delete the old workbook
'Reset Macro Optimization Settings
Application.EnableEvents = True
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
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.