3 Ways to Generate Temporary File Names in VBA

Mike Wolfe -

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.  

  1. GetTempFileName: Windows API function
  2. TempFileName: my function for creating user-friendly temp file names
  3. 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!):

TempFileName()

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 .txt opens in Notepad by default
' --== Sample Default Output ==--
C:\Users\Mike\AppData\Local\Temp\temp.txt
C:\Users\Mike\AppData\Local\Temp\temp_001.txt

GetGuidBasedTempPath()

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 .tmp emphasizes 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

Referenced articles

Getting a Temporary File Name with VBA
Here is a simple function to generate an unused temporary file name in VBA.
No Longer SetMike Wolfe
A GUID-Based Temporary File Name Generator
If you need a reliable and fast way to create lots of temporary file paths, a GUID-based filename generator has some compelling benefits.
No Longer SetMike Wolfe

Image by Тарик Тристар from Pixabay