When I run the spreadsheet manually the PI Datalink formulas in Excel work just fine. Running Excel under a VB script and I get the "COM Err". What is causing this error? I want this to run as a batch job.
Would you be able to provide the batch script you are running, or an example of the batch file that is throwing this error? Where do you see the error when it is thrown? What user account is used to run the script? What version of PI DataLink is installed? Are there any additional Excel add-ins installed besides PI DataLink?
Error 2146234348 translates to 0x80131014 in hex. A Google search seems to indicate this is the HRESULT return from an AppDomainUnloadedException. I wonder if enabling the VSTO debug messages could provide something interesting:
Open Computer > Properties
Click on Advanced system settings
Click on Advanced Tab
Click on the Environmental Variables
Click on New under System Variables
Add VSTO_SUPPRESSDISPLAYALERTS with value 0
Attempt running the batch script again and see if there are any new errors/messages
I did try the system variable update, but it didn't have an effect. The batch VB script is below. We now have idea of what might be happening. Just the Excel "shell" is running in the script and it doesn't load the Add-ins which would include PI Datalink. The Excel file works just fine if I run the macro on my desktop, but not if we run it using this VB script. Any thoughts on this theory?
'Wscript.Echo "** Creating a WshShell to get the current directory..."' Create a WshShell to get the current directoryDim WshShellSet WshShell = CreateObject("WScript.Shell")
'Wscript.Echo "** Creating an Excel instance..."' Create an Excel instanceDim myExcelWorkerSet myExcelWorker = CreateObject("Excel.Application")
' Disable Excel UI elementsmyExcelWorker.DisplayAlerts = FalsemyExcelWorker.AskToUpdateLinks = FalsemyExcelWorker.AlertBeforeOverwriting = FalsemyExcelWorker.FeatureInstall = msoFeatureInstallNonemyExcelWorker.Visible = False
'Wscript.Echo "** Find current working directory..."' Tell Excel what the current working directory is ' (otherwise it can't find the files)Dim strSaveDefaultPathDim strPathstrSaveDefaultPath = myExcelWorker.DefaultFilePathstrPath = WshShell.CurrentDirectorymyExcelWorker.DefaultFilePath = strPath
'Wscript.Echo "** Current directory: " & strPath
' Open the Workbook specified on the command-line Dim oWorkBookDim strWorkerWB
strWorkerWB = strPath & "\Fermentor PI Data Update.xlsm"
'Wscript.Echo "** Open the Workbook and run Macro : " & strWorkerWB
Set oWorkBook = myExcelWorker.Workbooks.Open(strWorkerWB)
' Build the macro name with the full path to the workbookDim strMacroNamestrMacroName = "'" & strPath & "\Fermentor PI Data Update.xlsm'!Module.Macro2"on error resume next ' Run the calculation macro myExcelWorker.Run strMacroName if err.number <> 0 Then ' Error occurred - just close it down. End If err.clearon error goto 0
'Wscript.Echo "** Save"myExcelWorker.Save
'Wscript.Echo "** close"'oworkBook.Close
myExcelWorker.DefaultFilePath = strSaveDefaultPath
' Clean up and shut downSet oWorkBook = Nothing
' Don’t Quit() Excel if there are other Excel instances ' running, Quit() will shut those down alsoif myExcelWorker.Workbooks.Count = 0 Then myExcelWorker.QuitEnd If
Set myExcelWorker = NothingSet WshShell = Nothing
'Wscript.Echo "** Done!"
Retrieving data ...