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

chas1

New Member
Joined
Oct 1, 2010
Messages
12
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

MrExcel MVP
Joined
Mar 10, 2004
Messages
19,013
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.

Code:
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

New Member
Joined
Oct 1, 2010
Messages
12
Hi Domenic,<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:eek:ffice:eek:ffice" /><o:p></o:p>
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:p></o:p>
thanks
chas
 

Domenic

MrExcel MVP
Joined
Mar 10, 2004
Messages
19,013
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

Well-known Member
Joined
Feb 28, 2003
Messages
2,615
Hi

Replace

Code:
MyFile = Dir(MyFolder & "*.docx")
with

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

chas1

New Member
Joined
Oct 1, 2010
Messages
12
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

Code:
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

MrExcel MVP
Joined
Mar 10, 2004
Messages
19,013
For each .doc file in the specified folder, the file is renamed with the first 10 characters removed...

Code:
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

New Member
Joined
Oct 1, 2010
Messages
12
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

MrExcel MVP
Joined
Mar 10, 2004
Messages
19,013
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

New Member
Joined
Oct 1, 2010
Messages
12
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.
 

Forum statistics

Threads
1,082,344
Messages
5,364,812
Members
400,814
Latest member
gangstar67

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top