Turn Off Calcs in Excel

[tuktuk]

I am running some code that export qry result to an excel application but want to turn off the "Application.Calculation" in excel prior to the export.

i cannot figure out the correct syntax.

here is the portion of the modules code definin my Excel app, workbook worksheet etc.

Dim xlx As Object, xlw As Object, xls As Object, xlc As Object
Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim blnEXCEL As Boolean, blnHeaderRow As Boolean

blnEXCEL = False

' Replace True with False if you do not want the first row of
' the worksheet to be a header row (the names of the fields
' from the recordset)
blnHeaderRow = False

' Establish an EXCEL application object
On Error Resume Next
Set xlx = GetObject(, "Excel.Application")
If Err.Number <> 0 Then
      Set xlx = CreateObject("Excel.Application")
      blnEXCEL = True
End If
Err.Clear
On Error GoTo 0

' Change True to False if you do not want the workbook to be
' visible when the code is running
xlx.Visible = True

i've tried

'turn off calcs
excel.Application.Calculation = xlManual

and

xlx.Calculation = xlManual

thanks for the help
Tuk

 

[boblarson]

The problem with this:

'turn off calcs
excel.Application.Calculation = xlManual

is that you are trying to use an EXCEL CONSTANT with late binding and you can't do it exactly that way. You also should not be using excel.Application at all. That will open a hidden instantiated copy of Excel which will cause you grief until you close your database.

See my article here for more about that.


So, I don't know if the code is correct but you would reference this like this:

xlx.Application.Calculation = -4105

There might be a way to reference the constant in late binding but I've not used it.

 

[tuktuk]

this appears to have solved the issue

Set xlw = xlx.Workbooks.Open(theFullPath & "Template_Subnet_Specific.xls")

'turn off calcs
xlw.Application.Calculation = xlManual

i'll continue to research the topic on the suggested site.

i appreciate the help