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

问题描述

我没有更新excel文件的脚本有问题，我把它减少到以下问题：

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

如果我运行VBScript 只是打开文件（见下面的代码），如果我去公式选项卡，然后点击立即计算它会立即刷新，没有任何改变。

  Dim objXLApp，objXLWb，objXLWs 
 
设置objXLApp = CreateObject（Excel.Application）
 objXLApp.Visible = True 
设置objXLWb = objXLApp.Workbooks.Open（file_path.xls）
  

我尝试过各种各样的东西，如：

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

但是，这些似乎和去打标签一样，点击计算，只需快速刷新，而excel页面根本不会更新

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

 导入win32com.client作为win32 
 
 excel = win32.Dispatch（'Excel.Application'）
 excel.Visible = True 
 excel_workbook = excel.Workbooks.Open（file_path.xls）
  

PowerShell也是如此。

  $ excel = New-Object -com excel.application 
 $ excel.Visible = $ True 
 $ workbook = $ excel.Workbooks.Open（$ file_path）
  

那么为什么打开这个文件我们发现这个问题，用VBScript（或Powershell）打开excel页面，不会自动包括手动打开时包含的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 
  

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?

解决方案

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

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