Are you automating annual Workbooks, monthly PDF reports, or producing any other dynamic files with Excel? Your program needs to automatically save each file in a folder. Here is how you can construct the full path and filename with a couple of VBA functions
What is a dynamically constructed file name?
When you save a file, you need to point to a particular folder and give it a name.
Your 2022 P&L Workbook may have the following path and file name: \\Shared-Server\Financials\P&L\2022.xlsm
Instead of you manually browsing to the destination folder and typing a filename when you create a new file, you can write VBA code to automate this process and construct the file name for you based on dynamic parameters, such as: customer number or name, report date (and even time), etc.
You may want to arrange the reports by years in folders, one folder per year. In this case, your program would also need to create the new folder upon saving the first report for the year.
The destination path usually starts off a root maintained by the user in a Worksheet cell, a VBA constant, or an environment variable.
For example, if our root folder for reports is designated to be: C:\Reports, our program may create the folder 2022 under this root to store customers reports for year 2022, with the customer name and date as part of the file name, resulting in the following destination:
Of course, I could create folders per customers, not per years, and any other combination I desire to best serve my business needs.
You can see that the customer name was also stripped of any spaces and preceded also by the customer number as it is maintained in the system. I pad the customer number with zeros for sorting the files in the folder properly and for visual clarity.
When do we need to dynamically construct file names?
The following are all examples of cases in which I needed to construct file names as part of projects producing reports of all sorts:
- A PDF report is needed to be temporarily stored on disk before it can be emailed (see how to create PDF reports with Excel VBA here).
- A customer PDF report needs to be archived for future reference.
- A customer lab results data-set needs to be archived for future retrieval and processing.
- A monthly financial report needs to be saved as a separate Excel Workbook, generated from the main tool for managing financials.
- Official documents produced by the solution must be muted on disk as PDF reports with timestamp for 7 years for regulatory reasons.
Perhaps you can identify your case in the above list or add other use-cases to it. One way or another, saving files from Excel programs is a much-needed task, and today you are going to have the VBA code to do just that – properly.
Preparing the required folder
Following the above example of full path and file name, we need to construct the following path and have the function return this path as a string to its calling function:
Our root folder, C:\Reports, is stored in a Worksheet cell with a range name of “Reports_Folder_Root”.
The function receives the report year as an argument; however it can be calculated in any other way, of course. I often construct an array with the customer report information and pass over the array as a pointer to the function handling the report.
For clarity and brevity, I omitted error handling, however they may be needed here as well.
Function ConstructReportFolder(intYear As Integer) As String
Dim strFolder As String
strFolder = ThisWorkbook.Names("Reports_Folder_Root").RefersToRange.Value
If (Right(strFolder, 1) <> Application.PathSeparator) Then strFolder = strFolder & Application.PathSeparator
strFolder = strFolder & Format(intYear, "0000")
If Not DirectoryExists(strFolder) Then
ConstructReportFolder = strFolder & Application.PathSeparator
As I cannot be sure if the user specified the root folder with a closing path separator (backslash in Windows, colon in MacOS), I’m adding it if necessary, before further constructing the path I need.
I’m formatting the year to always occupy 4 digits even though years always take 4 characters nowadays, just as a consistent practice when constructing file names.
If needed, I am creating the folder with the MkDir command. How do I know if it is needed? I am calling a function that checks if the folder exists. Here it is:
Function DirectoryExists(Directory As String) As Boolean
On Error Resume Next
If Not Dir(Directory, vbDirectory) = vbNullString Then
If (GetAttr(Directory) = vbDirectory) Or (GetAttr(Directory) = (vbDirectory + vbArchive)) Then
DirectoryExists = True
On Error GoTo 0
I’m enclosing the call to the operating system within On Error Resume Next and On Error GoTo 0 to avoid error messages popping up if the folder is not found – I just need the True/False response to shoot back from this function.
Also, I’m using the GetAttr VBA function to verify the folder is actually a folder. I’m also accepting a folder that is flagged as vbArchive, meaning, it has changed since last time.
Constructing the File Name
Recall that in our example, the following function should construct this file name:
It is comprised of the customer number, customer name and report date.
In addition to the zero-padding trick for the customer number, we need to be prepared to handle all sorts of customer names that the database may throw at us.
For that, I am using a function to transform a file name I constructed into a legal file name, without illegal characters in it, to avoid errors coming back from the operating system.
I can call this function with any character to replace illegal characters or use the default underscore.
I took the advantage of working the filename in this function to also allow for elimination of spaces (or replacing them with anything else), although they are not illegal as far as the operating system is concerned.
Here are the two functions that return a beautiful and legal file name for us:
Function ConstructReportFileName(lngCustomerNumber As Long, _
strCustomerName As String, _
Optional strExtension As String = "pdf") As String
Dim strFileName As String
Dim datToday As Date
datToday = Date
strFileName = Format(Trim(CStr(lngCustomerNumber)), "000000")
strFileName = strFileName & "-" & LegalFileName(strCustomerName)
strFileName = strFileName & "-" & Year(datToday) & "-"
& Format(Month(datToday), "00") & "-" &
strFileName = strFileName & "." & strExtension
ConstructReportFileName = strFileName
Function LegalFileName(strFileName As String, _
Optional strReplaceIllegalsWith As String = "_", _
Optional strReplaceSpaceWith As String = vbNullString) As String
Dim i As Integer
Const strIllegals = "\/|?*<>"":"
LegalFileName = strFileName
For i = 1 To Len(strIllegals)
LegalFileName = Replace(LegalFileName, Mid(strIllegals, i, 1), strReplaceIllegalsWith)
LegalFileName = Replace(LegalFileName, " ", strReplaceSpaceWith)
Putting it all together
With the two functions to construct a path and a filename available for us, we can write the function that handles the next step, probably something like that:
Function SaveCustomerReport(lngCustomerNumber As Long, _
strCustomerName As String, _
intReportYear As Integer) As Boolean
Dim strPath As String
Dim strFileName As String
strPath = ConstructReportFolder(intReportYear)
strFileName = ConstructReportFileName(lngCustomerNumber, strCustomerName)
FunctionToDoSomethingWithFile (strPath & strFileName)
By the way, in this Blog post I show how to save an Excel Worksheet as a PDF report, it works hand-in-hand with the file name you can now construct dynamically!
For temporary storage of files, I must provide a folder, but I don’t really need a folder as it is just serving a temporary store of the file until it is consumed in the next process step. In such cases, I opt to use Windows’ temporary folder that we can obtain by calling the following:
strFolder = Environ("Temp")
And now, I ask you to dynamically share this Blog post far and wide, so that we can encourage more constructive Excel solutions in this world.
WANT TO LEARN PROGRAMMING WITH EXCEL VBA?
I created a complete 8-course program for you.
From programming to databases with Excel VBA to deliver business-grade solutions.
Take the first course today: Computer Programming with Excel VBA.
Click the button to see the complete program and start now!