VBA Code to automate "Save As"

  • Thread starter Thread starter Kevin McLean
  • Start date Start date
K

Kevin McLean

I would like to use a "Save As" VBA code to save a file
already open to another drive location. An example of my
code is below. Everytime this code runs it asks for
confirmation "yes/no" if I'd like to override the existing
file. I will always want to override it and would like a
line of VBA code to take care of that. Any ideas?

Thank you so much!

Kevin
 
ActiveWorkbook.SaveAs Filename:= _
"H:\GRPSHARE\Intra-Day Performance.xls" _
, FileFormat:=xlNormal, Password:="",
WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False,
ConflictResolution:=xlLocalSessionChanges
 
Kevin,

You can set Application.DisplayAlerts to False prior to the SaveAs to
prevent the dialog from appearing. E.g.,

Application.DisplayAlerts = False
' your code here
Application.DisplayAlerts = True
 
This is an alternate to changing the DisplayAlerts property.

Check if the open file has the same name as the saveas filename. If it
does, then you can always use the Save command.

If ActiveWorkbook.FullName <> sNewFullName Then
'Your code here...
'Save new workbook
ActiveWorkbook.SaveAs filename:=sNewFullName
Else
'Your code here...
'Save workbook
ActiveWorkbook.Save
End If

Bill Barclift
 
Back
Top