I have an Excel (32-bit) .xlsm document that has multiple Continuous Historian function calls using the DeltaV Reporter Excel Add-In. When I open the file directly in Excel, it works fine.
When I use a PowerShell script (.ps1) to open the file with Excel.Application calls, all the function calls give the error response "#NAME?". I've tried a long list of different things to get around this and I'm just running into the same brick wall each time.
If I leave a blank workbook open in Excel in the background, then the PowerShell script works. If Excel is not already running before the script opens the file, then the Excel Add-In doesn't load. (Note that the COM Add-In portion of the DeltaV Reporter does load automatically at startup of Excel, regardless. It's the Excel Add-In, ContinuousHistorianWF that doesn't load). See screenshot below of the two Add-Ins that load when I manually open Excel. The DeltaV Reporter COM Add-In still loads when the PowerShell script runs, but the other one doesn't.
I need this XLSM file to open periodically (I'm using Windows Task Scheduler to call the PowerShell script, but I'm open to other means), so that a computer from another domain can reference the refreshed values in the XLSM file on demand.
Here's the PS1 content:
# start Excel$xl=New-Object -ComObject "Excel.Application"
$xl.DisplayAlerts = $FALSE
#open file$filename = "\\APP\DVData\CondLoadoutAccuload.xlsm"$wb=$xl.Workbooks.Open($filename)
$wb.Save()
$wb.Close()$xl.Quit()
# now you only need to release two.while([System.Runtime.Interopservices.Marshal]::ReleaseComObject($wb)){}while([System.Runtime.Interopservices.Marshal]::ReleaseComObject($xl)){}