ThisWorkbook.RefreshAll Issue/Question
 

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 a

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.

Nothing
 

Nothing



Robert Cottigham wrote:

ThisWorkbook.RefreshAll Issue/Question
23-Nov-09

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 a

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.

Previous Posts In This Thread:

On Monday, November 23, 2009 3:05 PM
Robert Cottigham wrote:

ThisWorkbook.RefreshAll Issue/Question
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 a

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.


Submitted via EggHeadCafe - Software Developer Portal of Choice
SharePoint Video Library Template Available For Download
http://www.eggheadcafe.com/tutorials...o-library.aspx