Update Macro.txt

' VBS cannot handle Excel erros events, so we can launch refresh using VBA code to have more control
' This file contains VBA macro that will be embedded into Excel workbook from VBS
' Enable 'Trust access to the VBA project object model' at 
' File - Options - Trust Center - Trust Center Settings - Macro Settings 

Function UpdateConnections() as Integer
	Dim cnct
	Dim slc As SlicerCache
	
	On Error Resume Next
	Application.Calculate
	ThisWorkbook.Model.Initialize
	
	If Err.Number <> 0 Then		
		' handle error
		Err.Clear		
	end if
	
	For Each cnct In ThisWorkbook.Connections
		Select case cnct.type
			case xlconnectiontypeodbc
				cnct.ODBCConnection.BackgroundQuery = False
			case xlconnectiontypeoledb
				cnct.OledbConnection.BackgroundQuery = False
		end select
	Next cnct
		
	
	ThisWorkbook.RefreshAll
	Application.Calculate
	Application.CalculateUntilAsyncQueriesDone	
	' wait for refresh of cube formulas
	Application.Wait(Now + TimeValue("0:00:10"))
	
	If Err.Number <> 0 Then
		' handle error
		Err.Clear
		
		UpdateConnections = 0
		Exit Function
	End If
	
	' update cache after Model refresh
	For Each slc In ThisWorkbook.SlicerCaches        
        slc.ClearManualFilter
		slc.ClearAllFilters
    Next slc
	
	' wait for refresh of cube formulas
	Application.Calculate
	Application.CalculateUntilAsyncQueriesDone
	If Not Application.CalculationState = xlDone Then
		DoEvents
		Application.Wait(Now + TimeValue("0:00:01"))
	End If
	
	UpdateConnections = 1
End Function