Rename Files With Random Number/Number of Files

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.
G ScottAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Rey Obrero (Capricorn1)Commented:
replace this part

strfile = Dir(FILEPATH)


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


strfile = Dir(FILEPATH & "*.avi")
Jeffrey CoachmanMIS LiasonCommented:
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:

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.
Newly released Acronis True Image 2019

In announcing the release of the 15th Anniversary Edition of Acronis True Image 2019, the company revealed that its artificial intelligence-based anti-ransomware technology – stopped more than 200,000 ransomware attacks on 150,000 customers last year.

G ScottAuthor Commented:
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.
G ScottAuthor Commented:
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.

[ fanpages ]IT Services ConsultantCommented:
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


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
G ScottAuthor Commented:
I got that to work, thanks fanpages.
[ fanpages ]IT Services ConsultantCommented:
You're very welcome.

Thanks for closing the question.


It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.