Results 1 to 7 of 7
  1. #1
    Join Date
    Jan 2008
    Location
    Denver, CO
    Posts
    54

    Question Unanswered: exporting the results of an sql statement to excel

    I'd like to export the results of a query I'm running using VBA to an excel file. I know how to export an entire table but I'm not entirely sure how to export just the results of the sql statment. Here's the code I'd use for the entire table:

    Code:
        DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "tblMain", "c:\test.xls", True
    and that works just fine but I'd like to do two things: First, export the results of an sql statment dynamically generated. My code for that looks something like this:

    Code:
        Dim db As Database
        Dim rs As Recordset
        Set db = DBEngine(0)(0)
        Dim strSQL As String
        strSQL = "SELECT * FROM tblMain " _
            & "WHERE CaseNum = '" & Me.txtCaseNum & "';"
    
    ' ---not sure what to put here... db.execute or db.openrecordset or what
    
        DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, --not sure what to put here either--, "c:\test1.xls", True
    And second, pretty-up the spreadsheet by expanding the columns to fit the data and by bolding the header row and such. Your help would be greatly apprecaited. Thanks!

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Check out the code bank and look at the exporting to excel entry. This covers all this and more

    HTH

  3. #3
    Join Date
    Jan 2008
    Location
    Denver, CO
    Posts
    54
    is that the excel automation files?

  4. #4
    Join Date
    Jan 2008
    Location
    Denver, CO
    Posts
    54
    Got it... I'm trying it now... I'll let you know how it goes. Thanks!

  5. #5
    Join Date
    Jan 2008
    Location
    Denver, CO
    Posts
    54
    OK... ran into a bit of a problem... I think the issue I'm facing is regarding that note in your code re reference to 2003 library. I imported your code and I get the error "User defined type not defined" on "exApp As Excel.Application" when I run it. I assume I need to go to Tools : References and add some library but I know nothing about the libraries... what am I looking for? Is that even where I should look? Thanks for your help!

  6. #6
    Join Date
    Jan 2008
    Location
    Denver, CO
    Posts
    54
    One more question... if I add "Microsoft Excel 12.0 Library" what will that mean if I run my app from a computer with an earlier version of office? Thanks for all your help!

  7. #7
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    read "Late Binding" further down in the 'Bank.
    late binding to Excel doesn't need a reference set.

    izy
    currently using SS 2008R2

Posting Permissions

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