General discussion

Locked

Exporting Access Reports

By audit ·
How can I export an Access Report into a spreadsheet and get all the detailed data and subtotals and totals I need with the data under the correct column headings?

I have a huge monthly General Ledger Report that Accounting needs in a spreadsheet. If I export the report - Access is really only exporting the Query which has no totals and subtotals.

I've tried exporting the Report as a CSV, TXT, RTF. All make it difficult to give them a spreadsheet with totals and subtotals and
all the columns aligned just right like the report.

This conversation is currently closed to new comments.

9 total posts (Page 1 of 1)  
| Thread display: Collapse - | Expand +

All Comments

Collapse -

Exporting Access Reports

by chainsawz In reply to Exporting Access Reports

When in report preview mode, click on 'file/save as export'. Then select 'to an external file or database'. Change the file type to Excel 97. This exports the report with column headings, subtotals, and totals.

Good luck!

Collapse -

Exporting Access Reports

by audit In reply to Exporting Access Reports

This method yielded the same result as when we exported from the Objects|Report window and clicked on the file|export|as Microsoft Excel 97-2000(*.xls)format. The report line items shift two columns to the right so that the column headings do not line up. The subtotals of detail lines for each group and the totals at the end of the report disappear. The work around that as yielded the best result so far has been to export as *.rtf and then copy the result into Excel. The columns still shiftbut the subtotals and totals stay.

Collapse -

Exporting Access Reports

by electraglide In reply to Exporting Access Reports

Hello,

If accounting needs to work with the figures, this solution will not work for you, but if this is a report - for general reporting purposes, you can also export to a snapshot file, which will give you an exact replica of your report. The downside to this is that the information can't be manipulated in this format.

Collapse -

Exporting Access Reports

by audit In reply to Exporting Access Reports

Yes, accounting needs to be able to work with the figures. We have used the snapshot file export for other functions where the users need just to see the report.

Collapse -

Exporting Access Reports

by hal_mcgee In reply to Exporting Access Reports

We had the same problem and the answer is a bit screwy but can work assuming you have your columns fairly rigid in their formating. We export the report to Word first and this puts the report in an RTF document, the only formating you lose is the backgound shading in some areas. Once it is in Word, open Word, select all and paste to Excel. This of course does not create any formulas etc in Excel, but at least the totals and subtotals are where they were in the original report.
Another possible solution is to write a macro in Excel that uses the subtotaling and creates the subtotals after you export the query.
Good Luck

Collapse -

Exporting Access Reports

by audit In reply to Exporting Access Reports

This was the approach we too had resorted to before I posted the question. After trying a straight export to Excel from Access we tried tried exporting the Report as a CSV, TXT, RTF and then copying into Excel. The RTF worked the best but we still had problem with the shift of the columns to the right of the headings. We were hoping for a cleaner solution so the task could be incorporated into a Access Macro. Thanks for the input - It is beginning to look like there is no straight forward solution.

Collapse -

Exporting Access Reports

by hal_mcgee In reply to Exporting Access Reports

I'm going to try another tact this time. Export the Query to Excel then use Excel's Subtotal feature to do the subtotaling. This has the advantage of creating an Outline and you can expand and collapse the spreadsheet just to see certain departments etc. You will have to export in the query all the fields you are subtotaling by and have them sorted correctly.
On the first pass, leave the check box Replace Current Subtotals checked and for each susequent pass, Uncheck this and you can createdifferent levels of subtotals by subgroups within the main group. This could be recorded in a macro and placed on a button for the users or run with Autorun.

A final thought is Pivot Tables. If you think this might work or you've never tried them, I will post some basic guidelines if you need them. I have found these to be very powerful when looking at this type of data.
Good Luck!

Collapse -

Exporting Access Reports

by audit In reply to Exporting Access Reports

This looks to be the approach to take. The only problem we ran into was that the Access export feature to a spreadsheet only allows the choice to either overwrite the existing spreadsheet or name it something else. This would seem to mean that theAutorun macro would be overwritten. However, I think I see the work around as creating a freestanding spreadsheet that contains the Autorun macro you suggest in it which would import the other spreadsheet into it and perform the summarization as you outlined. Thanks for the help. If you have any other comments I would very much appreciate reading them.

Collapse -

Exporting Access Reports

by audit In reply to Exporting Access Reports

This question was closed by the author

Back to Software Forum
9 total posts (Page 1 of 1)  

Related Discussions

Related Forums