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

ASKER CERTIFIED SOLUTION

Dustin Saunders

membership

Create a free account to see this answer

Signing up is free and takes 30 seconds. No credit card required.

See answer

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.

StampIT

ASKER

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

StampIT

ASKER

Thanks Dustin and Thanks Pat for the explanation on sorting.