Constructing a Dynamic File Name with Excel VBA

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:

C:\Reports\2022\0001-TheEpochTimes-2022-01-15.pdf

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:

C:\Reports\2022\

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

        MkDir (strFolder)

    End If  

    ConstructReportFolder = strFolder & Application.PathSeparator

End Function

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

        End If

    End If

    On Error GoTo 0

End Function

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:

0001-TheEpochTimes-2022-01-15.pdf

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") & "-" &
Format(Day(datToday), "00")

    strFileName = strFileName & "." & strExtension

    ConstructReportFileName = strFileName

End Function

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)

    Next i

    LegalFileName = Replace(LegalFileName, " ", strReplaceSpaceWith)

End Function

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)

    ...

End Function

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.