I try open export Excel files to PDF. Since there are a log of Excel files to be exported I try to automate this via Powershell. In principal this works fine, but some Excel files contain formulas from an add-in. The corresponding cells only container "#NAME?" if the file is opened via Powershell. If the file is opened manually the values are calculated correctly.

I set $objExcel.visible = $True to be able to see what is going on and introduced a pause of 120 seconds to check for any error and if the add-in are actually loaded. The add-ins seem to be all active but the formulas still display as "#NAME?".

Nonetheless I tried to add the add-ons manually. Interestingly this fails for .dll add-ins with the error message "Unable to get the Add property of the AddIns class". All other add-ins do not give this error message.

What am I missing?

This is the relevant part of my Powershell script:

# Initialize Excel object
$xlFixedFormat = "Microsoft.Office.Interop.Excel.xlFixedFormatType" -as [type]
$objExcel = New-Object -ComObject excel.application
$objExcel.visible = $True
$objExcel.AskToUpdateLinks = $False
$objExcel.DisplayAlerts = $False

# Open Excel file
$workbook = $objExcel.workbooks.open("path/to/document.xls, 3, 0, 1)
$workbook.checkCompatibility = $False
$workbook.Saved = $true

# Possible load add-ins, tried with and without
$addIn = $workbook.Application.AddIns.Add("C:\Program Files (x86)\Microsoft Office\root\Office16\Library\some_addin.xla").Installed = $True

# Tried refresh all - to no effect
$workbook.RefreshAll()

Start-Sleep -Seconds 120

# Save as PDF
$pdfPath = "path/to/document.pdf
$workbook.ExportAsFixedFormat($xlFixedFormat::xlTypePDF, $pdfPath)

# Close Excel
$workbook.Close($False)
$objExcel.Workbooks.close()
$workbook = $null
$objExcel.Quit()