Results 1 to 11 of 11
  1. #1
    Join Date
    Oct 2009
    Posts
    204

    Unanswered: Exporting queries to Excel.....and opening them?

    Hello,

    I'm exporting a query to Excel and that works great but I would like it to also open the spreadsheet after I choose a save location. The way I had it before was to designate a place and put the file path in the code, but this can confuse users. I took out the file path so they are able to choose their own path, but when they click on "OK" I would like it then to open the workbook they just created. Here is my current code:

    Code:
    DoCmd.OutputTo acOutputQuery, "qry_ByOrderReason", acFormatXLSX, yes, , , acExportQualityPrint
    Does anyone know how to do this?

    Thanks,
    Chris

  2. #2
    Join Date
    Jan 2002
    Location
    Bay Area
    Posts
    511
    Attached an example of exporting the results of an Access query to Excel, and then opening the workbook. It includes a SaveAs dialog box.
    Code:
    Function ExportToExcel()
    Dim txtPath As String, savePath As String
    Dim dlgSaveAs As FileDialog
    Dim oXL As Object
    
        Set dlgSaveAs = Application.FileDialog(msoFileDialogSaveAs)
    
        With dlgSaveAs
        
            .AllowMultiSelect = False
            If txtPath <> "" Then .InitialFileName = txtPath
            .Show
            
            If .SelectedItems.Count <> 0 Then
                savePath = .SelectedItems(1)
                txtPath = .SelectedItems(1) & _
                    IIf(Right(.SelectedItems(1), 1) = "\", "", "\")
            End If
            
        End With
        
        Set dlgSaveAs = Nothing
    
        If txtPath = "" Then
            MsgBox "Export cancelled."
            Exit Function
        End If
      
        If Right(txtPath, 1) = "\" Then
            txtPath = Mid(txtPath, 1, Len(txtPath) - 1)
        End If
        
        If UCase(Right(txtPath, 4)) = "XLS" Then
            txtPath = Mid(txtPath, 1, Len(txtPath) - 4)
        End If
        
        DoCmd.TransferSpreadsheet acExport, 8, _
            "qryMyQuery", txtPath, False, ""
            
        Set oXL = CreateObject("Excel.Application")
        With oXL
            .Visible = True
            .Workbooks.Open (txtPath)
        End With
    
        Set oXL = Nothing
       
    End Function
    
    Function fileExists(pathFile As String)
    Dim fileName As String
    
        fileName = Dir(pathFile)
        If fileName <> "" Then
            fileExists = True
        Else
            fileExists = False
        End If
        
    End Function
    Attached Files Attached Files
    Last edited by JerryDal; 04-23-10 at 21:53. Reason: changed "Table1" to "qryMyQuery"

  3. #3
    Join Date
    Oct 2009
    Posts
    204
    I look at in this database and it works....I use a button on a form to call the function. I put the same function in my database and change only the query name and it gets stuck here

    Code:
    Dim dlgSaveAs As FileDialog
    I am using Access 2007, I don't know if that makes a difference.

  4. #4
    Join Date
    Jan 2002
    Location
    Bay Area
    Posts
    511
    I have Access 2003. I can duplicate your problem when I uncheck "Microsoft Office 11.0 Object Library".
    While in VBA editor, choose Tools, References, then search for your version of the Object Library and check it. That should fix the problem.
    Jerry
    Last edited by JerryDal; 04-26-10 at 12:06. Reason: spelling

  5. #5
    Join Date
    Oct 2009
    Posts
    204
    That worked perfectly. A couple more questions:

    Will I have to check that box on every user's machine, or does it stay that way within the database settings?

    I change
    Code:
    If UCase(Right(txtPath1, 4)) = "XLS" Then
    to

    Code:
    If UCase(Right(txtPath1, 4)) = "XLSX" Then
    and there is no effect. How can I get it to output in .xlsx format?

  6. #6
    Join Date
    Jan 2002
    Location
    Bay Area
    Posts
    511
    Someone else on the forum will have to take this. I do not have any experience with Access 2007.
    Jerry

  7. #7
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    I use this example which has worked very nice for exporting to excel (or other formats) and then opening the folder where it was exported to: http://www.dbforums.com/6390529-post84.html
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  8. #8
    Join Date
    Oct 2009
    Posts
    204
    Quote Originally Posted by pkstormy View Post
    I use this example which has worked very nice for exporting to excel (or other formats) and then opening the folder where it was exported to: http://www.dbforums.com/6390529-post84.html
    This is nice and I will probably use it in the future, but I think I want more of a one-click export and open. That's what I have right now with the above module, but I would also like it to save the file as .xlsx. It works great now as xls without error messages, but since everything we do here is 2007, I would like xlsx so it can all be uniform.

  9. #9
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    Glad you looked at it chris07tibgs. It can be modified easily to do xlsx exports and then automatically open up the exported folder. Perhaps I'll work on a new example. Thanks for taking a look. Hopefully you found 'something' helpful. There are other examples in the code bank if you're interested.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  10. #10
    Join Date
    Jan 2002
    Location
    Bay Area
    Posts
    511
    Go into the code and click on TransferSpreadsheet and press F1. I hope the help file will tell you how to use this in the way you want to. That's about all the help I can offer. Good Luck!

  11. #11
    Join Date
    Oct 2009
    Posts
    204
    Quote Originally Posted by JerryDal View Post
    Go into the code and click on TransferSpreadsheet and press F1. I hope the help file will tell you how to use this in the way you want to. That's about all the help I can offer. Good Luck!
    This set me on the right direction, and I found this AcSpreadSheetType Enumeration*[Access 2007 Developer Reference]

    so I changed the 8 to 10 and it works exactly as desired. Thank you so much for your help!

Posting Permissions

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