CODEWITHSUNDEEP
vba
Suraj
Suraj

Reputation: 305

Not able to rename file using FSO ( file has space in its name)

I have this small code to rename a file with name "XYZ (43).xlsx" to "XYZ (43)-07/02/2020.xlsx"

I have tried using FSO.Move, FSO.movefile, Name as but none of them work. I suspect its because of space in the name. Can some one help me here ?

Const SourcePath As String = "C:\Users\a\Desktop\Suraj\Imp\"
        Const DestinationPath As String = "C:\Users\a\Desktop\Suraj\personal\"
        Set FSO = CreateObject("scripting.filesystemobject")
        Set Directory = FSO.GetFolder(SourcePath)
        Set files = Directory.files
        Set RegExp = CreateObject("VBScript.Regexp")
        RegExp.Pattern = "XYZ \([0-9]+\).xlsx"
        For Each eachfile In files
            fileName = FSO.getFileName(eachfile)
            Nameonly = FSO.GetBaseName(fileName)
            newfilename = "" & Nameonly & "-" & Date
            If RegExp.Test(fileName) Or fileName = "XYZ.xlsx" Then
                Set file = FSO.GetFile(eachfile)
                NewFile = SourcePath & newfilename
                msgbox Dir(eachfile)
                FSO.moveFile Source:="" & SourcePath & fileName, Destination:=SourcePath & newfilename
            End If
        Next eachfile

Upvotes: 0

Views: 204

Answers (1)

BigBen
BigBen

Reputation: 50162

The problem isn't the space, but the foward slashes in Date in the following line:

 newfilename = "" & Nameonly & "-" & Date

The / is an invalid character in file names:

Perhaps use Format$ to change the forward slashes to dashes, for example.

 newfilename = "" & Nameonly & "-" & $Format(Date,"dd-mm-yyyy")

(though you don't need the leading "" &).

Upvotes: 1

Related Questions