Renaming excel file with "save as" macro

By eshtica ·

I have a short excel sheet where different departments have to input some data.

I have a "save as" macro so that we have a standard/sequential file name:

Sub saveas()
If MsgBox("blablabla", vbYesNo) = vbNo Then Exit Sub
With Application
.DefaultFilePath = "C:"
End With
ThisFile = Range("E40").Value
FileDrive = Range("E41").Value
ActiveWorkbook.saveas Filename:=FileDrive & ThisFile & ".xls", _
FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False

End Sub

E41 has the standard path written (a shared drive).
E40 has a formula that creates a filename from the data entered in the sheet.

As this filename may change a bit everytime a different Dept. enters pieces of data, saving it every time with this macro is a good tool for showing what the completeness status is for these forms, just by having a glance at the filename.

My problem is that this generates a lot of duplicate files, since it only creates a new file, not replacing the old one.
I have:
...where I just wanted to have the 3xxxxxx.xls, which is the most recent one.

is it possible to have this macro work in a way so that if the filename has changed it just replaces the current name instead of duplicating?

Thanks in advance,

This conversation is currently closed to new comments.

Thread display: Collapse - | Expand +

All Answers

Collapse -

re: Is it possible........

by ThumbsUp2 In reply to Renaming excel file with ...

You can't use the Save As to replace the currently opened file. The file has to be closed to replace it. You need to just use Save, not Save As, if you want to replace the current name.

Collapse -

thanks but...

by eshtica In reply to re: Is it possible....... ...

Ok, that is helpful, but how can I rename a file? How could I adapt that code to do that?

Related Discussions

Related Forums