save / saveas specific filename.....with a difference!

[michael.beckinsale]

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

 

[NickHK]

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]

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

 

[Bob Phillips]

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)

 

[NickHK]

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]

Bob / Nick,

Sorry typo on my part.

Can l take it that the UI indicates UserInterface?

 

[NickHK]

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]

Nick / Bob,

Many thanks. Code does exactly as required.

Regards

Michael Beckinsale