Vba ask user where to save file

I wish you the best of luck and I hope this tutorial gets you heading in the right direction! Chris "Macro" Newman :. Remove Pesky Double Spaces Between Build Your Own Excel Add-in! VBA Vault. Also, by default, the file will be saved in Documents under This PC. This default location can be provided explicitly at the time of defining sheet name. Instead of defining name initially, is it possible to write a code which allows a user to save the worksheet by the name of his choice same as Excel Save As function?

Step 1: Define a new sub-procedure under newly inserted module which can store the macro. Step 2: Define a new variable which can hold the value of the user-defined name. The reason for this variable being defined as Variant is, this data type makes Naming conventions versatile. For Example, a user may add some extra special character which are allowed in naming conventions or can add dates as well under the file name.

Step 3: Now, with the help of an assignment operator and function combination called application. GetSaveAsFilename , make a statement that allows the system to take a user-defined name. See how it has been achieved in the screenshot below. Step 4: Use conditional IF to make sure the name user enters is valid as per the naming conventions. It will only provide the full path the user has selected to save the file:.

The full path can be seen in cell A There was a problem with Example 1. There were no file filters to choose from:. A path with no file extension.

If you try to save an excel file using that path you will end up with an unrecognized file type:. In order for this not to happen you have 2 options:. The method for creating the save file dialog explained in this article uses the command FileDialog msoFileDialogSaveAs. It has only one problem. If file type filters are essential, there are 2 things you could do:.

You can download the file and code related to this article here. In this example a dialog will open asking the user to select a location to save the file.