Report to Excel (1 Viewer)

[gazsharpe101]

Hi,

I have got a report that I want to export to a spreadsheet and I am currently using the following code to do so:
DoCmd.OutputTo acOutputReport, "reportname", acFormatXLS

However, in the report header there are two text boxes, one underneath the other, but when I export it to excel it puts them next to each other and makes the column widths really big.

Is the DoCmd.TransferSpreadsheet better and if so how do I go about using it? I am currently using the following code:

DoCmd.TransferSpreadsheet (acExport, acSpreadsheetTypeExcel8, "rptTenonWULSInformation", "C:\TenonWULS.xls",True)

But when I try and run it, I get an error message saying:
Compile Error:
Expected: =

I cannot understand what I need to put.

I would prefer if possible to have a template document and choose the specific cells in which the date is imported is there a way to do this?

Thanks for your help.
Gareth.

 

[namliam]

You cannot (I think) use Transfer spreadsheet for a report but it is worth trying...

The Expected = error is because you are executing the Docmd as a function because you added () around the commands/parameters.
Now it is expecting something like:
SomeVariable = Docmd
To send the returncode of the DoCmd into that variable, I have never used this functionality tho...

Simply remove the () and your error is resolved, which is the same way you are doing the DoCmd.OutputTo

As for you "report"-problem, try increasing the spacing between the 2 textboxes... that sometimes helps.

 

[boblarson]

Actually, check out my export to Excel code here:
http://www.btabdevelopment.com/main/CodeSnippets/tabid/104/Default.aspx
and modify it to suit your needs.

 

[Bilbo_Baggins_Esq]

Hey Bob,
Long time no chat!
I hope all is well with you and yours!

I've been looking at your site and while I have been able to find your sample code for exporting a single record set from a form to Excel, I have not been able to locate anything specific to exporting reports to Excel.

Can you give me a clue where I'll find it, or what its title is?

THANKS!
bilbo