Question Locked

Renaming excel file with "save as" macro

0 helpful
3 replies
Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.

Hello,

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
Sheets("Sheet1").Select
ThisFile = Range("E40").Value
FileDrive = Range("E41").Value
ActiveWorkbook.saveas Filename:=FileDrive & ThisFile & ".xls", _
FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False
Range("A1").Select

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:
1xxxxxx.xls
2xxxxxx.xls
3xxxxxx.xls
...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,
Tiago

3 replies

by thumbsup2

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.

by eshtica

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

This discussion is closed to new replies.