Prevent error when Excel OLAP pivot table autorefresh

[oldapola]

Hi,

I use Excel 2010. I have workbook full of Pivot tables based on OLAP cube data, which are used by analyst for writeback. There is event trigger Workbook_open which refresh all Pivottables in the workbook. I need to prevent situation, when for some reason is cube not accessible.
When it happens now, then it calls some message like "Do you really connect to 'source' ?" And opens login window.

I need code which first tests if cube is accessible ,if yes then it continues in my current code, if not then it trigger MSGBOX with information to user + another action (like closing workbook, but it is not important now, i can do that).

My current code is

Sub LockPivotTable()
Dim pt As PivotTable
Dim pf As PivotField
Dim ws As Worksheet

For Each ws In ActiveWorkbook.Worksheets
Set pt = ActiveSheet.PivotTables(1)
With pt
.RefreshTable
.EnableWizard = True
.EnableDrilldown = False
.EnableFieldList = False
.EnableFieldDialog = False
.PivotCache.EnableRefresh = True
For Each pf In pt.PivotFields
With pf
.DragToPage = False '(1)
.DragToRow = False
.DragToColumn = False
.DragToData = False
.DragToHide = False
End With
Next pf
End With
Next ws
End Sub

Thanks alot

 

[SkipVought]

hi,

Never used cubes but do use PTs & VBA. Some code observations that may help you proceed with more success...
Sub LockPivotTable()
Dim pf As PivotField
Dim ws As Worksheet

For Each ws In ActiveWorkbook.Worksheets
With ws.PivotTables(1)
.RefreshTable
.EnableWizard = True
.EnableDrilldown = False
.EnableFieldList = False
.EnableFieldDialog = False
.PivotCache.EnableRefresh = True
For Each pf In .PivotFields
With pf
.DragToPage = False '(1)
.DragToRow = False
.DragToColumn = False
.DragToData = False
.DragToHide = False
End With
Next pf
End With
Next ws
End Sub
[/code]


Skip,

_{Just traded in my old subtlety...
for a NUANCE!}