ERROR: -2147467259 - Unable to get property 'restorePruning' of undefined or null reference

Question

We have a perl script on a Linux server that invokes a VBScript on Windows Server 2019 to refresh the data in an excel pivot sheet and then save a copy of the same without the data connections. We randomly face an error in this process, but the issue is fixed on a rerun without any changes.

The complete error log we get is :

4/19/2024 2:55:43 AM - Excel workbook opened

4/19/2024 2:56:31 AM - Conections refreshed

4/19/2024 2:56:44 AM - ERROR: -2147467259, Unexpected Error:\r\n\r\nMessage:\r\nUnable to get property 'restorePruning' of undefined or null reference\r\n\r\r\nDetails:\r\nMicrosoft.Mashup.Client.UI.Shared.JavaScriptInvocationException: Unable to get property 'restorePruning' of undefined or null reference ---> Microsoft.Mashup.Host.Document.JavaScriptException: Unable to get property 'restorePruning' of undefined or null reference

at eval code (eval code:1:1)

--- End of inner exception stack trace ---

at Microsoft.Mashup.Client.UI.Windows.JsErrorHandler.WrapInvokeScript(Func`1 invokeScript)

at Microsoft.Mashup.Client.UI.Windows.JsHostedScript.TryInvoke[T]

at Microsoft.Mashup.Client.UI.Windows.JsHostedScript.TryInvoke(String script)

at Microsoft.Mashup.Client.UI.Shared.Ux.Navigator.NavigatorHost.RestorePruning()

at Microsoft.Mashup.Client.Excel.TaskPaneControls.QueriesAndConnectionsTaskPaneControl.UpdateDisplayFilter(IEnumerable1 queries, IEnumerable1 connections)

at Microsoft.Mashup.Client.Excel.TaskPaneControls.QueriesTaskPaneControlBase.OnPublicQueriesCollectionChanged(Object sender, NotifyCollectionChangedEventArgs e)

at System.Collections.Specialized.NotifyCollectionChangedEventHandler.Invoke(Object sender, NotifyCollectionChangedEventArgs e)

at Microsoft.Mashup.Host.Document.Model.Queries.RaisePublicQueriesChangedEvent(NotifyCollectionChangedAction action, Query[] queries)

at Microsoft.Mashup.Host.Document.Model.Queries.OnQueryDeleted(Object sender, EventArgs e)

at Microsoft.Mashup.Host.Document.Model.Query.NotifyDeleted()

at Microsoft.Mashup.Client.Excel.VbaOm.NativeVbaOmHandler.<>c__DisplayClass6_1.<DeleteQuery>b__2()

at Microsoft.Mashup.Client.Excel.Shim.NativeWorkbookStorageServices.Microsoft.Mashup.Client.Excel.Shim.IDeferredStorageInvoker.InvokeDeferredStorageAction(IWorkbook workbook, Action action)

at Microsoft.Mashup.Client.Excel.Shim.NativeCoAuthServices.InvokeCoauthAction(IWorkbook workbook, UndoableActionType actionType, Action action)

at Microsoft.Mashup.Client.Excel.VbaOm.NativeVbaOmHandler.InvokeOnQuery(IntPtr workbookPointer, String queryId, Action`3 action)

at Microsoft.Mashup.Client.Excel.VbaOm.NativeVbaOmHandler.InvokeVbaOmHandler(Action action)

The code snippet for the same is :

'--- Open workbook 
set wb = XlObj.Workbooks.Open(reportTemplateName, 0) 
logFile.WriteLine Now() & " - Excel workbook opened" 
 
If Err.Number <> 0 Then 
	logFile.WriteLine Now() & " - ERROR: " & Err.Number & ", " & Err.Description 
	XlObj.Quit 
	Wscript.Quit 
End If 
 
'--- Refresh all connections 
'--- This refreshes all the data from the linked text files in the Data Sheets 
'--- After that it refreshes all the Pivot tables 
wb.RefreshAll 
 
If Err.Number <> 0 Then 
	logFile.WriteLine Now() & " - ERROR: " & Err.Number & ", " & Err.Description 
	XlObj.Quit 
	Wscript.Quit 
End If 
 
logFile.WriteLine Now() & " - Conections refreshed" 
 
'--- Now remove all the external connections and queries before saving as plain report excel 
For Each Query In wb.Queries 
    Query.Delete 
Next 
 
If Err.Number <> 0 Then 
	logFile.WriteLine Now() & " - ERROR: " & Err.Number & ", " & Err.Description 
	XlObj.Quit 
	Wscript.Quit 
End If 
 
logFile.WriteLine Now() & " - Queries Deleted"  

The error occurs in the last error handling block when deleting the queries in the Excel sheet. Any help is appreciated and since I am new to Vbscript and info on better debugging will help too.