Exporting data from continuous form to Excel spreadsheet

[OssieMac]

I have the following code that I thought should export my data from a
continuous form to an Excel Spreadsheet. The sql statement basically emulates
that which is used within my continuous form as the forms record source.
However, on
the "DoCmd.TransferSpreadsheet...." line I get the following error:-

Runtime error '2498'
An expression you entered in the wrong data type for one of the arguments.

So what I thought would be simple just isn't working and I have come to the
conclusion that I can't use record set for the parameter in lieu of a table
name.

Any help in pointing me in the right direction will be greatly appreciated
because I have been searching for answers and just not finding exactly what I
need.

Private Sub Export_To_Excel_Click()

Dim sqlStatement As String
Dim strPath As String
Dim strFileName As String
Dim strFilePath As String
Dim dBs
Dim rs

sqlStatement = "SELECT [PrefixAndId], [Association1], [Product]," & _
"[ProductDescription] FROM [DonatedProducts] " & _
"WHERE ([DonatedProducts].[ReceiptOut]= " & _
Forms![Receipts Out Tracking]![ReceiptOut] & ") " & _
"ORDER BY Left(DonatedProducts!PrefixAndId,1)," & _
"DonatedProducts.ProductID;"

Set dBs = CurrentDb

Set rs = dBs.OpenRecordset(sqlStatement)

strPath = "C:\Documents and Settings\Database"
strFileName = "TestExport.xls"
strFilePath = strPath & "\" & strFileName

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel8, rs, strFilePath

End Sub

 

[Jeanette Cunningham]

OssieMac,

The third argument is the one that is giving the error.

From vba help - the third argument is called TableName, here is what help
says:

TableName Optional Variant. A string expression that's the name of the
Microsoft Access table you want to export spreadsheet data to, or the
Microsoft Access select query whose results you want to export to a
spreadsheet.

I have never been able to use TransferSpreadsheet with a recordset or sql
statement.
However OutputTo will work with an sql statement.
You can also use excel's copy from recordset method with a recordset.

Jeanette Cunningham

 

[OssieMac]

Thanks for your reply Jeanette. You have confirmed what I suspected was the
problem and so I have now attacked the problem with a different approach.

I have overcome it by creating a Make Table Query. Probably (but not sure)
could have done it just with a query but using the Make Table query, I can
delete all the records from the table (Not delete the table, only the
records) with the Form Load event and the user is presented with a blank form
instead of the results of the last time the query was run.

When the user populates a field, the AfterUpdate event runs the query and
the form is populated from the table and if the user wants an Excel copy then
I have a button to create it from the table.

So once again, thanks for your reply. It is much appreciated.

--
Regards,

OssieMac

OssieMac,

The third argument is the one that is giving the error.

From vba help - the third argument is called TableName, here is what help
says:

TableName Optional Variant. A string expression that's the name of the
Microsoft Access table you want to export spreadsheet data to, or the
Microsoft Access select query whose results you want to export to a
spreadsheet.

I have never been able to use TransferSpreadsheet with a recordset or sql
statement.
However OutputTo will work with an sql statement.
You can also use excel's copy from recordset method with a recordset.

Jeanette Cunningham

I have the following code that I thought should export my data from a
continuous form to an Excel Spreadsheet. The sql statement basically
emulates
that which is used within my continuous form as the forms record source.
However, on
the "DoCmd.TransferSpreadsheet...." line I get the following error:-

Runtime error '2498'
An expression you entered in the wrong data type for one of the arguments.

So what I thought would be simple just isn't working and I have come to
the
conclusion that I can't use record set for the parameter in lieu of a
table
name.

Any help in pointing me in the right direction will be greatly appreciated
because I have been searching for answers and just not finding exactly
what I
need.

Private Sub Export_To_Excel_Click()

Dim sqlStatement As String
Dim strPath As String
Dim strFileName As String
Dim strFilePath As String
Dim dBs
Dim rs

sqlStatement = "SELECT [PrefixAndId], [Association1], [Product]," & _
"[ProductDescription] FROM [DonatedProducts] " & _
"WHERE ([DonatedProducts].[ReceiptOut]= " & _
Forms![Receipts Out Tracking]![ReceiptOut] & ") " & _
"ORDER BY Left(DonatedProducts!PrefixAndId,1)," & _
"DonatedProducts.ProductID;"

Set dBs = CurrentDb

Set rs = dBs.OpenRecordset(sqlStatement)

strPath = "C:\Documents and Settings\Database"
strFileName = "TestExport.xls"
strFilePath = strPath & "\" & strFileName

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel8, rs,
strFilePath

End Sub