Link to home
Start Free TrialLog in
Avatar of Wayne Markel
Wayne MarkelFlag for United States of America

asked on

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
Avatar of Jim Dettman (EE MVE)
Jim Dettman (EE MVE)
Flag of United States of America image

Wayne,

 TransferSpreadsheet will only handle a table or query.

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

Jim
Avatar of Wayne Markel

ASKER

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.

thanks,

Wayne
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
ASKER CERTIFIED SOLUTION
Avatar of Jim Dettman (EE MVE)
Jim Dettman (EE MVE)
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
<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

JeffCoachman
This will be a challenge for me, but a good learning experience
Thank you Jeffrey for the additional input.  This gives me more confidence to explain to my client.