AirCooledNut
09-26-2012, 11:39 AM
I have a recordset that I want to export into Excel 2000 format (acSpreadsheetTypeExcel9). I believe I need to drop it into a table first then execute a DoCmd.TransferSpreadsheet (keeps it easy and works). The user sets just a few parameters in the form, thus the Me. syntax you will see.
Here's the code so far:
Option Explicit
Public Const gTEMP_TBL As String = "_temp_table"
...
Dim sExecuteQuery As String, rst As dao.Recordset, qdf As dao.QueryDef, bHasProgramCode As Boolean, sFullPath As String, sFileName As String
...code not shown here just gets the query name...
Set qdf = CurrentDb.QueryDefs(sExecuteQuery) 'Open the query
'Assign values to the query using the parameters option
If bHasProgramCode = True Then
qdf.Parameters(0) = Me.lbl_ProgramCodes.Section
qdf.Parameters(1) = Me.txt_StartDate
qdf.Parameters(2) = Me.txt_EndDate
Else
qdf.Parameters(0) = Me.txt_StartDate
qdf.Parameters(1) = Me.txt_EndDate
End If
sFullPath = Me.lbl_SaveTo.Caption & "\" & sFileName
Set rst = qdf.OpenRecordset 'Convert the querydef to a recordset and run it
If rst.BOF = True And rst.EOF = True Then
MsgBox "No records were found.", vbExclamation, "Empty recordset"
Exit Sub
End If
'Dump recordset into a table, export it to Excel, then delete it.
'
'Here's where I need to put the recordset into the temporary table gTEMP_TABLE
'
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, gTEMP_TABLE, sFullPath, True 'Export table to an Excel format
'Clean up!
DoCmd.DeleteObject acTable, gTEMP_TBL 'Done with the temporary table so delete it
rst.Close
qdf.Close
Set rst = Nothing
Set qdf = Nothing
Help/suggestions? Thank you:beerchug:
Access 2010 in Windows 7
Here's the code so far:
Option Explicit
Public Const gTEMP_TBL As String = "_temp_table"
...
Dim sExecuteQuery As String, rst As dao.Recordset, qdf As dao.QueryDef, bHasProgramCode As Boolean, sFullPath As String, sFileName As String
...code not shown here just gets the query name...
Set qdf = CurrentDb.QueryDefs(sExecuteQuery) 'Open the query
'Assign values to the query using the parameters option
If bHasProgramCode = True Then
qdf.Parameters(0) = Me.lbl_ProgramCodes.Section
qdf.Parameters(1) = Me.txt_StartDate
qdf.Parameters(2) = Me.txt_EndDate
Else
qdf.Parameters(0) = Me.txt_StartDate
qdf.Parameters(1) = Me.txt_EndDate
End If
sFullPath = Me.lbl_SaveTo.Caption & "\" & sFileName
Set rst = qdf.OpenRecordset 'Convert the querydef to a recordset and run it
If rst.BOF = True And rst.EOF = True Then
MsgBox "No records were found.", vbExclamation, "Empty recordset"
Exit Sub
End If
'Dump recordset into a table, export it to Excel, then delete it.
'
'Here's where I need to put the recordset into the temporary table gTEMP_TABLE
'
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, gTEMP_TABLE, sFullPath, True 'Export table to an Excel format
'Clean up!
DoCmd.DeleteObject acTable, gTEMP_TBL 'Done with the temporary table so delete it
rst.Close
qdf.Close
Set rst = Nothing
Set qdf = Nothing
Help/suggestions? Thank you:beerchug:
Access 2010 in Windows 7