vba 使用 New-Object 中断公式打开 excel 文件，使用 Invoke-Item 不会
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/25393169/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Opening an excel file using New-Object breaks formulas, using Invoke-Item doesn't
提问by Charles Clayton
I need to run a script that just opens an excel file, calculates an excel cell connected with a Pi DataLink, then tells me the value.
我需要运行一个脚本，它只打开一个 excel 文件，计算一个与Pi DataLink连接的 excel 单元格，然后告诉我值。
If I try to do that in the way that's standard:
如果我尝试以标准的方式做到这一点：
$objExcel = New-Object -com Excel.Application
$objExcel.Visible = $True
$WorkBook = $objExcel.Workbooks.Open("C:\Users\crclayton\sheet.xlsx")
$WorkSheet = $WorkBook.Sheets.Item("Sheet1")
write-host $worksheet.Range("A1").Text
$WorkBook.Save()
$WorkBook.Close()
$objExcel.Quit()
I get a #NAME?error. And even if I just use the first three lines to just openan excel file and look at it, I can't run calculations, =PICurrVal("TAGNAME",0,"SERVERNAME")is just a dead formula that excel doesn't understand if I open it this way. I've also tried to UpdateLinkswhen I open the file, but no dice.
我得到一个#NAME?错误。而且即使我只是用前三行打开一个excel文件看一下，我也不能运行计算，=PICurrVal("TAGNAME",0,"SERVERNAME")只是一个死公式，如果我这样打开它excel不明白。UpdateLinks当我打开文件时，我也尝试过，但没有骰子。
However, if I open the file like so:
但是，如果我像这样打开文件：
Invoke-Item "C:\Users\crclayton\sheet.xlsx"
I don't get a #NAME?error and I can run the calculations and excel understands this formula.
我没有收到#NAME?错误，我可以运行计算并且 excel 理解这个公式。
Maybe something like this?
也许是这样的？
Invoke-Item "C:\Program Files (x86)\Microsoft Office\Office14\EXCEL.EXE"
Start-Sleep 10
$objExcel = Get-Process "EXCEL.EXE"
$WorkBook = $objExcel.Workbooks.Open("C:\Users\crclayton\sheet.xlsx")
$WorkSheet = $WorkBook.Sheets.Item("Sheet1")
write-host $worksheet.Range("A1").Text
Is there some way to get the value in cell A1 having opened the spreadsheet using Invoke-Item?
有没有办法在使用 Invoke-Item 打开电子表格后获取单元格 A1 中的值？
回答by Bluecakes
I'm not sure why you're getting #NAME?as Excel should be doing all the calculations within the sheet all we're doing in Powershell is getting the value of the cell.
我不确定为什么你会得到#NAME?Excel 应该在工作表中进行所有计算，而我们在 Powershell 中所做的只是获取单元格的值。
However what you can try is outputting the value of your formula to a nearby cell and getting the value of it instead, for example:
但是，您可以尝试将公式的值输出到附近的单元格并获取它的值，例如：
Your formula is in D18-> =PICurrVal("TAGNAME",0,"SERVERNAME")
Your value is in D19-> =D18
你的公式在D18->=PICurrVal("TAGNAME",0,"SERVERNAME")
你的价值在D19->=D18
Call the value in your Powershell:
在 Powershell 中调用该值：
$objExcel = New-Object -com Excel.Application
$objExcel.Visible = $True
$WorkBook = $objExcel.Workbooks.Open("C:\Users\crclayton\sheet.xlsx")
$WorkSheet = $WorkBook.Sheets.Item(1)
write-host $worksheet.Range("D18").Text
$WorkBook.Save()
$WorkBook.Close()
$objExcel.Quit()
Update
更新
Excel addins can be added in powershell by using the Addins property like so:
可以使用 Addins 属性在 powershell 中添加 Excel 插件，如下所示：
$MyAddin = $Workbook.AddIns.Add('C:\test.xla', $True)
$MyAddin.Installed = "True"
Your new complete code might look something like
您新的完整代码可能类似于
$objExcel = New-Object -com Excel.Application
$objExcel.Visible = $True
$WorkBook = $objExcel.Workbooks.Open("C:\Users\crclayton\sheet.xlsx")
$MyAddin = $Workbook.AddIns.Add('C:\test.xla', $True)
$MyAddin.Installed = "True"
$WorkSheet = $WorkBook.Sheets.Item(1)
write-host $worksheet.Range("D18").Text
$WorkBook.Save()
$WorkBook.Close()
$objExcel.Quit()
Edit 2:
编辑2：
Yes, add-ins were the problem. I needed to add each the following files:
是的，加载项是问题所在。我需要添加以下每个文件：
$ExcelAddin = $WorkBook.Application.AddIns.Add("C:\Program Files (x86)\PIPC\Excel\PITrendXL.xla", $True)
$ExcelAddin.Installed = "True"
$ExcelAddin = $WorkBook.Application.AddIns.Add("C:\Program Files (x86)\PIPC\Excel\pipc32.xll", $True)
$ExcelAddin.Installed = "True"
$ExcelAddin = $WorkBook.Application.AddIns.Add("C:\Program Files (x86)\PIPC\Excel\OSIsoft.PIDataLink.UI.dll.manifest", $True)
$ExcelAddin.Installed = "True"
