Export recordset to Excel spread sheet

[cope22]

I have an SQL string that generates an ADO recordset, and I need to export the contents of that recordset to an Excel spread sheet. I've messed around with using the DoCmd.TransferSpreadsheet function:

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, rsRecordset, filename, True

This has brought me no success; I'm sure I'm doing it wrong. What is the correct way to do this, if possible? This is Access/Excel 2000, if that makes a difference.

Thanks in advance for any help.

cope22

 

[psemianonymous]

I think using the rsRecordset.Source property will return the SQL or query name. With ADO, I'm assuming it will be straight SQL.


Anyway:

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, rsRecordset.Source, filename, True

Maybe that will work. --
Find common answers using Google Groups:

http://groups.google.com/groups?group=comp.databases.ms-access

 

[cope22]

Thanks for your reply, but unfortunately the Recordset.Source property didn't work. I think that the function is having a problem because I'm not actually specifying the name of an existing table or query object. Maybe there's another function I should be using...?

I'll keep poking at it, and check back occasionally to see if anyone has any additional suggestions. Thanks,

cope22

 

[dougcranston]

cope22,

I have done something similar, although I took the approach to pull the data from Access via ADO and then create a workbook and associated worksheets, format the sheet, etc.

My approach exposes/utilizes the Access and Excel object models. It is to long to post here so if your interested in the code, respond to this with your email address and will forward you a copy.

The following link provides some references.

Export to Excel then format the spreadsheet using VBA
thread705-511927 you go to Google and search for VBA or VBScript Excel Access object and play with the search string and you will find alot of info.

Hope this helps.
DougCranston

 

[cope22]

That would be great, Doug. Here's my email address:

cope22@hotmail.com

I was thinking that I might have to do something like what you're suggesting, but was dreading it because I have little experience with the Excel object in VBA. But hey, you live, you learn.

Thanks so much.

cope22

 

[dougcranston]

On its way.
DougCranston