Avatar of StampIT
StampITFlag for United States of America asked on

Best Method to Export Access Query/Report To Excel

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
DatabasesMicrosoft AccessMicrosoft ExcelMicrosoft Office

Avatar of undefined
Last Comment
StampIT

8/22/2022 - Mon
ASKER CERTIFIED SOLUTION
Dustin Saunders

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
See how we're fighting big data
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
PatHartman

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.
ASKER
StampIT

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
PatHartman

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
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy
ASKER
StampIT

Thanks Dustin and Thanks Pat for the explanation on sorting.