ThisWorkbook.RefreshAll Issue/Question

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.
 
L

Luis Alva

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. M
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 Exce
2007
* WSS 3.0 SP1 (on a remote server

I am using an macro-enabled Excel workbook (xlsm) that contains connection
to a dozen SharePoint lists. When I call the ThisWorkBook.RefreshAl
function in one of my routines, I sometimes (not always) recieve multipl
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 o
the MsgBox)
* An OK and Help button are present
* If I push the Help button, I get the default help file. So, I presume th
help context is unspecified
* If I push the OK button, the program continues. It does not trip the erro
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' i
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. (N
effect.

In desperation, I tried writing my own RefreshAll as follows

Public Sub MyRefreshAll(
' Declare local variables
Dim wbc As WorkbookConnectio
Dim pc As PivotCach
' Refresh each connection individually
For Each wbc In ThisWorkbook.Connection
wbc.Refres
Next wb
' Refresh each pivot cache indiviually
For Each pc In ThisWorkbook.PivotCache
pc.Refres
Next p
End Sub ' MyRefreshAl

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 t
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:

ThisWorkbook.RefreshAll Issue/Question
I have a problem I was hoping someone might be able to help with. M
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 Exce
2007
* WSS 3.0 SP1 (on a remote server

I am using an macro-enabled Excel workbook (xlsm) that contains connection
to a dozen SharePoint lists. When I call the ThisWorkBook.RefreshAl
function in one of my routines, I sometimes (not always) recieve multipl
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 o
the MsgBox)
* An OK and Help button are present
* If I push the Help button, I get the default help file. So, I presume th
help context is unspecified
* If I push the OK button, the program continues. It does not trip the erro
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' i
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. (N
effect.

In desperation, I tried writing my own RefreshAll as follows

Public Sub MyRefreshAll(
' Declare local variables
Dim wbc As WorkbookConnectio
Dim pc As PivotCach
' 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.


Submitted via EggHeadCafe - Software Developer Portal of Choice
SharePoint Video Library Template Available For Download
http://www.eggheadcafe.com/tutorial...05-5995f2b0ab63/sharepoint-video-library.aspx
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top