We help IT Professionals succeed at work.

Best Method to Export Access Query/Report To Excel

125 Views
Last Modified: 2018-01-30
This is related to an earlier question titled "MS Excel Enter a formula into multiple cells conditionally". Currently I create a query like "qrsCostOfSales" in the sample database attached. This is exported to Excel. Using the subtotal function in Excel customer totals are added. The COS % for each customer is manually added in Excel. I can do all this in an Access report. However I need to present this information to our users/clients in a spreadsheet. Exporting the spreadsheet results in the attached spreadsheet. The resulting spreadsheet requires manipulation and sometimes not all the calculated fields are preserved. Also the Access export of a report only allows exporting to the "xls" format. Not very efficient. My question is there a way to export this data from Access to Excel where the only work required in Excel would be formatting ? Thanks.
TestExcelExport.accdb
CostOfSales_Test.xls
Comment
Watch Question

Co-Founder and Chief Architect
CERTIFIED EXPERT
Top Expert 2016
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
CERTIFIED EXPERT
Distinguished Expert 2017

Commented:
If you want the totals to be "live", you will need to export the data and then using OLE automation, add the totals using VBA.  If I need to do things like this and I don't know the Excel VBA to do them, I open Excel and turn on the macro recorder and perform the steps.  Then I turn off the recorder and open the generated macro.  Sometime, it can be directly imported into Access but other times, you will need to clean up the way cells are referenced.  The generated code is from Excel's point of view and you need it to be from Access' point of view.  I don't have any code handy.

Author

Commented:
I replicated what you did with a couple changes. Instead of AVG I summed CostOfSales and divided to get the percent for customer and grand total. So it appears to work. The only issue is the customer totals are displayed at the top before the item by item summary. See attached. Yours is not like that. What am I doing differently ?  Thanks.
COS_EE.docx
CERTIFIED EXPERT
Distinguished Expert 2017

Commented:
In order to get the totals to be at the bottom, you need a field you can sort on that will make that happen.  Then include an order by clause in the query.  It looks like sorting on CustID in the above example will work.  However, if the actual customer ID is not fixed in length, there will be anomalies because the numeric value is being converted to a text string and so it will sort like a string so you might have to solve that problem also.

For example
1
1
1Total
10
10
10Total
100
100
100Total
2
2
2Total

Author

Commented:
Thanks Dustin and Thanks Pat for the explanation on sorting.
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.