Hi. I am new to powershell and have had to convert old VBA code on an old server to prosershell 3 on a Windows 2012 server.
An OpenVMS system generates Excel XML spreadsheets complete with data which are deposited in a folder on the 2012 Server. A schedule service runs a powershell script periodically (every 5 minutes) which converts the Excel XML file to an Excel XLSX spreadsheet which can then be accessed by users.
If there are any errors in the XML code, (e.g. insufficient columns declared) then Excel pops up a message box with the error and the operator has to log in and accept the error or else Excel will just hang around awaiting.
If I set $objExcel.DisplayAlerts = $False then Excel exits with an error and no operator action is required.
My problem is that the error returned by Excel to Powershell in $error bears no relation to that error that would have been displayed in the Excel Message box had DisplayAlerts been set to $True.
How do I get the actual Excel error causing the problem.
I have attached the whole Powershell script which also emails the resultant Excel spreadsheet to a user. That part works fine.