VAP to create the excel file with formular from query

[kowol]

Below is the current VBA to export the query to excel file but I need know how to export the formula from query like "avg=([m1]+[m2]+[m3])/3" to the excel file as well.

Private Sub Command34_Click()

Dim stDocName As String

DoCmd.SetWarnings Off
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "rpt_weeklystatus", "c:\rpt_weeklystatus.xlsx", True

Exit_Command34_Click:
Exit Sub

Err_Command34_Click:
MsgBox Err.description
Resume Exit_Command34_Click

End Sub

 

[CJ_London]

So far as I know, it can't be done by exporting the query. The only way you could do it is by using VBA to open an instance of Excel and modifying the cells directly.

One simple reason is the formula used in Excel are written completely differently from those written in VBA./SQL