Export to Excel from a recordset?

[KarenH]

I have an Access application that I need to run several non-stored queries
and transfer the results to an Excel spreadsheet. They can't be stored
queries, since some of the variables, like the Excel spreadsheet name, will
change every time.
So what I'm doing is creating a recordset and then I want to transfer the
results of that recordset into Excel.

My SQL statement to create the recordset is:

strSQL = "SELECT * INTO [Excel 9.0;HDR=Yes;Database='" &
strExportSpreadsheetName & "'] FROM " & gstrImportedTableName & " WHERE
[Trans Type] = 'ADD'"

My statement to open the recordset is:

rst.Open strSQL, cnn, adOpenKeyset, adLockReadOnly, adCmdText

This all works -- it's when I try and do the transfer with the following
statement that it fails:

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, rst,
strExportSpreadsheetName, True

The strExportSpreadsheetName contains the full and proper Excel file name
with the path. The error I get is:

'Excel 9.0;HDR=Yes;Database='P:\WorkingFiles\PTSDownload\ks06_ADD.xls'' is
not a valid name

I am not sure where to go from here with this. I thought I could create a
querydef from the SQL statement, but apparently that's not an option in
Access, not that I could find anyway.

Suggestions?

 

[Klatuu]

That is because the TransferSpreadsheet is expecting the name of a table or
store query and cannot understand a recordset. Here is how you can do this:

Dim dbCurr As DAO.Database
Dim qdfCurr As DAO.QueryDef

Set dbCurr = CurrentDb()
Set qdfCurr = CurrentDb.CreateQueryDef("qryTemp", strSQL)
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, qryTemp,
strExportSpreadsheetName, True
dbCurr.QueryDefs.Delete "qryTemp"

 

[KarenH]

Awesome! Thank you, that works exactly like I wanted it to!

That is because the TransferSpreadsheet is expecting the name of a table or
store query and cannot understand a recordset. Here is how you can do this:

Dim dbCurr As DAO.Database
Dim qdfCurr As DAO.QueryDef

Set dbCurr = CurrentDb()
Set qdfCurr = CurrentDb.CreateQueryDef("qryTemp", strSQL)
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, qryTemp,
strExportSpreadsheetName, True
dbCurr.QueryDefs.Delete "qryTemp"

I have an Access application that I need to run several non-stored queries
and transfer the results to an Excel spreadsheet. They can't be stored
queries, since some of the variables, like the Excel spreadsheet name, will
change every time.
So what I'm doing is creating a recordset and then I want to transfer the
results of that recordset into Excel.

My SQL statement to create the recordset is:

strSQL = "SELECT * INTO [Excel 9.0;HDR=Yes;Database='" &
strExportSpreadsheetName & "'] FROM " & gstrImportedTableName & " WHERE
[Trans Type] = 'ADD'"

My statement to open the recordset is:

rst.Open strSQL, cnn, adOpenKeyset, adLockReadOnly, adCmdText

This all works -- it's when I try and do the transfer with the following
statement that it fails:

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, rst,
strExportSpreadsheetName, True

The strExportSpreadsheetName contains the full and proper Excel file name
with the path. The error I get is:

'Excel 9.0;HDR=Yes;Database='P:\WorkingFiles\PTSDownload\ks06_ADD.xls'' is
not a valid name

I am not sure where to go from here with this. I thought I could create a
querydef from the SQL statement, but apparently that's not an option in
Access, not that I could find anyway.

Suggestions?