Pivot Table Update Error: The cube file cannot be opened for writing.
Posted on 2007-03-21
I am trying to programmatically update multiple pivot tables in an Excel spreadsheet. These pivot tables all point to the same Access database. I can update the first one fine, but when I try to update any of the other pivot tables I get the following error: "The cube file C:\Sales Budget 2007\Sales Budget Cube Detail.cub cannot be opened for writing.". I also get this same error when trying to manually update each of the pivot tables. If I save it, close Excel, then re-open I am able to update the next pivot table. I am curently using this code to auto-update the pivot tables:
On Error Resume Next
Application.EnableEvents = False
Dim ws As Worksheet
Dim Pt As PivotTable
If MsgBox("Do you wish to update ALL PivotTables in this workbook?", _
vbYesNo + vbQuestion + vbDefaultButton2, "Refresh PivotTables?") = vbNo Then Exit Sub
Application.StatusBar = "Updating PivotTables in current workbook!"
For Each ws In ActiveWorkbook.Worksheets
For Each Pt In ws.PivotTables
If Err.Number <> 0 Then
Err.Number = 0
Application.StatusBar = False
Application.EnableEvents = True
Any help in getting this to work would be appreciated.