CODEWITHSUNDEEP
pythonwinapiexcelvbscriptvba
Charles Clayton
Charles Clayton

Reputation: 17946

Opening an excel file manually allows formulas to run, opening an excel file with VBScript or PowerShell or Python's win32com doesn't

I'm having a problem with a script not updating an excel file, and I reduced it to the following problem:

If I open an excel file, I can go to the Formulas tab and click "Calculate Now" and it'll spend a bit of time updating all the calculations.

If I run a VBScript just to open the file (see following code), if I go to the Formulas tab and click "Calculate Now" it'll just refresh immediately and nothing will change. 

Dim objXLApp, objXLWb, objXLWs

Set objXLApp = CreateObject("Excel.Application")
objXLApp.Visible = True
Set objXLWb = objXLApp.Workbooks.Open(file_path.xls)

I've tried all sorts of stuff like:

objXLApp.Calculation = xlAutomatic
objXLApp.Calculate
objXLApp.CalculateFull
objXLApp.CalculateFullRebuild
objXLWb.RefreshAll
objXLWs.EnableCalculation = True
objXLWs.Calculate

But those seem to do the same thing as going to the tab and clicking "Calculate" resulting in just a quick refresh and the excel page not trying to update at all.

The same is true when using python's win32com module. I can't run calculations in the opened file.

import win32com.client as win32

excel = win32.Dispatch('Excel.Application')
excel.Visible = True
excel_workbook = excel.Workbooks.Open(file_path.xls)

The same is also true using PowerShell. 

$excel = New-Object -com excel.application
$excel.Visible = $True
$workbook = $excel.Workbooks.Open( $file_path )

So why does opening a file with these languages somehow shut off the ability to calculate the formulas?

Upvotes: 5

Views: 5733

Answers (3)

Charles Clayton
Charles Clayton

Reputation: 17946

I found the problem, opening excel pages with VBScript (or Powershell) doesn't automatically include the Addins that are included when opening manually, so I needed to manually add these two addins.

excel.AddIns.Add("C:\Program Files (x86)\PIPC\Excel\PITrendXL.xla").Installed = True
excel.AddIns.Add("C:\Program Files (x86)\PIPC\Excel\pipc32.xll").Installed = True

Upvotes: 2

Bond
Bond

Reputation: 16311

Try this. Just for testing.

' Launch Excel...
CreateObject("WScript.Shell").Run "excel.exe"

' Wait for it to load...
WScript.Sleep 5000

' Get the running instance...
Set Excel = GetObject(, "Excel.Application")

' Open your workbook...
Excel.Workbooks.Open strPath

' Now go and click the Calculate button and see if it works.

Upvotes: 1

Pike7893
Pike7893

Reputation: 80

Try adding a module with this in it

    Sub Auto_Open()
        ActiveWorkbook.RefreshAll
        Calculate
    End Sub

Upvotes: 0

Related Questions