Full Version: Cannot Export to an Excel File
UtterAccess Forums > Microsoft® Access > Access Forms
Lin100
Access 2003

Can Access export to an Excel file using a recordset instead of just table or query?
Error Message: An expression you entered is the wrong data type for one of the arguments

///////////////////////////////////////////////////////////////////////////////

Private Sub Export_To_An_Excel_Spreadsheet_Click()

Dim Recordset_Employee_HR As Recordset

Set Recordset_Employee_HR = Forms!F_Employee_HR.RecordsetClone

DoCmd.TransferSpreadsheet acExport, 8, Recordset_Employee_HR, Me!Export_Path & Me!File_Name, True, ""

End Sub
Edited by: Lin100 on Fri Aug 29 13:20:56 EDT 2008.
vtd
Not AFAIK.
If you want to use Recordset, you need to automate Excel and use the Excel's CopyFromRecordset method. Check Excel VBA Help for more info on CopyFromRecordset.
How do you create the Recordset in the first place?
Lin100
Hi Dinh. I ran the code below and it gives me an error.
"User-Defined type not defined."
The text is highlighted at Dim Target As Range
////////////////////////////////////////////////////////////////////
Private Sub Export_To_An_Excel_Spreadsheet_Click()
Dim Target As Range <<---- error
Dim Dbs As Database
Dim Recordset_Employee_HR As Recordset

Set Dbs = CurrentDb
Set Recordset_Employee_HR = Forms!F_Employee_HR.RecordsetClone

Set Target = ThisWorkbook.Worksheets(3).Range("A2")
Target.CopyFromRecordset Recordset_Employee_HR
End Sub
vtd
It looks to me that should be:
im Target As Excel.Range
You need the Excel Object Library in the References collection of your database if your code runs from Access. Alternatively, I think you can use late-binding.
Ocan't see where your code declares or instantiates ThisWorkbook object???
This is a "lo-fi" version of UA. To view the full version with more information, formatting and images, please click here.