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

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)

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

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


Reply With Quote