Removing an apostrophy ' from the front of filename

[whatzzup]

I have over 200 excel files that has a apostrophy infron of the file name and wanted to remove them, the filename is currently like this '4123 Flooring, '1211 Window etc., and i want them to be 4123 Flooring and 1211 Window respectively, is there a way to change them in a simple way without doint it one at a time?

 

[Richard Schollar]

Hi

Where are all these files? Are they in one individual folder?

 

[whatzzup]

Yes they are in one folder....

 

[Jon von der Heyden]

Try;

Public Sub RenameFile()
    Const strPath As String = "C:\Test\"
    Dim strFileName As String
 
    strFileName = Dir$(strPath)
 
    Do While Len(strFileName) > 0
        If strFileName Like "*'*" Then
            Name strPath & strFileName As strPath & Replace$(strFileName, "'", "")
        End If
        strFileName = Dir$
    Loop
End Sub

Change the directory path to the path that the files are in.

 

[whatzzup]

How is this done at a folder level? Meaning all the files are in one folder, does this mean i need to open them individually and run this macro?

 

[Jon von der Heyden]

Copy the code to a standard module in a new workbook.
Change this line to point to the folder path with all the files.

Const strPath As String = "C:\Test\"

Hit the run button. All filenames should be changed. No need to open them. In fact, none of them should be open at all.

 

[whatzzup]

Sorry if i am been a bit slow but the file is named '4001 Book2.xls, so what do i do in that case?

 

[whatzzup]

his is how i wrote it:

Public Sub RenameFile()
Const strPath As String = "C:\'4001 Book2.xls\"
Dim strFileName As String

strFileName = Dir$(strPath)

Do While Len(strFileName) > 0
If strFileName Like "*'*" Then
Name strPath & strFileName As strPath & Replace$(strFileName, "'", "")
End If
strFileName = Dir$
Loop
End Sub

 

[JackDanIce]

I think Jon is asking what is the path name of the folder only (not the name of the file).

If the file you wanted updated is called:

'4001 Book2.xls

And it is located in:

C:\

Then your code would be:

Public Sub RenameFile()
    Const strPath As String = "C:\"
    Dim strFileName As String
 
    strFileName = Dir$(strPath)
 
    Do While Len(strFileName) > 0
        If strFileName Like "*'*" Then
            Name strPath & strFileName As strPath & Replace$(strFileName, "'", "")
        End If
        strFileName = Dir$
    Loop
End Sub

 

[whatzzup]

This doesn't work at all, the location is c:\ and filename is '4001 Book2.xls