VBA Removing Spaces from File Names

[LakeSumter]

Hi,

I used to work with someone who could do this stuff...he did tell me about this site before we lost touch though!

I have a directory where people send me files & photos for my magazine, I need to turn them round and submit them on our corporate FTP site sometimes I may have 20-50 files in each clients directory. The website has rules:

No spaces being the most abused one.

I will be happy to navigate into a directory and then run the macro with the current directory before moving on to the next one.

The File Names need to have all the spaces removed (and possibly some other characters (#/) etc please note these files are not Excel files so this may cause some problems.

Thanks for any help you can give...

 

[venkat1926]

there is a third party softare
batch rename.exe

try this url

http://www.stintercorp.com/br.php?app=BatchRenameEXE1048


venkat

 

[wongm003]

Here is a way to do it in excel...

Public fso As Variant

Public Sub rename_files()

    Set fso = CreateObject("Scripting.FileSystemObject")
    Set fs = Application.FileSearch

    'search for files
    With fs
        .NewSearch
        .LookIn = ActiveWorkbook.Path
        .SearchSubFolders = True
        .FileType = msoFileTypeAllFiles
        .Execute

        For x = 1 To .FoundFiles.Count
            If .FoundFiles(x) <> ActiveWorkbook.FullName Then Call clean_name(.FoundFiles(x))
        Next x
    End With

    MsgBox "DONE"

End Sub

Private Sub clean_name(strName)
On Error Resume Next
    Dim arUnwanted As Variant
    Dim varFile As Variant
    Dim varName As Variant
    Dim strFileName As String
    Dim strPath As String
    Dim newFileName As String
    Dim x As Integer
    Dim y As Integer

    'unwanted characters... add if needed
    arUnwanted = Array(" ", "#")

    varFile = Split(strName, "\", -1, vbTextCompare)
    
    strFileName = varFile(UBound(varFile))
    strPath = Left(strName, Len(strName) - Len(strFileName))
    newFileName = strFileName

    For x = 0 To UBound(arUnwanted)
        'check to see if filename has unwanted characters
        If InStr(1, newFileName, arUnwanted(x), vbTextCompare) > 0 Then
            varName = Split(newFileName, arUnwanted(x), -1, vbTextCompare)
            'determine new file name
            newFileName = ""
            For y = 0 To UBound(varName)
                newFileName = newFileName & varName(y)
            Next y
        End If
    Next x
    
    'exit sub if no change needed
    If newFileName = strFileName Then Exit Sub
    
    'exit sub and do not rename if new file name already exists
    If fso.FileExists(strPath & newFileName) Then Exit Sub

    'change file name... comment out the if - if you don't want to confirm name change
    If MsgBox(strFileName & " -> " & newFileName, vbYesNo) = vbYes Then _
        Name (strPath & strFileName) As (strPath & newFileName)

End Sub