Use of transferspreadsheet

Does transferspreadsheet work with reports?  
OutputTo acOutputReport works for reports but it truncates memo fields.
transferspreadsheet does not truncate memo fields in queries but fails with error when the source is a report

Scenerio 1. fails to export a report with runtime error 3011-...could not find object rptlegal
Scenerio 2. works to export query

1.      Private Sub cmdExcel_Click()
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "rptLegal", "D:\Risk Mgmt DB\Monthly Legal Report.xls"
End Sub

2.      Private Sub cmdExcel_Click()
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "qryIncidentLegalMonthly", "D:\Risk Mgmt DB\Monthly Legal Report.xls"
End Sub
Wayne MarkelOwnerAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:

 TransferSpreadsheet will only handle a table or query.

 You can however use the reports recordsource for that rather than the report.

Wayne MarkelOwnerAuthor Commented:
I understand.  It is just that my client wants the report header along with the exported data.
Not a show stopper, just a nice to have.


Wayne MarkelOwnerAuthor Commented:
I've requested that this question be closed as follows:

Accepted answer: 0 points for Wayne Markel's comment #a40920866

for the following reason:

This is a valid solution even if not the only solution.  

Lightning fast response on my question.

Thanks to Jim
Protecting & Securing Your Critical Data

Considering 93 percent of companies file for bankruptcy within 12 months of a disaster that blocked access to their data for 10 days or more, planning for the worst is just smart business. Learn how Acronis Backup integrates security at every stage

Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
If your trying to avoid the truncation on the Output to, you can use method #2 here:

ACC2000: Memo Field Truncated When Report Is Output to Excel


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Jeffrey CoachmanMIS LiasonCommented:
<No Points wanted>
Exporting reports can be troublesome
1. Some data may be misaligned
2. You may not get any of the report "formatting"
3. Calculated report values will end up being "static" values in Excel.
4. Page breaks, headers, footers, margins ...etc, ...may not appear where you might expect.

Follow Jim's suggests

Wayne MarkelOwnerAuthor Commented:
This will be a challenge for me, but a good learning experience
Wayne MarkelOwnerAuthor Commented:
Thank you Jeffrey for the additional input.  This gives me more confidence to explain to my client.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.