Change File Name On Network Drive

[Espy]

Hi, I'm trying to rename a file with VBA so that the original file and the reviewed file are on the network drive in the same folder.

P:\Compliance\4000\RD2\RG Round 2.xls is compared to P:\Compliance\4000\RD1\RG Round 1.xls to identify which cells have changed.

P:\Compliance\4000\RD2\RG Round 2.xls should then be saved as P:\Compliance\4000\RD2\Checked RG Round 2.xls so that we have the original and the version with changed cells highlighted.

The code I'm using is failing at ActiveWorkbook.SaveAs.

Sub ChangeFileName()
 
    Dim sFileNameChange As String
    Dim sFile3 As String
    sFileNameChange = ActiveWorkbook.FullName
    sNewFileSplit = Right(sFileNameChange, Len(sFileNameChange) - InStrRev(sFileNameChange, "\"))
    sNewPathSplit = Left(sFileNameChange, Len(sFileNameChange) - InStrRev(sFileNameChange, "\"))
    sNewFileSplit2 = "Checked " & sNewFileSplit
    sNewPathSplit2 = sNewPathSplit & "\"
    sFile3 = sNewPathSplit2 & sNewFileSplit2
    ActiveWorkbook.SaveAs Filename:=sFile3, FileFormat:=xlExcel8
 
End Sub

Thanks in advance.

Steve

 

[Gary McMaster]

I didn't actually let it attempt the save, but your code shows me a double backslash in the middle of the filename when I add the MsgBox as shown below.

Gary

Sub ChangeFileName()
 
    Dim sFileNameChange As String
    Dim sFile3 As String
    sFileNameChange = ActiveWorkbook.FullName
    sNewFileSplit = Right(sFileNameChange, Len(sFileNameChange) - InStrRev(sFileNameChange, "\"))
    sNewPathSplit = Left(sFileNameChange, Len(sFileNameChange) - InStrRev(sFileNameChange, "\"))
    sNewFileSplit2 = "Checked " & sNewFileSplit
    sNewPathSplit2 = sNewPathSplit & "\"
    sFile3 = sNewPathSplit2 & sNewFileSplit2
    
    [COLOR=Red]MsgBox sFile3[/COLOR]

    ActiveWorkbook.SaveAs Filename:=sFile3, FileFormat:=xlExcel8
    
End Sub

 

[Espy]

I didn't actually let it attempt the save, but your code shows me a double backslash in the middle of the filename when I add the MsgBox as shown below.

Gary

Sub ChangeFileName()
 
    Dim sFileNameChange As String
    Dim sFile3 As String
    sFileNameChange = ActiveWorkbook.FullName
    sNewFileSplit = Right(sFileNameChange, Len(sFileNameChange) - InStrRev(sFileNameChange, "\"))
    sNewPathSplit = Left(sFileNameChange, Len(sFileNameChange) - InStrRev(sFileNameChange, "\"))
    sNewFileSplit2 = "Checked " & sNewFileSplit
    sNewPathSplit2 = sNewPathSplit & "\"
    sFile3 = sNewPathSplit2 & sNewFileSplit2
 
    [COLOR=red]MsgBox sFile3[/COLOR]
 
    ActiveWorkbook.SaveAs Filename:=sFile3, FileFormat:=xlExcel8
 
End Sub

Thanks - I don't get the double backslash when I run it (I added the MsgBox to check the file name).

 

[Gary McMaster]

I'm using XL 2000. If I send sFile3 to the debug window I get:

Brand new file (never been saved):

Book1\Checked Book1


File saved as "Book1.xls" in "C:\Temp"

C:\Temp\\Checked Book1.xls

I don't know why it would be different on another machine unless it has something to do with the Excel version.


Maybe try it like this:

ThisWorkbook.SaveAs Filename:=ThisWorkbook.Path & "\" & "Checked " & ThisWorkbook.Name, FileFormat:=xlExcel8

Also double check, or maybe even eliminate, "FileFormat"

Gary