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

Answer 1

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!