Having a user choose where to save an Excel workbook

Question

I currently have a macro that will read data, print a results sheet, and then copy the data and results to a different workbook that is then saved as a regular .xlsx file as opposed to .xlsm. My code to save that book is as follows:

NewBook.Activate
ActiveWorkbook.SaveAs Filename:=NotNum & " Make Ready", _
     FileFormat:=xlOpenXMLWorkbook
CTLBook.Activate

It brings up the new workbook I want to save, saves it under the name NotNum (a changing variable) and the string "Make Ready" and then brings back my original workbook so I can manipulate that further.

The save function works perfectly however it saves to my default folder (currently 'My Documents'). Is there a way I can code something before or after the save function that allows the user to choose where to save the document? Thanks

You can use the built in folder chooser dialog to ask the user for a location — Tim Williams, Jun 01 '18 at 18:28
[This thread](https://stackoverflow.com/questions/26392482/vba-excel-to-prompt-user-response-to-select-folder-and-return-the-path-as-string#26392703) may have the info you need — cybernetic.nomad, Jun 01 '18 at 18:30
@TimWilliams I'm not experiences in the built in functionality of VBA. How would I call that dialogue? — Matt Gaydon, Jun 01 '18 at 18:53
@cybernetic.nomad That thread was exactly what I needed. Thank you! — Matt Gaydon, Jun 01 '18 at 19:18

Wookies-Will-Code · Accepted Answer · 2018-06-01T19:10:24.080

You are looking for something like this:

Dim fldr As FileDialog
Dim sItem As String
Dim fileSaveName As String


Set fldr = Application.FileDialog(msoFileDialogFolderPicker)
    With fldr
        .Title = "Select a Folder to Save to"
        .AllowMultiSelect = False
        .InitialFileName = Application.DefaultFilePath
        If .Show <> -1 Then GoTo NextCode

        sItem = .SelectedItems(1)
    End With
NextCode:
        Set fldr = Nothing

sItem now has the path.

You can prompt for the file name:

fileSaveName = Application.GetSaveAsFilename(NotNum & " Make Ready", fileFilter:="Excel Files (*.xlsx), *.xlsx")

This is the part you are after to complete:

ActiveWorkbook.SaveAs Filename:=sItem & "\" & fileSaveName, _
     FileFormat:=xlOpenXMLWorkbook

I wove some of your naming into that and wove out some of the sub I was using that prints PDF files individually or all in one file.

EDIT: This code was adapted from @Ozgrid and @Gary's Student . . .

Happy Coding!

Thank you good sir! Your code combined with the thread someone posted before gave me exactly what I need. I started running into syntax errors when I tried running it myself and your code helped me debug it and get it up and running. — Matt Gaydon, Jun 01 '18 at 19:19

Having a user choose where to save an Excel workbook

1 Answers1