We help IT Professionals succeed at work.

Rename Files  With Random Number/Number of Files

G Scott
G Scott asked
Ok, so here is what I need to do. I have a folder with 8 avi files in it. Files are named 1.avi, 2.avi...etc. I want to rename those files so that file 1.avi will be a random file in the folder. I also want to be able to add files with the same naming convention and have it rename as many files as I put in there. So basically I just want to scramble the files in the folder.

I thought maybe having a table with the filenames in a column and then a random number column. It would just rename the filename to the random number. But I can't figure it out.
This is the code I found that I have been messing with:

Private Sub Command0_Click()

Const FILEPATH As String = _

Dim strfile As String
Dim filenum As String

strfile = Dir(FILEPATH)

Do While strfile <> ""
  Debug.Print strfile
  If Right$(strfile, 3) = "avi" Then
    filenum = Mid$(strfile, Len(strfile) - 6, 3)
    Name FILEPATH & strfile As FILEPATH & filenum & ".avi"
  End If

  strfile = Dir


End Sub

But it gives me the old 'Filename already exists." error. Which makes sense. I just can't figure out how to do this. Thanks for any help you can give me on this. It is very puzzling.
Watch Question

Top Expert 2016

replace this part

strfile = Dir(FILEPATH)


strfile = Dir(FILEPATH & "*.*")


strfile = Dir(FILEPATH & "*.avi")
Jeffrey CoachmanMIS Liason
Most Valuable Expert 2012

Please clearly define "Scramble"

If there are 8 files:
...then scrambling them might be:

Or scrambled might mean:
here, the issue with "Random" numbers is that you need *non-repeating* random numbers (dupe names are not allowed in the same folder)
So you need a system to check to see in numbers are being duplicated...

FWIW a simple formula to generate a random number might be something like this:

Top Expert 2014

You can also use the GetTempName method of the FileSystem object to generate a unique file name for a directory.  It is an alphanumeric value.

You can generate a GUID through an API call or through the scriptlet typelib object.
Example from an Immediate window:
Set TypeLib = CreateObject("Scriptlet.TypeLib")
{40464BF0-22C0-47A0-BA26-651F7D1A49FD} 1

?Mid(TypeLib.Guid,2,36), Left(TypeLib.Guid,38)
40464BF0-22C0-47A0-BA26-651F7D1A49FD      {40464BF0-22C0-47A0-BA26-651F7D1A49FD}

Open in new window

Since this is an Access question, you could create a table to track your AVI files and use a Replication ID type of autonumber key.  These are GUID values.


Hey Jeff, what I mean by scramble is this. Let's say I have 8 video files.
1.avi = a cat video
2.avi = a dog video
3.avi = a car video

When I open them in VLC  they play in order 1 - 8. I can 'shuffle' from within VLC and that works fine. The only problem is it ALWAYS starts with the 'cat' video and then shuffles the rest. I just want to rename the actual files so that 1.avi is a different video (possibly). I have found out that manually renaming them works and changes the lead video. This way I am sort of doing VLC's shuffle function from within VBA.

Does that make sense? That is probably THE worst example I have ever given.

Thanks for any help.

aikimark - I will look in to that when I am at home with the DB in question. Thanks.


Hey capricorn1, that didn't work. Maybe after my poor sample you understand at least what I am trying to do.

I don't remember the error. I will post it when I get home.

Please place the sample code, the Public Rename_Files_In_Folder() subroutine, in a Public code module & change the line referring to the (String) Constant strFolder (the folder where your ".avi" files are stored.

I have used "c:\E-E Folder\" as an example.
Please do not forget to add the trailing (suffix) folder separator character "\" to the end of the string.

Run the routine & note the output in the Visual Basic for Applications "Immediate" window to see what file operations are occurred on your individual files.

Also please note that there is an assumption that your ".avi" files are numbered sequentially; started at 1 (& in increments of a single unitary number).


Option Explicit
Public Sub Rename_Files_In_Folder()

  Dim lngNumber                                         As Long
  Dim lngFile()                                         As Long
  Dim strFile()                                         As String
  Dim strFilename                                       As String
  On Error GoTo Err_Rename_Files_In_Folder
  Const strFolder                                       As String = "c:\E-E Folder\"
  Const strFile_Extension                               As String = "avi"
  Erase lngFile
  ReDim lngFile(0&) As Long
  Erase strFile
  ReDim strFile(0&) As String
  strFilename = Dir$(strFolder & "*." & strFile_Extension, vbNormal)
  While Not ((Len(Trim$(strFilename))) = 0)
      ReDim Preserve strFile(UBound(strFile) + 1&) As String
      strFile(UBound(strFile)) = strFilename
      strFilename = Dir$()
  If UBound(strFile) > 0& Then
     ReDim lngFile(UBound(strFile)) As Long
     While lngFile(0&) < UBound(strFile)
         lngNumber = CLng(Rnd() * UBound(strFile)) + 1&
         If lngNumber > 0& And _
            lngNumber <= UBound(strFile) Then
            If lngFile(lngNumber) = 0& Then
               lngFile(0&) = lngFile(0&) + 1&
               lngFile(lngNumber) = lngFile(0&)
            End If
         End If
     For lngFile(0&) = 1& To UBound(strFile)
         Debug.Print "Name " & vbTab & _
                     Chr$(34) & strFolder & strFile(lngFile(0&)) & Chr$(34) & vbTab & _
                     " As " & vbTab & _
                     Chr$(34) & strFolder & "New_" & CStr(lngFile(lngFile(0&))) & "." & strFile_Extension & Chr$(34)
         Name strFolder & strFile(lngFile(0&)) As strFolder & "New_" & CStr(lngFile(lngFile(0&))) & "." & strFile_Extension
     Next lngFile(0&)
     For lngFile(0&) = 1& To UBound(strFile)
         Debug.Print "Name " & vbTab & _
                     Chr$(34) & strFolder & "New_" & CStr(lngFile(0&)) & "." & strFile_Extension & Chr$(34) & vbTab & _
                     " As " & vbTab & _
                     Chr$(34) & strFolder & CStr(lngFile(0&)) & "." & strFile_Extension & Chr$(34)
         Name strFolder & "New_" & CStr(lngFile(0&)) & "." & strFile_Extension As strFolder & CStr(lngFile(0&)) & "." & strFile_Extension
     Next lngFile(0&)
  End If

  On Error Resume Next
  Erase lngFile
  ReDim lngFile(0&) As Long
  Erase strFile
  ReDim strFile(0&) As String

  Exit Sub

  MsgBox "ERROR #" & CStr(Err.Number) & _
         vbCrLf & vbLf & _
         Err.Description, _
         vbExclamation Or vbOKOnly, _

  Resume Exit_Rename_Files_In_Folder
End Sub

Open in new window


I got that to work, thanks fanpages.
You're very welcome.

Thanks for closing the question.