Tuesday, July 7, 2020

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.

No comments:

Post a Comment

I'd love to hear from you!
-Nick