A long time ago we posted a tip for getting a random file name that doesn't exist in a given directory. That has worked well for us for years, but we decided to update it. There were a couple of things we didn't like about the old function. The first was that you were restricted to one file name. This is fine for 99% of the times you want to use it, but if you needed a couple different file extensions, then it meant a couple of calls to the function. This new version allows you to get a file name that doesn't exist for one or more file extensions. The second thing we didn't like is that you had to remember to NOT include the "." in your extension. If you included the ".", when the function returned you would have consecutive periods in the file name. Again, not a serious limitation, but the new function doesn't care if you include the period in the file extension or not.

The old version of the function returned the full path of the random file name (directory plus file name plus extension). We didn't want to break any of our existing code that used the old version. But when you pass in an array of file extensions, then the return value in the old version doesn't fit. So we decided that if one extension was passed (just like the old version), then the full path would be returned. But if you passed in an array of file extensions, then the directory plus the file name would be returned (the calling code would have to add the extension to the end).

This new version also does not care if any of the extensions start out with a period or not. If the period is missing, it will be added. That check didn't exist in the old version.

Without further ado, here is version 2 of the RandomFileName script library.

Function RandomFileName(inpDir As String, ext As Variant) As String
   ' Pass in the name of a directory, build a random file name, make sure the file does not exist
   ' in the directory.   Note that ext can be a single string or an array of strings. If it's an array,
   ' the random file name will not match with any of the extensions and the value returned will
   ' be MISSING THE EXTENSION. If it's a single string, the random file name will not match
   ' with that extension and the FULL PATH INFORMATION will be returned.
   Dim directory As String
   Dim slash As String
   Dim checkFileName As String
   Dim fileName As String
   Dim i As Integer
   Dim num As Integer
   Dim ch As String
   
   ' The "ext" parameter can either be a single value or an array. Make sure the first character
   ' is a period - makes things easier later on
   If Isarray(ext) Then
      For i = Lbound(ext) To Ubound(ext)
         If ext(i) <> "" Then ' Could be looking for a blank extension, so check for that
            If Left(ext(i), 1) <> "." Then ext(i) = "." & ext(i)
         End If
      Next
   Else ' Not an array. Is a single value
      If ext <> "" Then ' Could be looking for a blank extension
         If Left(ext, 1) <> "." Then ext = "." & ext
      End If
   End If
   directory = inpDir ' We don't want to change the passed-in parm, so make a local copy of the value
   If Instr(directory, "/") = 0 And Instr(directory, "\") = 0 Then
      RandomFileName = "" ' No slashes. Unknown file system setup - return empty string
      Exit Function ' ======== EXIT ============
   End If
   If Instr(directory, "/") <> 0 And Instr(directory, "\") <> 0 Then
      RandomFileName = "" ' Both slashes. Unknown file system setup - return empty string
      Exit Function ' ======== EXIT ============
   End If
   If Instr(directory, "/") <> 0 Then slash = "/" Else slash = "\"
   ' Make sure the directory exists first
   If Right(directory, 1) <> slash Then directory = directory & slash ' Format dir so it ends in a slash
   On Error Resume Next
   checkFileName = Dir$(directory & "*.*") ' Look for something in the directory
   On Error Goto 0
   If Err <> 0 Then ' Will give a "path not found" error if the path doesn't exist
      Err = 0
      RandomFileName = ""
      Exit Function ' ======== EXIT ============
   End If
   Randomize
   fileName = "" ' Get us into the while loop
   While fileName = ""
      For i = 1 To 8 ' Random file name will have 8 characters
         If i = 1 Then num = Rnd * 52 Else num = Rnd * 62 ' starts w/ a letter, but other chars can be nums
         Select Case num
         Case 0 To 25 : fileName = fileName & Chr$(Asc("A")+num) ' 0...25 = upper case letter
         Case 26 To 51 : fileName = fileName & Chr$(Asc("a")+num-26) ' 26...52 = lower case letter
         Case 52 To 61 : fileName = fileName & Cstr(num-52) ' 52...61 = number
         End Select
      Next
      If Isarray(ext) Then
         For i = Lbound(ext) To Ubound(ext)
            checkFileName = Dir$(directory & fileName & ext(i)) ' See if the file exists
            If checkFileName <> "" Then ' If found, continue the while loop
               i = Ubound(ext) + 1 ' Get out of the inner For loop
               fileName = "" ' Continue the outer While loop
            End If
         Next
      Else ' Not an array - a single string was passed in
         checkFileName = Dir$(directory & fileName & ext) ' See if the file exists
         If checkFileName <> "" Then fileName = "" ' If found, continue the while loop
      End If
   Wend
   ' At this point, the random file name doesn't exist in the directory. To make this compatible
   ' with the old version of the function, return the full path if only one extension was passed
   ' in. If multiple extensions were passed in, return the directory plus file name (w/ no extension)
   If Isarray(ext) Then
      RandomFileName = directory & fileName
   Else
      RandomFileName = directory & fileName & ext
   End If
End Function