Excel VBA Rename existing file

[cstimart]

I have some code which will look for a specific file in a given location. Is it possible to use VBA to rename that file without opening/re-saving and killing the specific file?

 

[Rick Rothstein]

You can use VB's Name..As statement to do that... you have to provided full paths to old and new filenames (this command allows you to move files to different directories as long as those directories already exist). For what you asked, here is a generic example...

GivenLocation = "c:\temp\" 'note the trailing backslash
OldFileName = "SomeFileName.xls"
NewFileName = "DifferentFileName.xls"
Name GivenLocation & OldFileName As GivenLocation & NewFileName

The parts in bold lettering are the required syntax of this command.

 

[cstimart]

Awesome. Thanks!

 

[cstimart]

You can use VB's Name..As statement to do that... you have to provided full paths to old and new filenames (this command allows you to move files to different directories as long as those directories already exist). For what you asked, here is a generic example...

GivenLocation = "c:\temp\" 'note the trailing backslash
OldFileName = "SomeFileName.xls"
NewFileName = "DifferentFileName.xls"
Name GivenLocation & OldFileName As GivenLocation & NewFileName

The parts in bold lettering are the required syntax of this command.

I am able to use this to rename a file and keep it in the same folder. However, I just tried to use it to move the file to another folder and I'm having issues.

I ran the code and the file disappeared from the original folder, but does not appear in the destination folder. I tried again and the code stops, indicating the file already exists...but it doesn't.

    PrevMonth = DateAdd("m", -2, Date)
    Saved_File = TARGET_DIR & "Trial Count " & Format(PrevMonth, "mm - yyyy") 
    BackUp_File = TARGET_DIR & Year(PrevMonth) & " Archive\" & "Trial Count " & Format(PrevMonth, "mm - yyyy") 
        If Dir(Saved_File & ".xls") <> "" Then
            Name Saved_File & ".xls" As BACKUPFILE & ".xls"
        ElseIf Dir(Saved_File & ".orig.xls") <> "" Then
            Name Saved_File & ".orig.xls" As BACKUPFILE & ".orig.xls"
        End If

 

[Rick Rothstein]

I ran the code and the file disappeared from the original folder, but does not appear in the destination folder. I tried again and the code stops, indicating the file already exists...but it doesn't.

    PrevMonth = DateAdd("m", -2, Date)
    Saved_File = TARGET_DIR & "Trial Count " & Format(PrevMonth, "mm - yyyy") 
    BackUp_File = TARGET_DIR & Year(PrevMonth) & " Archive\" & "Trial Count " & Format(PrevMonth, "mm - yyyy") 
        If Dir(Saved_File & ".xls") <> "" Then
            Name Saved_File & ".xls" As BACKUPFILE & ".xls"
        ElseIf Dir(Saved_File & ".orig.xls") <> "" Then
            Name Saved_File & ".orig.xls" As BACKUPFILE & ".orig.xls"
        End If

What value is assigned to TARGET_DIR? What version of Windows are you using?

 

[cstimart]

What value is assigned to TARGET_DIR? What version of Windows are you using?

TARGET_DIR = "O:\xxxx\xxxxxxx\xxxxx\Trial Count\"

I am using Windows XP here at work.

I was able to reverse the NAME AS line to recover the file back to the original directory.

 

[Rick Rothstein]

TARGET_DIR = "O:\xxxx\xxxxxxx\xxxxx\Trial Count\"

I am using Windows XP here at work.

I was able to reverse the NAME AS line to recover the file back to the original directory.

Well, I'm glad you got the file back doing that... it shows the Name..As statement actually does work (although I do not know why you could not find or see the moved file). I should have asked you this in my first message... what did you assign to BACKUPFILE?

 

[cstimart]

what did you assign to BACKUPFILE?

Solved!!! Should be "BackUp_File". D'OH!!

Thanks!

 

[gdmerioles]

Hello cstimart.... is it possible to copy your code? I am dying to create a code to rename my files.

 

[pratmacro]

Well, I'm glad you got the file back doing that... it shows the Name..As statement actually does work (although I do not know why you could not find or see the moved file). I should have asked you this in my first message... what did you assign to BACKUPFILE?

Hi,

i have to rename current excel which excel i am using without writting the path of excel file. is this possible? and i don't want to rename whole file. for example if file name is "Hello" than file name will rename as "hello you are". is this possible? please help me on this.