Results 1 to 3 of 3
  1. #1
    Join Date
    Jan 2002
    Location
    Bay Area
    Posts
    511

    Question 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.
    Thanks.
    Jerry

  2. #2
    Join Date
    Feb 2004
    Location
    Chicago, IL
    Posts
    1,312
    You will want to look at several things:

    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:

    Code:
    Private Sub cmdBrowse_Click()
    
        Dim dlgBrowse As FileDialog
        
        Set dlgBrowse = Application.FileDialog(msoFileDialogSaveAs)
        
        With dlgBrowse
            
            .AllowMultiSelect = False
            If txtPath <> "" Then .InitialFileName = txtPath
            .Show
            
            If .SelectedItems.Count <> 0 Then
    
                txtPath = .SelectedItems(1) & IIf(Right(.SelectedItems(1), 1) = "\", "", "\")
                
            End If
            
        End With
        
        Set dlgBrowse = Nothing
    
    End Sub

  3. #3
    Join Date
    Jan 2002
    Location
    Bay Area
    Posts
    511

    Thumbs up

    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:
    Code:
    Shell "Excel.exe " & savePathFile, 3
    This is resolved. Thank you.
    Jerry

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •