手动打开 excel 文件允许公式运行，使用 VBScript 或 PowerShell 或 Python 的 win32com 打开 excel 文件不会 [英] Opening an excel file manually allows formulas to run, opening an excel file with VBScript or PowerShell or Python's win32com doesn't

问题描述

我的脚本无法更新 excel 文件时遇到问题，我将其简化为以下问题:

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

如果我打开一个 excel 文件，我可以转到公式"选项卡并单击立即计算"，它会花费一些时间来更新所有计算.

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.

如果我运行 VBScript 只是来打开文件(见下面的代码)，如果我转到公式选项卡并单击立即计算"，它会立即刷新并且没有任何变化.

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               

但这些似乎与转到选项卡并单击计算"执行相同的操作，结果只是快速刷新并且 Excel 页面根本不尝试更新.

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.

使用python的win32com模块时也是如此.我无法在打开的文件中运行计算.

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)

使用 PowerShell 也是如此.

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?

推荐答案

我发现了这个问题，用 VBScript(或 Powershell)打开 excel 页面不会自动包含手动打开时包含的 Addins，所以我需要手动添加这两个插件.

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)PIPCExcelPITrendXL.xla").Installed = True
excel.AddIns.Add("C:Program Files (x86)PIPCExcelpipc32.xll").Installed = True

这篇关于手动打开 excel 文件允许公式运行，使用 VBScript 或 PowerShell 或 Python 的 win32com 打开 excel 文件不会的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！