Change Application.Calculation in different Excel process

Question

In a VBA subroutine I write values to another "Target" workbook that runs in a separate Excel process, like this:

Set xlWB = GetObject(STR_TARGET_WORKBOOK_FILENAME)
xlWB.Worksheets(STR_TARGET_SHEET).Range("B1").value = Now()

I want to suspend calculation in the Target workbook while I write values to it. If it were running in the same Excel process as this VBA I could just do Application.Calculation = xlCalculationManual.

How can I get and set the Application.Calculation mode in the Excel process in which the Target workbook is running?

Answer 1

The external workbook object will also have a reference to its Application, so we can simply do:

Dim xlWB As Object
Dim calcMode As Integer

Set xlWB = GetObject(STR_TARGET_WORKBOOK_FILENAME)
' Get the Target's calculation mode
calcMode = xlWB.Application.Calculation
' Suspend calculation in the Target's Excel process
xlWB.Application.Calculation = xlCalculationManual
' ... do stuff to xlWB
' Restore whatever calculation mode was in that process
xlWB.Application.Calculation = calcMode