Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
save / saveas specific filename.....with a difference!
Hi All,
Can anybody tell me how to save or save as a specific filename when the the user uses the built in Excel menu functionality ? (ie File, SaveAs or Save or clicking on the Save icon) All help gratefully received. Regards Michael Beckinsale |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
save / saveas specific filename.....with a difference!
Michael,
The _BeforeSave event will fire. But you will need to run your code first as the dialog is shown after this. So you need : Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) Dim RetVal As Variant RetVal = Application.GetSaveAsFilename("Default FileName.xls") If RetVal < False Then ThisWorkbook.SaveAs RetVal End If 'Don't want to show the .SaveAs dialog now Cancel = True End Sub If you have fixed filename that must be used, then there is no need to show the dialog. NickHK "michael.beckinsale" wrote in message ups.com... Hi All, Can anybody tell me how to save or save as a specific filename when the the user uses the built in Excel menu functionality ? (ie File, SaveAs or Save or clicking on the Save icon) All help gratefully received. Regards Michael Beckinsale |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
save / saveas specific filename.....with a difference!
Nick, Many thanks for the quick response. I'm not sure that i understand your response. Are you saying that when the user uses file,saveas or file,save or the save icon that is built into the Excel menu bar that triggers the Before_Close event and therefore by adding code l can change its behaviour? If that is the case how does Excel know what functionality the user has chosen. (ie SaveAs triggers the SaveAs dialog whereas Save simply saves the workbook in its current location giving a warnign if it already exists) I need to ensure that whatever method the user chooses to save the file the default name generated by the file is used. TIA Regards Michael beckinsale |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
save / saveas specific filename.....with a difference!
No he means BeforeSave as he said.
That event has a SaveAsUI argument that informs whether SaveAs has been invoked. -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "michael.beckinsale" wrote in message ups.com... Nick, Many thanks for the quick response. I'm not sure that i understand your response. Are you saying that when the user uses file,saveas or file,save or the save icon that is built into the Excel menu bar that triggers the Before_Close event and therefore by adding code l can change its behaviour? If that is the case how does Excel know what functionality the user has chosen. (ie SaveAs triggers the SaveAs dialog whereas Save simply saves the workbook in its current location giving a warnign if it already exists) I need to ensure that whatever method the user chooses to save the file the default name generated by the file is used. TIA Regards Michael beckinsale |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
save / saveas specific filename.....with a difference!
Michael ,
Any call to .Save or .SaveAs (from Excel or your own code) will trigger this event, provided Application.EnableEvents=True (It's the _BeforeSave event, not _BeforeClose, but I assume that is a typo.) You can tell if it is a .SaveAs or .Save by checking the SaveAsUI argument of the event and react accordingly. So if you need to ensure a certain filename, enforce your own .Save/.SaveAs and Cancel the one that Excel is in the process of executing. Also, to prevent the event firing when you perform your own save, set Application.EnableEvents=False ThisWorkbook.saveAs Filename Application.EnableEvents=True If you try to .SaveAs with a path/filename that already exists you will receive and error that you can trap. NickHK "michael.beckinsale" wrote in message ups.com... Nick, Many thanks for the quick response. I'm not sure that i understand your response. Are you saying that when the user uses file,saveas or file,save or the save icon that is built into the Excel menu bar that triggers the Before_Close event and therefore by adding code l can change its behaviour? If that is the case how does Excel know what functionality the user has chosen. (ie SaveAs triggers the SaveAs dialog whereas Save simply saves the workbook in its current location giving a warnign if it already exists) I need to ensure that whatever method the user chooses to save the file the default name generated by the file is used. TIA Regards Michael beckinsale |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
save / saveas specific filename.....with a difference!
Bob / Nick, Sorry typo on my part. Can l take it that the UI indicates UserInterface? |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
save / saveas specific filename.....with a difference!
Michael,
Yes. The UI may appear in 2 situations: - The file has never been saved and .Save is called - .SaveAs is called NickHK "michael.beckinsale" wrote in message ups.com... Bob / Nick, Sorry typo on my part. Can l take it that the UI indicates UserInterface? |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
save / saveas specific filename.....with a difference!
Nick / Bob, Many thanks. Code does exactly as required. Regards Michael Beckinsale |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
SaveAs with a pre-defined filename | Excel Programming | |||
SaveAs using two cells for filename | Excel Discussion (Misc queries) | |||
FileName SaveAs | Excel Programming | |||
'ActiveWorkbook.SaveAs Filename' | Excel Programming | |||
SaveAs Filename Help | Excel Programming |