Unanswered: Filedialog and Exporting Query to Excel
I need to know a VBA solution to export the results of a query from Access 2003 to Excel and also allow the user to pick the location and file name, using a common dialog box. After the export, I want the new file to be opened (displayed) in Excel. The Access application is a split data base.
1. Use DoCmd.TransferSpreadsheet to export the query to Excel
2. Use the code below to save the file
3. You need to add a reference to the Microsoft Office Object Library
4. You probably need to use the Shell command to open the Excel spreadsheet after it has been created.
Here is the code:
Private Sub cmdBrowse_Click()
Dim dlgBrowse As FileDialog
Set dlgBrowse = Application.FileDialog(msoFileDialogSaveAs)
.AllowMultiSelect = False
If txtPath <> "" Then .InitialFileName = txtPath
If .SelectedItems.Count <> 0 Then
txtPath = .SelectedItems(1) & IIf(Right(.SelectedItems(1), 1) = "\", "", "\")
Set dlgBrowse = Nothing
I was able to use your VBA solution to save an Excel workbook using a common dialog box to get the path and file name, and then open the workbook for display using the SHELL command. I first saved the value in .SelectedItems(1), and used that value to open file file followingthe export: