I'm using the code below to transfer the contents of a subform to an excel sheet. I'd like to do this without having to create a query. Is it possible to use TransferSheet with a SQL string somehow?
Also, I use Set qdf = nothing to try to get rid of the query I build in this procedure when I'm done with it but when I return to the navigation pane "qryLPMSummaryFiltered" is still there. Is there a way to delete the query without having to loop through all the querys in QueryDef?
Private Sub cmdTransferToExcel_Click()
Dim dbs As Database
Dim qdf As QueryDef
Set dbs = CurrentDb
For Each qdf In dbs.QueryDefs
If qdf.Name = "qryLPMSummaryFiltered" Then
Set qdf = dbs.CreateQueryDef("qryLPMSummaryFiltered", Me!subLPMSummary.Form.RecordSource)
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "qryLPMSummaryFiltered", "C:\Documents and Settings\dpark\My Documents\LPMSummary.xls", True
Set qdf = Nothing
Set dbs = Nothing