Saving a custom export from an Access 2007 script

Question

Hello and thanks for looking at my question. I'm attempting to get a script working to export a set of excel spreadsheets from an Access 2007 database. I have the export and other functions working quite well, it's the save function that is causing me issues. My current implementation is a message box that appears to the user asking for a folder name. This allows the user to enter an existing folder name to save to that folder, but only if that folder already exists and is in the My Documents directory. To me this is functional, but not exactly what I want my client to be forced to deal with. Is there any way to get a simple "Save As" or other default save file dialog box to appear? I've got an import script working on the other end that has the default file open picker working, am I missing something? Script is below:

Private Sub btnExport_Click()
On Error GoTo Err_Command38_Click

Dim strPath As String

strPath = InputBox("Enter an existing folder name, or enter a file path.", "Export")       
Beep
MsgBox "Report will now be exported to the " & strPath & " folder in My Documents"
Dim strDocName As String
strDocName = "DatabaseExport" + Date$ + ".xlsx"   'Enter your filename here

DoCmd.SetWarnings False
DoCmd.TransferSpreadsheet acExport, 10, "tblBenefit", strPath + "/" + strDocName, True
DoCmd.TransferSpreadsheet acExport, 10, "tblBenefitDispensation", strPath + "/" +     strDocName, True
DoCmd.TransferSpreadsheet acExport, 10, "tblCourse", strPath + "/" + strDocName, True
DoCmd.TransferSpreadsheet acExport, 10, "tblCourseEnrollment", strPath + "/" + strDocName, True
DoCmd.TransferSpreadsheet acExport, 10, "tblDistinguishedStudent", strPath + "/" + strDocName, True
DoCmd.TransferSpreadsheet acExport, 10, "tblEvent", strPath + "/" + strDocName, True
DoCmd.TransferSpreadsheet acExport, 10, "tblEventFacultyAttendee", strPath + "/" + strDocName, True
DoCmd.TransferSpreadsheet acExport, 10, "tblEventPresenter", strPath + "/" + strDocName, True
DoCmd.TransferSpreadsheet acExport, 10, "tblEventsUniversityParticipant", strPath + "/" + strDocName, True
DoCmd.TransferSpreadsheet acExport, 10, "tblForeignLanguageKnowledge", strPath + "/" + strDocName, True
DoCmd.TransferSpreadsheet acExport, 10, "tblLanguage", strPath + "/" + strDocName, True
DoCmd.TransferSpreadsheet acExport, 10, "tblGrant", strPath + "/" + strDocName, True
DoCmd.TransferSpreadsheet acExport, 10, "tblOrganization", strPath + "/" + strDocName, True
DoCmd.TransferSpreadsheet acExport, 10, "tblProgramRole", strPath + "/" + strDocName, True
DoCmd.TransferSpreadsheet acExport, 10, "tblRole", strPath + "/" + strDocName, True
DoCmd.TransferSpreadsheet acExport, 10, "tblStudent", strPath + "/" + strDocName, True
DoCmd.TransferSpreadsheet acExport, 10, "tblStudyAbroad", strPath + "/" + strDocName, True
DoCmd.TransferSpreadsheet acExport, 10, "tblStudyAbroadParticipation", strPath + "/" + strDocName, True
DoCmd.TransferSpreadsheet acExport, 10, "tblTripLocation", strPath + "/" + strDocName, True
DoCmd.TransferSpreadsheet acExport, 10, "tblUniDegreeProgram", strPath + "/" + strDocName, True
DoCmd.TransferSpreadsheet acExport, 10, "tblUniFacultyActivity", strPath + "/" + strDocName, True
DoCmd.TransferSpreadsheet acExport, 10, "tblUniParticipantStudentAttendee", strPath + "/" + strDocName, True
DoCmd.TransferSpreadsheet acExport, 10, "tblUniParticipantFacultyAttendee", strPath + "/" + strDocName, True
DoCmd.TransferSpreadsheet acExport, 10, "tblUniversity", strPath + "/" + strDocName, True
DoCmd.TransferSpreadsheet acExport, 10, "tblUniversityFaculty", strPath + "/" + strDocName, True
MsgBox "Export Complete!", vbOKOnly, ""
DoCmd.SetWarnings True

Exit_Command38_Click:
Exit Sub
Err_Command38_Click:
MsgBox Err.Description
Resume Exit_Command38_Click

End Sub

--- End Script

I've tried substituting the Inputbox method with something like

Dim fileSelection As Object

Set fileSelection = Application.FileDialog(2)
fileSelection.Show
Set strPath = fileSelection

But that throws an "object required" error on Set strPath line. Any help you can offer is appreciated, thanks!

Answer 1

strPath is not an object, so Set des not apply. Try this instead:

Set fileSelection = Application.FileDialog(2)
fileSelection.Show
If fileSelection.SelectedItems.Count > 0 Then
    strPath = fileSelection.SelectedItems(1)
End If