VBA code to save file with ID in file name... (1 Viewer)

sjl

Registered User.
Local time
Today, 09:48
Joined
Aug 8, 2007
Messages
221
Folks,

I have a Button control at the top of my worksheet which, when clicked, will save the file with the ID included in the file name (ID sits in cell D9).

My problem is that if the file already exists, a Microsoft window pops up and says "A file named X already exists. Do you want to replace it?"

If I say yes, no problem.
If I say no, a window comes up that says
"Runtime error '1004'. Cannot access file"
and gives user the choice of End, or Debug.

How can I get VBA to gracefully allow a user to say "No" (no, I don't want to replace the file)?

This is my code:

Public Sub SaveAsA1()

If Len(Dir("C:\ifed", vbDirectory)) = 0 Then
MkDir "c:\ifed"
End If

ThisFile = Range("D9").Value

ActiveWorkbook.SaveAs Filename:="C:\IFED\Calculator_ " & [ThisFile] & ".xlsm"
'ActiveWorkbook.SaveAs Filename:="C:\IFED\Calculator_ThisFile.xlsm"
End Sub
 

pbaldy

Wino Moderator
Staff member
Local time
Today, 06:48
Joined
Aug 30, 2003
Messages
36,118
You can use Dir() to test for the file, and only run the save if it does not already exist.
 

Users who are viewing this thread

Top Bottom