UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Exporting A Report With Calculated Fields To Excel, Any Version    
 
   
PeteBryden
post Jun 15 2019, 08:23 AM
Post#1



Posts: 261
Joined: 22-November 07



Hi - I have a report that I want to export to Excel using a button on the report. I can do this, but there's a calculated field in the report, and the figures in this column aren't correctly being exported to the spreadsheet. There's also a total at the bottom of the calculated field column that works fine on the report, but doesn't show up on the export.

I've noticed that if I export via 'external data' on the ribbon, the calculated field column figures are correct, but not formatted to two decimal places, and the total doesn't show up.

Is it possible to specify that you want to export the values in a report, exactly as they show up on the report? Or perhaps there's a better way to get my report results into a spreadsheet?

Any pointers will be much appreciated.

Thanks
Pete
Go to the top of the page
 
GroverParkGeorge
post Jun 15 2019, 08:56 AM
Post#2


UA Admin
Posts: 35,316
Joined: 20-June 02
From: Newcastle, WA


We probably don't want to export a report directly to Excel for reasons you are experiencing here. Reports are intended for DISPLAY, not to be used as data sources for other purposes.

Therefore, I'd recommend that you export the data directly from the query which provides records for the Access report. You'll need to include in that export, the values used in the calculations. And, then, you'll want to apply the formulas that produce those calculations in the Excel worksheet instead.

Not knowing how complex your reports are, of course, this may be easier said than done. However, as a general rule, it's probably more effective to use the relative tools according to their strengths (calculations in Excel, presentations in Access reports).

--------------------
My Real Name Is George. Grover Park Consulting is where I do business.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
 
PeteBryden
post Jun 15 2019, 10:23 AM
Post#3



Posts: 261
Joined: 22-November 07



Thanks, George - understood, and I'll have a go at that.
Pete
Go to the top of the page
 
GroverParkGeorge
post Jun 15 2019, 10:28 AM
Post#4


UA Admin
Posts: 35,316
Joined: 20-June 02
From: Newcastle, WA


One approach that seems productive is to create a template in Excel into which the data can be exported. In that template, you can apply formatting, add calculations, and so on.

--------------------
My Real Name Is George. Grover Park Consulting is where I do business.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
 
PeteBryden
post Jun 15 2019, 10:30 AM
Post#5



Posts: 261
Joined: 22-November 07



That's beyond my current knowlege, but there's nothing like a challenge!
Thanks - P
Go to the top of the page
 
June7
post Jun 15 2019, 06:07 PM
Post#6



Posts: 653
Joined: 25-January 16



I exported a report to Excel. Report has grouping as well as total in footer. All exported, including the decimal place formatting. I used right click menu, VBA OutputTo, and ribbon button. All produced same result.
This post has been edited by June7: Jun 15 2019, 06:09 PM

--------------------
Attachments Manager is below the edit post window. To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.
DEBUG! DEBUG! DEBUG! http://www.cpearson.com/Excel/DebuggingVBA.aspx
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    20th July 2019 - 05:50 PM