Exporting To XLS

[johny678]

I need to export a recordset to a spreadsheet but "TransferSpreadsheet" doesn't seem to do the work. Any ideas???

Thanks!

 

[boblarson]

Could you be more specific? Why doesn't DoCmd.TransferSpreadsheet appear to work? How are you calling it? What is it doing that it shouldn't or not doing what it should?

 

[johny678]

I am trying to export data based on an SQL statement but the TransferSpreadsheet returns the error "The Microsoft Jet database engine could not find the object 'select..'".
I suppose that is cause it needs to read the name of a table.
But I want to export the result of the statement.
Does this help???

 

[johny678]

this is how i call it:

DoCmd.TransferSpreadsheet acExport, , strRecs, "V:\Database\Temp.xls", True

strRecs is the SQLstatement

 

[boblarson]

You can't export a SQL statement. It won't work. What you need is an object that actually contains a recordset. So, if you open a recordset with that SQL Statement and then export that recordset that would work.

 

[johny678]

I tried that also and i got the message
"An expression you entered is the wrong data type for one of the arguments"

The code is:
Dim rsRecs As ADOdb.Recordset

Set rsRecs = New ADOdb.Recordset
rsRecs.Open "SELECT Kartela.* FROM Kartela", CurrentProject.Connection, adOpenStatic

DoCmd.TransferSpreadsheet acExport, , rsRecs, "V:\Database\Temp.xls", True

 

[johny678]

Anyone please help, cause I am too close to the deadline and nothing seems to work!!!

 

[johny678]

Finally, I came up with an answer. You cannot export data based on SQL statements or Recordsets!!!!
You need to have stored Tables or Queries. Great!!!

 

[allan57]

You can export recordsets to Excel, I use the following method:

Dim dbs As DAO.Database

Dim rstGetRecordSet As Recordset

Dim objXL As Object
Dim objCreateWkb As Object
Dim objActiveWkb As Object

Set dbs = CurrentDb
Set objXL = CreateObject("Excel.Application")
Set objCreateWkb = objXL.Workbooks.Add
Set objActiveWkb = objXL.Application.ActiveWorkBook

objXL.Visible = True
objActiveWkb.Sheets.Add
objActiveWkb.Worksheets(1).Name = "EnterSheetNameHere"

Set rstGetRecordSet = dbs.OpenRecordset("PlaceRecordSetQueryHere;")

objActiveWkb.Worksheets("EnterSheetNameHere").Cell s(1, 1).CopyFromRecordset rstGetRecordSet

objActiveWkb.Worksheets(1).SaveAs FileName:="EnterFileNameHere.xls"
objActiveWkb.Close

Set objActiveWkb = Nothing
Set objCreateWkb = Nothing
Set objXL = Nothing
rstGetRecordSet.Close
dbs.Close
Set rstGetRecordSet = Nothing
Set dbs = Nothing

 

[johny678]

Very helpful, although I had to alter it a bit!
Cheers mate!

 

[johny678]

any ideas why i get no results when i use wildcards like '*' to create my recordset???

 

[boblarson]

any ideas why i get no results when i use wildcards like '*' to create my recordset???

If you are talking about

rsRecs.Open "SELECT Kartela.* FROM Kartela", CurrentProject.Connection, adOpenStatic

Then why not use:

rsRecs.Open "SELECT * FROM Kartela", CurrentProject.Connection, adOpenStatic

That should work.