Export Query to Excel w/ Formula

[ry94080]

Hello all,

I have a query that is basically exporting a count of items to Excel. I was wondering if it was possible to export to excel which would include a formula in a cell. So that the user of the report could enter a value into the excel and automatically do a simple calculation.

My query is as follows:

SELECT LV_UNQ.OrderCode, Count(LV_UNQ.OrderCode) AS CountOfOrderCode, 0 AS HoursWorked, "=C2/B2" AS Calculation
FROM LV_UNQ
WHERE (((LV_UNQ.FirstOfResultDate)>=Date()-1))
GROUP BY LV_UNQ.OrderCode, 0, "=C2/B2"
HAVING (((LV_UNQ.OrderCode)="Test"));



However, when it is exported to Excel, the formula column is not "recognized" as a formula.

Below is what the Excel export looks like:

OrderCode	Count	HoursWorked	Calculation
Test	594	0	=C2/B2

I'm hoping the end user can enter a value into HoursWorked and that the Calculation would auatomatically calculate.

I'm open to any ideas. Thanks

[Micron]

How are you exporting to Excel? If using TransferSpreadsheet method I think that to do this from Access side you'd have to use automation. Whatever method you're using, it appears your expression is being interpreted as a string, not a formula. Check Excel cell to see if an apostrophe was prepended, and check the cell format.

[ranman256]

You cannot export formulas.

[NoellaG]

You can export the data to an excel workbook and then use VBA to open the workbook and add the formula.

[ArviLaanemets]

Why don't you have simply a SQL query in Excel workbook, which reads data from your Access DB? You can set the query to be run when the workbook is opened, and additionally Excel user can activate the query at any time manually. And you can have additional columns at right of query datarange, which are automatically updated whenever the query is running. All this without any code needed.

[alansidman]

You could also bring the query into Excel using Power Query. Once set up, everytime you select refresh all, it will update in excel based upon the current values in the Access query.