Opening an excel file using New-Object breaks formulas, using Invoke-Item doesn't

Question

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.

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 open an 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 UpdateLinks when I open the file, but no dice.

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.

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?

Answer 1

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.

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

Call the value in your 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:

$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:

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"