Compromise between GetSaveAsFilename and xlDialogSaveAs

EricSmith

New Member
Joined
Aug 23, 2007
Messages
7
I'm writing an Excel (XP-2007) add-in for which I need to execute code at a couple of very specific points in time whenever a user saves a workbook:

1. When the user is saving and full path to the save location is known, but before the save actually happens.
2. When the save has successfully completed.

My strategy is to handle the before save event, provide the correct save user interation myself (such as in the case of a Save As), and thereby have hooks to be able to do stuff at the right time.

The problem is that neither of the two methods I know of for displaying a Save As dialog give me the hooks above without removing Excel functionality.

GetSaveAsFilename works perfectly to give me the hooks, but the Save As dialog that is displayed is not exactly the same as the built-in dialog. For example, there is no way to add password protection from the dialog because the "General Options..." menu item doesn't appear under the Tools drop-down in the upper right corner of the Save As dialog. There are a couple of other menu items missing as well. Removing functionality is a big deal since I'm taking over all saves.

On the other hand, using the built-in dialog causes the save to happen before the dialog returns, so I miss my hook #1, but the dialog obviously has all the expected features.

The Word object model provides a way to do this by providing separate Display and Execute methods for the built-in dialog in addition to the Show method, but I don't see anything equivalent for Excel.

Anybody have any ideas?

Thanks very much,
Eric
 

Some videos you may like

Excel Facts

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney

EricSmith

New Member
Joined
Aug 23, 2007
Messages
7
Thanks for the reply.

I'm not sure what you're suggesting, though. At the point that the BeforeSave event arrives, you don't know two things if the save is a Save As:

1. Whether the user will actually save the workbook (since they could choose to cancel when the dialog appears later).
2. The file name to which the user will save (if they don't cancel the dialog).

This leads me to the strategy of displaying the Save As dialog myself in order to know those two things, leading me to the problem in my original post.

Is there something about the BeforeSave event that I'm missing?

Thanks,
Eric
 

EricSmith

New Member
Joined
Aug 23, 2007
Messages
7
Office FileDialog

To partially answer my own question, there is also the option of using the Office FileDialog from the application's FileDialog property (using msoFileDialogSaveAs).

This approach is similar to GetSaveAsFilename in that you can get the file name before the save happens. It also has the advantages of prepopulating the file filters, and all the save logic is handled by just calling Execute on the dialog. It is still missing the Tools functionality, though, and seems to have other weird behaviors.

For example, if you try to save over an existing file, a prompt appears asking for confirmation when the user clicks the "Save" button (within the call to Show), but another overwrite confirmation prompt appears when calling Execute.
 

Scott Huish

MrExcel MVP
Joined
Mar 17, 2004
Messages
19,958
Office Version
  1. 365
  2. 2010
Platform
  1. Windows

ADVERTISEMENT

Not sure completely what you are doing, but is something like this what you want?

Code:
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Dim x
Application.EnableEvents = False
    Cancel = True
    x = Application.GetSaveAsFilename(, "Excel Files (*.xls), *.xls")
    If x <> False Then ThisWorkbook.SaveAs x, , "password"
Application.EnableEvents = True
End Sub


If cancel wasn't pressed in the GetSaveAsFileName, then it will save the workbook where you specified with a password of password.
 

EricSmith

New Member
Joined
Aug 23, 2007
Messages
7
What I really want is the built-in Excel Save As dialog (including the built-in UI to set a password and various other options) split into Show and Execute steps. I don't think I'm going to get there, so I'm trying to make the closest approximation I can.

Thankyou, though, because your sample made me aware of the EnableEvents property, which let me clean up my code a little, and also got me looking for an application property to get rid of the double overwrite prompts. At first I thought it was AlertBeforeOverwriting, but that's for overwriting cells, not files. Eventually I found the DisplayAlerts property, which when set to false, got rid of the redundant overwrite prompt.

Thanks,
Eric
 

Jon Peltier

MrExcel MVP
Joined
May 14, 2003
Messages
4,943
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
Excel Dialog Argument List:
http://msdn2.microsoft.com/en-us/library/aa221648(office.11).aspx

xlDialogSaveAs document_text, type_num, prot_pwd, backup, write_res_pwd, read_only_rec

This illustrates how it works:

Code:
Sub TestSaveAs()
  Dim sFile As String
  Dim bTest As Boolean

  ' initial directory - only works if file not yet saved
  ChDir "C:\Test\"
  
  ' initial filename
  sFile = "TestSaveAsDialog.xls"
  
  bTest = Application.Dialogs(xlDialogSaveAs).Show(sFile)
  
  MsgBox bTest ' true if file saved, false if dialog canceled
End Sub

Unlike GetSaveAsFileName, the dialog doesn't wait for you to save the file, the save happens when the user clicks Save. The dialog doesn't return the file name or path, but you can find the .name and .path of the activeworkbook. Also, you wanted to do something after the path was known before the file was saved, but the user can browse to a different directory and you can't respond until the file has been saved.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,577
Messages
5,596,996
Members
414,116
Latest member
sfullnet

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top