Export Forms Recordset to Excel 2

[longestdrive]

Hi,

I'm what is best described as a stumbler....from one issue to another. Most I resolve through other posts but I'm stuck on this one.

I have a form which allows the user to specify criteria. After clicking a search button the search results are displayed. The query is created within the form.

I can successfully open a report with the search results.

I want to now export the search results direct to excel. I've unsuccessfully tried a couple of versions here and am settling on the TransferSpreadsheet method. Here is my code so far:

Private Sub btn_ExportExcel_Click()

Dim rst As Recordset
Dim strfilename As String
Dim strpath As String

strpath = CurrentProject.path
strfilename = strpath & "\profiles report.xls"
Set rst = Me.RecordsetClone
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel8, rst, strfilename, True
[CODE]
I get an error message:
Wrong data type for one of the arguments in the TransferSpreadsheet line.

Any advice, pointers where I'm going wrong or whether there is a simple answer?

I think it's around the recordsetclone part - although not sure

My version of Access is 2003

Thanks

 

[VRoscioli]

The reason for your error is that the TransferSpreadsheet method expects a string (the name of a named query or table) as its third argument, and you are passing it a RecordSet. I don't know if you are able to use TransferSpreadsheet without having a named query.


-V

 

[VRoscioli]

You might also take a look at thread705-1388308


-V

 

[longestdrive]

Thanks.

I think your right. Doing further research seems to support your suggestion.

I've looked at the thread above but could not implement it without errors in my application so thought I'd try this approach....(bad move?)

Is there a way then that I can use the recordsetclone to create a temp query or table and then use the tmp table/query in this function?

Ta

 

[VRoscioli]

I see no reason why you couldn't populate a temporary table with the contents of your query. I think the previously cited method might be cleaner, though.


-V

 

[PHV]

Another way is to automate Excel and use the CopyFromRecordset method of the Excel.Range object.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886