Compromise between GetSaveAsFilename and xlDialogSaveAs

[EricSmith]

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

 

[Von Pookie]

Take a look at the BeforeSave workbook-level event.

 

[EricSmith]

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]

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]

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

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]

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]

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:

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.