rename all files in a folder removing first 10 characters of name

[chas1]

Hi I need code to rename all word doc files in a folder. I require to remove the first 10characters from the file name for the new filename.
Can anyone help me on this. (I am currently doing it via the command prompt to list all files and then generating the new filename through excel and putting the new list of names back into the command prompt to rename file.) I'm guessing this could be done more neatly through VBA code.Thanks in advance.

 

[Domenic]

The following code also checks to make sure that the file name contains more than 10 characters (not including the file extension). If a file name does not contain more than 10 characters, it will appear in a message box.

Option Explicit

Sub RenameDocFiles()

    Dim MyFolder As String
    Dim MyFile As String
    Dim Temp1 As String
    Dim Temp2 As String
    Dim NewFileName As String
    
    'Change the path accordingly
    MyFolder = "C:\Users\Domenic\Desktop\temp\"
    
    'Change the file filter (.docx) accordingly
    MyFile = Dir(MyFolder & "*.docx")
    
    Do While Len(MyFile) > 0
        Temp1 = Left(MyFile, InStr(1, MyFile, ".doc") - 1)
        If Len(Temp1) > 10 Then
            NewFileName = Mid(MyFile, 11)
            Name MyFolder & MyFile As MyFolder & NewFileName
        Else
            Temp2 = Temp2 & vbLf & MyFile
        End If
        MyFile = Dir
    Loop
    
    If Temp2 <> "" Then
        MsgBox "File names less than 10 characters:  " & vbLf & Temp2
    End If
    
End Sub

 

[chas1]

Hi Domenic,<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
i tried entering your code and it doesn't work. I changed the path and doc filter but it doesn’t do anything to the filename. I am using office 2003 and windows XP. Do you know why this may not be working.<o></o>
thanks
chas

 

[Domenic]

Can you provide the following?

1) A few samples of the file names in the folder.

2) The new file name desired for each of those sample file names.

3) The exact code used.

 

[Krishnakumar]

Hi

Replace

MyFile = Dir(MyFolder & "*.docx")

with

MyFile = Dir(MyFolder & "*.doc*")

 

[chas1]

Hi Domenic,

the files are named as follows for example
172846793.SA122135-18.doc
172846795.SA122135-20.doc

just need to retain everything after the dot i.e. new name would be something like SA122135-18.doc

code i have saved to my normal template under modules - newmacros

Sub RenameDocFiles()
    Dim MyFolder As String
    Dim MyFile As String
    Dim Temp1 As String
    Dim Temp2 As String
    Dim NewFileName As String
 
    'Change the path accordingly
    MyFolder = "C:\Documents and Settings\oregac1\Desktop\test dhr\test"
 
    'Change the file filter (.docx) accordingly
    MyFile = Dir(MyFolder & "*.doc")
 
    Do While Len(MyFile) > 0
        Temp1 = Left(MyFile, InStr(1, MyFile, ".doc") - 1)
        If Len(Temp1) > 10 Then
            NewFileName = Mid(MyFile, 11)
            Name MyFolder & MyFile As MyFolder & NewFileName
        Else
            Temp2 = Temp2 & vbLf & MyFile
        End If
        MyFile = Dir
    Loop
 
    If Temp2 <> "" Then
        MsgBox "File names less than 10 characters:  " & vbLf & Temp2
    End If
 
End Sub

 

[Domenic]

For each .doc file in the specified folder, the file is renamed with the first 10 characters removed...

Option Explicit

Sub test()

    Dim FileSys As Object
    Dim Files As Object
    Dim File As Object
    Dim Folder As String
    
    Folder = "C:\Documents and Settings\oregac1\Desktop\test dhr\test\"
    
    Set FileSys = CreateObject("Scripting.FileSystemObject")
    Set Files = FileSys.GetFolder(Folder).Files
    
    For Each File In Files
        If InStr(1, File, ".doc") > 0 Then
            Name File As Folder & Mid(File.Name, 11)
        End If
    Next File
    
End Sub

 

[chas1]

Hi Domenic,
this worked the first time i tried it put when i tried it a second time it was stopping on the below line also got a file already exists error. why would this happen. is it possible to add code so that it prompts user to select location of their folder as well

Name File As Folder & Mid(File.Name, 11)

thanks
Charlotte.

 

[Domenic]

A few questions...

1) Does the file in fact already exist when running the code for the second time?

2) When running the code for the second time, does the folder only include files that need the first 10 characters of their file name removed?

2) Do we first need to check whether a file already exists before a file is renamed? If one already exists, what should happen? Do you want a message box to pop up with a list of those files?

 

[chas1]

thanks domenic i deleted the folder that i was using to test this out and tried testing it a few times again. works fine now. (must have been something left in the folder that i couldnt see.)
is there a line of code i can add so the user can select there folder when executing the macro rather then having to put it into the code itself.

thanks.