Remove characters from filenames in a folder

[scottb]

Hi everyone.
Not sure if this is the appropriate forum but I thought I saw the ability to do something similar with Access/VBA.
I have a folder that contains >100 files with file names that include multiple # characters. Is it possible to remove the # from all the file's filenames in the directory with VBA?
I thought I would start here.
Thank you for any assistance.
-Scott

[HansV]

I have moved this thread from the Access forum to the VB/VBA forum since it is a general VBA question, not specific to Access.
I'll post a reply soon.

[HansV]

Try this. The macro can be run from any Office application.

Code: Select all

Sub RemoveHash()
    Dim strFolder As String
    Dim strFile As String
    ' Modify as needed, keep trailing backslash
    strFolder = "C:\MyFiles\"
    strFile = Dir(strFolder * "*.*")
    Do While strFile <> ""
        If InStr(strFile, "#") Then
            Name strFolder & strFile As strFolder & Replace(strFile, "#", "")
        End If
        strFile = Dir
    Loop
End Sub

[scottb]

Thanks Hans I will give it a try.

[scottb]

Hi Hans,
I updated the path and tried to run in Excel. I received a run time error 13 type mismatch.

Sub RemoveHash()
Dim strFolder As String
Dim strFile As String
' Modify as needed, keep trailing backslash
strFolder = "C:\Users\OIK6179\Documents\Project Services\Aurora-Backup\"
strFile = Dir(strFolder * "*.*")
Do While strFile <> ""
If InStr(strFile, "#") Then
Name strFolder & strFile As strFolder & Replace(strFile, "#", "")
End If
strFile = Dir
Loop
End Sub

Thanks.

[HansV]

Which line caused the error?

[scottb]

It doesn't flag any line.

[HansV]

Try clicking Debug in the error message.

[scottb]

In the error message dialog box there is only ok and help. When I debug/step into from the menu the first line Sub RemoveHash() is highlighted.

[HansV]

In the Visual Basic Editor, select Tools > Options...
Activate the General tab.
In the Error Trapping section, select 'Break in Class Module'.
Click OK.
Now try to run the code again. Does the error message now have a Debug button?

[scottb]

Yes it did and stopped on:
strFile = Dir(strFolder * "*.*")

[HansV]

Thanks - my mistake. That line should have been:

Code: Select all

    strFile = Dir(strFolder & "*.*")

Sorry about that!

[scottb]

Thank you very much for your help Hans. This is going to save many hours of tedious work.