the file already exists - do you want to replace the existing file?

[Paul James]

I've got a VBA procedure that saves a record to disk using
Application.GetSaveAsFilename. However, if the file it's trying to save
already exists, the Windows API generates the above message, and the user's
only option is to either replace the existing file, or manually rename the
existing file in the File Save As.

What I'd really like to do is modify the code so that when it's run, it
takes care of renaming the existing file (if it exists) by adding a "1" to
the end of the filename, then saving the file in memory with the original
name, all without requiring the user to do anything. For simplicity, I'd be
happy to keep appending the character "1" to the end of any previously
renamed files in case this operation is performed multiple times, instead of
trying to increase the appended digit by 1 each time.

Is there any VBA code I can use to accomplish this?

Thanks in advance.

Paul

 

[Bill Manville]

I've got a VBA procedure that saves a record to disk using
Application.GetSaveAsFilename. However, if the file it's trying to save
already exists, the Windows API generates the above message, and the user's
only option is to either replace the existing file, or manually rename the
existing file in the File Save As.

Something along these lines:

Dim V
Dim stOther As String
Dim iVer As Integer
V = Application.GetSaveAsFilename(.....)
If TypeName(V)="Boolean" Then Exit Sub ' cancelled
' does it exist?
If Dir(V)<>"" Then
' yes
stOther = V
' remove extension if present
If Right(LCase(V),4)=".xls" Then stOther=Left(stOther,Len(stOther)-4)
' find an unused numerical suffix to the filename
iVer = 1
Do While Dir(stOther & iVer & ".xls")<>""
iVer = iVer + 1
Loop
' rename the existing file
Name V As stOther & iVer & ".xls"
End If
' save the current file
ActiveWorkbook.SaveAs V

Bill Manville
MVP - Microsoft Excel, Oxford, England
No email replies please - reply in newsgroup

 

[Paul James]

Great code, Bill - works just fine. It looks like more than a little
thought went into creating it.

Thank you so much for sharing it with me and the rest of the group.


For the benefit of others who may be interested in this procedure, I wrote
the following line:

Name V As stOther & iVer & ".xls"

as

V = stOther & iVer & ".xls"

and the parameters I used for GetSaveAsFilename(.....) are:

GetSaveAsFilename("MyData.xls", filefilter:="Excel Files (*.xls),
*.xls")

Paul

 

[Bill Manville]

For the benefit of others who may be interested in this procedure, I wrote
the following line:

Name V As stOther & iVer & ".xls"

as

V = stOther & iVer & ".xls"

OK - I thought your aim was to rename the existing file.
Your modification changes the name of the file you are saving to.

Bill Manville
MVP - Microsoft Excel, Oxford, England
No email replies please - reply in newsgroup

 

[Paul James]

Actually, Bill, you had it exactly right - my preference was to rename the
existing file. But when I first tried running your original code, I got an
error message. So I rewrote it as I described in my last message, and at
least it ran, although not exactly as desired.

However, your follow up message inspired me to take another shot at getting
the "Name V As stOther . . . " line to work, and, voila!!! It works just as
you originally wrote it in your message, and it does exactly what I had
hoped to accomplish: it renames the existing file on disk, and saves the
open file with the specified file name. I don't know what I was doing the
first time to cause the error, but it works fine now.

So many thanks again.

Paul