Pass a formula form a Query to Excel

[detrie]

Access 2003
Excel 2003

I have a routine that exports the results of a query to an Excel file.
Is it possible to input the formula into the query so that the Excel values calculate?

This is the formula I am trying to pass to the "AZ" column of the Data tab

MyCalc::"IF(T2="","0",TODAY()-T2)"

 

[boblarson]

No, you can't do it with a query export but you can open the Excel file and modify it using code:

Dim objXL As Object
Dim xlWB As Object
Dim xlWS As Object
 
Set objXL = CreateObject("Excel.Application")
Set xlWB = obXL.Workbooks.Open("PathAndFileNameHere")
Set xlWS = xlWB.Worksheets("WorksheetNameHere")
 
' code goes here to go down the used range (I left that out as I don't have time right now to include it
 
xlWS.Cells(lngRow2, lngCol1).Formula = "=SUM(" & xlWS.Cells(lngRow2, 5).Address & ":" & xlWS.Cells(lngRow2, lngCol1 - lngColOffSet).Address & ")"
 
' then we move to the next row by changing the row variable
 
' when done we then save and close the file
 
xlWB.Close True
 
objXL.Quit
Set objXL = Nothing

That's a very ROUGH bit of code which hopefully will give you the idea. It is in no way complete.