3 Ways to Generate Temporary File Names in VBA
Here are three options for generating temporary file names in VBA. Which is best? As with many programming questions, the answer is, "It depends."
Generating a temporary file name is such a common requirement in code that you would expect the Windows API to have such a function built in. And it does. But it sucks:
- Supports a maximum of 65,535 unique file names for a given set of inputs.
- Performs poorly when creating a large number of files with the same prefix.
How do I know this? Because the documentation says so!
65,535 Unique File Name Limit:
Only the lower 16 bits of the uUnique parameter are used. This limits GetTempFileName to a maximum of 65,535 unique file names if the lpPathName and lpPrefixString parameters remain the same.
Poor Performance at Scale:
Due to the algorithm used to generate file names, GetTempFileName can perform poorly when creating a large number of files with the same prefix. In such cases, it is recommended that you construct unique file names based on GUIDs.
Three Options to Generate Temp File Names
In addition to the Windows API function mentioned above, I've also published two different custom functions to generate temporary file names.
GetTempFileName: Windows API function
TempFileName: my function for creating user-friendly temp file names
GetGuidBasedTempPath: my function for creating GUID-based temp file names
And there are surely dozens more out on the interwebs.
Which Temporary File Name Function to Use?
If the Windows API has a built-in temp file name function and I already had one custom temp file name function, why did I feel the need to create yet another temp file name function?
It comes down to what you want to use it for. I'll list the comparative advantages of my two functions. Pick whichever one makes the most sense for your needs (or combine elements of both to make your own...that's the beauty of programming!):
Use the TempFileName() function when you want user-friendly file names:
- Custom folders
- Custom file name prefixes
- Much shorter file names
- Default file extension of
.txtopens in Notepad by default
' --== Sample Default Output ==-- C:\Users\Mike\AppData\Local\Temp\temp.txt C:\Users\Mike\AppData\Local\Temp\temp_001.txt
Use the GetGuidBasedTempPath() function when you want performance and reliability:
- Efficient because it doesn't need to check to see if the file already exists
- Can quickly create hundreds or thousands of file names
- Avoids race condition where two processes could generate the same file name
- Default file extension of
.tmpemphasizes temporary nature of the file
' --== Sample Default Output ==-- C:\Users\Mike\AppData\Local\Temp\165916D1-6F29-D64F-A98F-F97458A6BB19.tmp C:\Users\Mike\AppData\Local\Temp\AD1B0A12-CD75-D54E-9D8A-4CE0FB747F3C.tmp
Image by Тарик Тристар from Pixabay