R
Robert Cottigham
I have a problem I was hoping someone might be able to help with. My
environment is as follows:
* OS: Windows 7 -or- Windows XP SP3 (does not seem to matter)
* Excel 2007 (12.0.6514.500) SP2 MSO (12.0.6425.1000)
* Microsoft Visual Basic 6.5.1040 (installed from the Office CD with Excel
2007)
* WSS 3.0 SP1 (on a remote server)
I am using an macro-enabled Excel workbook (xlsm) that contains connections
to a dozen SharePoint lists. When I call the ThisWorkBook.RefreshAll
function in one of my routines, I sometimes (not always) recieve multiple
error messages as follows:
* The title of the error message is "Microsoft Visual Basic".
* The prompt for the message is empty.
* It is a critical error (the "X" in the red circle is shown on the left of
the MsgBox).
* An OK and Help button are present.
* If I push the Help button, I get the default help file. So, I presume the
help context is unspecified?
* If I push the OK button, the program continues. It does not trip the error
handler in VBA surrounding the RefreshAll call.
I traced the program execution and, it happens inside the RefreshAll call.
So, I really have no way to debug this problem. I have tried the following:
* Gone to every connection and made sure that 'Enable background refresh' is
disabled. (They all were but, had to check.)
* Tried sticking a DoEvents before the call to RefreshAll. (No effect.)
* Tried setting Application.DisplayAlerts = False before the command. (No
effect.)
In desperation, I tried writing my own RefreshAll as follows:
Public Sub MyRefreshAll()
' Declare local variables.
Dim wbc As WorkbookConnection
Dim pc As PivotCache
' Refresh each connection individually.
For Each wbc In ThisWorkbook.Connections
wbc.Refresh
Next wbc
' Refresh each pivot cache indiviually.
For Each pc In ThisWorkbook.PivotCaches
pc.Refresh
Next pc
End Sub ' MyRefreshAll
This seemed to fix the issue so far. So my questions are:
1. Has anyone seen this behaviour or even a resolution for it?
2. Is calling the MyRefreshAll routine above equivalent to
ThisWorkBook.RefreshAll.
3. Any suggestions on how I might track down the issue in RefreshAll?
Any assisitance would be greqatly appreciated.
environment is as follows:
* OS: Windows 7 -or- Windows XP SP3 (does not seem to matter)
* Excel 2007 (12.0.6514.500) SP2 MSO (12.0.6425.1000)
* Microsoft Visual Basic 6.5.1040 (installed from the Office CD with Excel
2007)
* WSS 3.0 SP1 (on a remote server)
I am using an macro-enabled Excel workbook (xlsm) that contains connections
to a dozen SharePoint lists. When I call the ThisWorkBook.RefreshAll
function in one of my routines, I sometimes (not always) recieve multiple
error messages as follows:
* The title of the error message is "Microsoft Visual Basic".
* The prompt for the message is empty.
* It is a critical error (the "X" in the red circle is shown on the left of
the MsgBox).
* An OK and Help button are present.
* If I push the Help button, I get the default help file. So, I presume the
help context is unspecified?
* If I push the OK button, the program continues. It does not trip the error
handler in VBA surrounding the RefreshAll call.
I traced the program execution and, it happens inside the RefreshAll call.
So, I really have no way to debug this problem. I have tried the following:
* Gone to every connection and made sure that 'Enable background refresh' is
disabled. (They all were but, had to check.)
* Tried sticking a DoEvents before the call to RefreshAll. (No effect.)
* Tried setting Application.DisplayAlerts = False before the command. (No
effect.)
In desperation, I tried writing my own RefreshAll as follows:
Public Sub MyRefreshAll()
' Declare local variables.
Dim wbc As WorkbookConnection
Dim pc As PivotCache
' Refresh each connection individually.
For Each wbc In ThisWorkbook.Connections
wbc.Refresh
Next wbc
' Refresh each pivot cache indiviually.
For Each pc In ThisWorkbook.PivotCaches
pc.Refresh
Next pc
End Sub ' MyRefreshAll
This seemed to fix the issue so far. So my questions are:
1. Has anyone seen this behaviour or even a resolution for it?
2. Is calling the MyRefreshAll routine above equivalent to
ThisWorkBook.RefreshAll.
3. Any suggestions on how I might track down the issue in RefreshAll?
Any assisitance would be greqatly appreciated.