Excel/VBA writing

Advertisement


Question
Hi Tom

I recorded the follwing macro.

Sub ssaveandclosefile()
'
' ssaveandclosefile Macro
'

'
   ActiveWorkbook.Save
End Sub


I wasw hoping that whe I ran it the Save/Do not save/cancel dialog box would pop up;  it doesn't.

Could you tell me where I have gone worng and possibly adjust the code?

Thanks in advance

Chris

Answer
Chris,

the macro recorder doesn't record your interaction with the dialog.  It only records the result as you see.  

You can display the dialog with

ans = Application.GetSaveAsFilename(filefilter:=" Excel Files (*.xls?), *.xls?")

this dialog does nothing but return either false (if you cancel) or the fully qualified filename to use to save the file.  You have to test for what it returned and take the action you want.  

so looking at your code:




this works if you want to do a SAVE or save the file with a different name.  It will also drop the dialog and do nothing if you click cancel.  If you save with a new name, it will save in Sub ssaveandclosefile()
'
' ssaveandclosefile Macro
'

'
Dim ans as Variant
ans = Application.GetSaveAsFilename(filefilter:=" Excel Files (*.xls?), *.xls?")
if ans = False then
 exit sub
else
 Application.DisplayAlerts = False
 Thisworkbook.SaveAs filename:=ans, fileformat:=thisworkbook.Fileformat
 Application.DisplayAlerts = True
End if
End Subthe same format as the existing file.  I used Thisworkbook as I assume you want this code to work with the workbook that contains the code.  You can replace ThisWorkbook with ActiveWorkbook if you want to work on whatever workbook is active.

If you just want to either save the workbook as you recoded, you can simplify the code like this:  

Sub ssaveandclosefile()
'
' ssaveandclosefile Macro
'

'
Dim ans as Variant
ans = Application.GetSaveAsFilename(filefilter:=" Excel Files (*.xls?), *.xls?")
if ans = False then
 exit sub
else
 Application.DisplayAlerts = False
 Thisworkbook.Save
 Application.DisplayAlerts = True
End if
End Sub

The displayalerts=False removes any warning that you are overwriting an existing file with the selected name.

Using this version, if you change the name or location in the dialog, it will be ignored.  It will just save the workbook if you don't click cancel.

--
Regards,
Tom Ogilvy  
About Excel
This topic answers questions related to Microsoft Excel spreadsheet (or workbook) stand-alone or Mircrosoft Office Excel including Excel 2003, Excel 2007, Office 2000, and Office XP. You can get Excel help on Excel formulas(or functions), Excell macros, charting in Excel, advanced features, and the general use of Excel. This does not provide a general Excel tutorial nor the basics of using a spreadsheet. It provides specific answers to using Microsoft Excel only. If you do not see your Excel question answered in this area then please ask an Excel question here

Excel

All Answers


Answers by Expert:


Ask Experts

Volunteer


Tom Ogilvy

Expertise

Selected as an Excel MVP by Microsoft since 1999. Answering Excel questions in Allexperts since its inception in 2001. Able to answer questions on almost all aspects of Excel's internal capabilities. If seeking a VBA solution, please specify that in your question itself so I give you the answer you want. [Excel has weak protection - if you are distributing an application, I don't answer questions on how to protect your project from your users.]

Experience

Extensive experience.

Education/Credentials
Master of Science (MS) degree Operations Research (ORSA)

Awards and Honors
Microsoft MVP in Excel.

©2016 About.com. All rights reserved.