How can I get the file name from the "Save As" input box (NOT the file dialog) prior to saving?

4
votes

I am trying to do some validation on the file name before it's saved.

Here are my three file-saving scenarios:

  1. File > Save
  2. File > Save As > Browse > Save
  3. File > Save As > Save

Excel's "Save As" menu with input box for file name

Additional Info:

As defined in the Workbook_BeforeSave event, the SaveAsUI parameter will indicate whether or not a dialog box is needed to save changes.

✓ In scenario 1, SaveAsUI=False, so I get the filename from ThisWorkbook.Name

✓ In scenario 2, SaveAsUI=True, so I force a dialog and get the file name with the GetSaveAsFilename method

✗ In scenario 3, SaveAsUI=True, but no intermediary dialog is ever actually required! The file gets immediately saved to that name entered in the input box.

...it's ridiculous that I'm obsessing over this because I can just override scenario 3 and force a dialog anyways, but I'm curious. Any ideas?

2
excelvba
I do not get this screen. This was caused by a recent Office update. One option would be to reverse the update as mentioned HERE but then if you are planning to send the file to someone else and that person has the update installed then this option is useless... – Siddharth Rout
@SiddharthRout This was not caused by a recent update. I am running Excel 2016 and this file dialog has been built into the Save As menu ever since I installed. Also, I cannot force all of the users of this macro to rollback their versions. – Cody

2 Answers

1
votes

I'm writing this as an answer, because the comment field is too small for it.

I found the mentioned file reference in the registry:

HKEY_CURRENT_USER\Software\Microsoft\Office\16.0\Excel\User MRU\AD_2B763A186A5179F1E2C2819B76BF6FDEF3F399938D48925BE3381441F2190369\File MRU

You'll need a find, either to open the Recent file list, or to find a way to read such a registry entry. One way (but quite a complicated one), the Shell command, as you can see here (commandline command):

reg query "HKCU\Software\Microsoft\Office\16.0\Excel\User MRU" /s

Launching this command and parsing the results might help you.

1
votes

I found what was missing in our missing vocabulary: Built-In Dialog Box

As explained here, there is no way to get the file name from this dialog until after the save has been made.