Export Table to Excel (Access 2000)

Topic

New Reply

I’m trying to export an Access recordset to an Excel file via a little VBA code. The problem is when the export line is read I get a 2498 error (which says I entered an incorrect data type for one of the arguments). Needless to say, I’m not an expert in VBA coding .

Here’s the code:

———————–
Private Sub B_GENERATE_Click()
Dim rs_excel As Recordset
Dim Query, EXCEL_NAME
Set rs_excel = New ADODB.Recordset

Query = “select FIELD_A, FIELD_B, FIELD_C from TABLE where CONDITION”
rs_excel.Open Query, CurrentProject.Connection, adOpenKeyset, adLockOptimistic

EXCEL_NAME = “C:myfolder” & InputBox(“Input Excel file name” & Chr(13) & “(do not include the .xls extension)”, “Input Name”) & “.xls”

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel8, rs_excel, EXCEL_NAME

End Sub
———————–

Edited to add: I tried the SQL query in the SQL Query Analyzer and it worked

Do you know what might be going wrong?

Reply | Quote

Replies

Chr(13) is a carriage return. That is an illegal character in a file name.
But the major problem is that DoCmd.TransferSpreadsheet can only export tables and saved queries, not recordsets. So ypu’ll have to create a query with the correct SQL and save it. Then you can export it to Excel.

Reply | Quote

Hi Hans,

The Chr(13) is part of an InputBox prompt, and thus does not form part of the file path/name.

After a whole morning trying to accomplish this and not finding anything related to an Access recordset DoCmd.TransferSpreadsheet export to Excel, we suspected the problem had to do with the recordset object. Your response confirms our suspicions .

Thanks!

Reply | Quote

Sorry, I didn’t look closely enough at the definition of EXCEL_NAME.

Reply | Quote