Using Excel VBA to rename files in a folder

[SimonR]

Hi,

I am trying to come up with a code to rename hundreds of PDFs in a folder. I see this question has been asked a number of times before, but the requests have differed from mine, and I am not skilled enough in VBA to understand any of the scripts that have been posted and modify them accordingly. What's required is very simple in theory and should be possible in practice.

So, basically the files I'm trying to rename have long names starting with an 11-character alphanumeric string - a12345bcd67. I need to rename each file to the string "abcde123 " (single space) combined with the first 11 characters of the original file name, so for example "a12345bcd67xxxxxxxxxxxxxxxxxxx" becomes "abcde123 a12345bcd67". Can anyone advise?

 

[Joe4]

So, basically the files I'm trying to rename have long names starting with an 11-character alphanumeric string - a12345bcd67. I need to rename each file to the string "abcde123 " (single space) combined with the first 11 characters of the original file name, so for example "a12345bcd67xxxxxxxxxxxxxxxxxxx" becomes "abcde123 a12345bcd67". Can anyone advise?

This does not make much sense to me, as you would be naming ALL of the PDFs in that folder "abcde123 a12345bcd67", so each one would overwrite the previous, and you would be left with exactly one single PDF file in that folder named "abcde123 a12345bcd67".

That cannot be right, can it?

 

[SimonR]

This does not make much sense to me, as you would be naming ALL of the PDFs in that folder "abcde123 a12345bcd67", so each one would overwrite the previous, and you would be left with exactly one single PDF file in that folder named "abcde123 a12345bcd67".

No (sorry if I wasn't clear), only the first nine characters of each file should be the same. The second part is the first 11 characters taken from the original file name - it is going to be different in every case (this is work-related and I have simply renamed both parts to a generic alphanumeric string for security reasons).

 

[Joe4]

OK, I think I get it now. It doesn't matter what the current file name is, you just want to tack on this 9 character prefix to it.
So I modified the code found here (VBA Loop Through PDF Files in a Folder or Directory | VBAF1) to do what you want:

Sub MyRenameFiles()
   
    'Variable Declaration
    Dim sFilePath As String
    Dim sFileName As String
    Dim prfx As String
    Dim oName As String
    Dim nName As String
       
    'Specify File Path
    sFilePath = "C:\Temp\Test"
   
    'Specify new prefix
    prfx = "abcde123 "
   
    'Check for back slash
    If Right(sFilePath, 1) <> "\" Then
        sFilePath = sFilePath & "\"
    End If
       
    sFileName = Dir(sFilePath & "*.pdf")
   
'   Loop through all PDF files in folder
    Do While Len(sFileName) > 0
        If (Right(sFileName, 3) = "pdf") And (Left(sFileName, Len(prfx)) <> prfx) Then
            'Get full path and name of file
            oName = sFilePath & sFileName
            'Build new path and name
            nName = sFilePath & prfx & sFileName
            'Rename file
            Name oName As nName
        End If
        'Set the fileName to the next available file
        sFileName = Dir
    Loop
   
    MsgBox "Macro complete!"
  
End Sub

Just change the file path and prefix values to suit your situation.