TransferSpreadsheet to Multiple Worksheets 1

[NMiller007]

I am trying to export a query to multiple worksheets in Excel. I am using TransferSpreadsheet and just exporting the query works. When I try some Excel formatting within the macro, I eventually am prompted to save a copy of the file. I saw some other posts, which suggested it was a memory issue. Here's the TransferSpreadsheet code in a subroutine:

Sub exportQuery(exportSQL As String, exportFilename As String, LoanID As Variant)
    
    Dim qdf As QueryDef
    
    On Error GoTo ExportQuery_Err
    
    Set qdf = CurrentDb.CreateQueryDef("IH8Access", exportSQL)
    qdf.Close
    
    DoCmd.TransferSpreadsheet acExport, , "IH8Access", exportFilename, True, LoanID
    
ExportQuery_Exit:
    On Error Resume Next
    DoCmd.DeleteObject acQuery, "IH8Access"
    qdf.Close
    Set qdf = Nothing
    CurrentDb.QueryDefs.Refresh
    Exit Sub
    
ExportQuery_Err:
    Resume ExportQuery_Exit
End Sub

Here's the loop in the main subroutine:

Do Until rs.EOF = True
    ItemCount = ItemCount + 1
    exportSQL = "SELECT..."
    
    Call exportQuery(exportSQL, exportFilename, rs!loan_id)
    
    DoEvents
    Set objXL = CreateObject("Excel.Application")
    With objXL
        ' .Visible = True
        Set objWB = .Workbooks.Open(exportFilename)
        
        .Sheets(ItemCount).Select
        
        .Columns("F:I").Select
        .Selection.Delete Shift:=-4159
        .Range("A1").Select
 
        .DisplayAlerts = False
        .Save
        .Application.Quit
    End With
   rs.MoveNext
Loop

Other info I saw suggested this line:

Set objXL = CreateObject("Excel.Application")

was my problem, since TransferSpreadsheet is already creating an instance of Excel. Is that true? How can I do the automations that follow if I don't define objXL?

When I have that code in place, I can get through like 20 worksheets before the Save A Copy dialog pops up (the current run has 21 items). If I add in my complete Excel automation, I can make it through 10-13.

Thank you!

 

[PHV]

Replace this:
.Save
with this:
objWB.Save
objWB.Close
Set objWB = Nothing

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[NMiller007]

Perfect, thanks!