View Single Post
  #2 (permalink)  
Old February 26th, 2006, 12:14 PM
Bob Bedell Bob Bedell is offline
Friend of Wrox
 
Join Date: Jun 2003
Location: , , USA.
Posts: 1,093
Thanks: 1
Thanked 12 Times in 11 Posts
Default

Hi Renee,

Have you tried TransferSpreadsheet yet? I don't know what the limit of either function is (probably buried in some ancient document archive on MSDN). The two other options would be Excel Automation or the ADO connection string extended property "Extended Properties=""Excel 8.0""", both of which are touched on a bit on the other topic you posted to. Here's a TransferSpreadsheet routine:


Sub ExportSQL(strSQL As String)

    Dim db As DAO.Database
    Dim qdf As DAO.QueryDef

    On Error GoTo ExportSQL_Err

    Set db = CurrentDb
    Set qdf = db.CreateQueryDef("qryTemp", strSQL)
    qdf.Close

    DoCmd.TransferSpreadsheet acExport, , "qryTemp", "C:\Test.xls", True

ExportSQL_Exit:
    On Error Resume Next
    DoCmd.DeleteObject acQuery, "qryTemp"
    qdf.Close
    Set qdf = Nothing
    db.QueryDefs.Refresh
    Set db = Nothing
    Exit Sub
ExportSQL_Err:
    Resume ExportSQL_Exit
End Sub

Bob

Reply With Quote