Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 37
Default this action will cancel a pending refresh data command

Hi All,

Hope you can help.

I have a number of workbooks with large numbers of sheets with MS Queries to
Access databases each also have a number of sheets with Pivot tables.

I have code to open each workbook, then refresh all the queries & then
refresh all the Pivots & then save the workbook

However when i run it i get the message "this action will cancel a pending
refresh data command" when it tries to send, obviously it has not finished
refreshing.

I have tried to add DoEvents in all over the place, it doesn't help, i
googled the error message & all i found was 'turn off the alert display'
which is great i don't get the message but it doesn't refresh half the
worksheets & pivots.

I don't want to build in a 'wait' time as some spreadsheets take seconds to
update and some take 10 minutes or more & i want the same code in each, so
each one of 30 odd spreadsheets would take 10mins * 30 (5 hours! instead of
the 30mins or so it should take.)

Please can someone help!
Emma


Sub Macro1()

Call UpdateQueryTablesInWorkBook

Call UpdatePivotTablesInWorkBook

ActiveWorkbook.Save

End Sub


'Updates all queries in the Workbook
Public Sub UpdateQueryTablesInWorkBook()
Dim s As Worksheet
Dim q As QueryTable

For Each s In ActiveWorkbook.Worksheets
For Each q In s.QueryTables
q.Refresh
Next q
Next s
End Sub

'Updates all Pivottables in the workbook
Public Sub UpdatePivotTablesInWorkBook()
Dim s As Worksheet
Dim p As PivotTable

For Each s In ActiveWorkbook.Worksheets
For Each p In s.PivotTables
With p.PivotCache
.Refresh
End With
Next p
Next s
End Sub
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 3,942
Default this action will cancel a pending refresh data command

hi
i think you are overloading the system. try this

For Each s In ActiveWorkbook.Worksheets
For Each q In s.QueryTables
q.Refresh Backgroundquery:= False
Next q
Next s

from experiece i have found that if you are refreshing only 1 query and
doing nothing after, then a backgroud query doesn't matter.
but if you are refreshing many querys AND allowing code to run while the
refresh is in process, then this can cause some problems.
not sure what is generating your specific error but gut feeling is telling
me you've hit a wall and excel is throwing up a "related" error when the real
error is overload.
try removing the background query ie "refresh Backgroundquery:=false" and
see what happens. code will (or should) pause untill each query has been
refreshed.

regards
FSt1
"Emma Hope" wrote:

Hi All,

Hope you can help.

I have a number of workbooks with large numbers of sheets with MS Queries to
Access databases each also have a number of sheets with Pivot tables.

I have code to open each workbook, then refresh all the queries & then
refresh all the Pivots & then save the workbook

However when i run it i get the message "this action will cancel a pending
refresh data command" when it tries to send, obviously it has not finished
refreshing.

I have tried to add DoEvents in all over the place, it doesn't help, i
googled the error message & all i found was 'turn off the alert display'
which is great i don't get the message but it doesn't refresh half the
worksheets & pivots.

I don't want to build in a 'wait' time as some spreadsheets take seconds to
update and some take 10 minutes or more & i want the same code in each, so
each one of 30 odd spreadsheets would take 10mins * 30 (5 hours! instead of
the 30mins or so it should take.)

Please can someone help!
Emma


Sub Macro1()

Call UpdateQueryTablesInWorkBook

Call UpdatePivotTablesInWorkBook

ActiveWorkbook.Save

End Sub


'Updates all queries in the Workbook
Public Sub UpdateQueryTablesInWorkBook()
Dim s As Worksheet
Dim q As QueryTable

For Each s In ActiveWorkbook.Worksheets
For Each q In s.QueryTables
q.Refresh
Next q
Next s
End Sub

'Updates all Pivottables in the workbook
Public Sub UpdatePivotTablesInWorkBook()
Dim s As Worksheet
Dim p As PivotTable

For Each s In ActiveWorkbook.Worksheets
For Each p In s.PivotTables
With p.PivotCache
.Refresh
End With
Next p
Next s
End Sub

  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 1
Default this action will cancel a pending refresh data command

On Tuesday, September 8, 2009 9:58:02 AM UTC-5, Emma Hope wrote:
Hi All,

Hope you can help.

I have a number of workbooks with large numbers of sheets with MS Queries to
Access databases each also have a number of sheets with Pivot tables.

I have code to open each workbook, then refresh all the queries & then
refresh all the Pivots & then save the workbook

However when i run it i get the message "this action will cancel a pending
refresh data command" when it tries to send, obviously it has not finished
refreshing.

I have tried to add DoEvents in all over the place, it doesn't help, i
googled the error message & all i found was 'turn off the alert display'
which is great i don't get the message but it doesn't refresh half the
worksheets & pivots.

I don't want to build in a 'wait' time as some spreadsheets take seconds to
update and some take 10 minutes or more & i want the same code in each, so
each one of 30 odd spreadsheets would take 10mins * 30 (5 hours! instead of
the 30mins or so it should take.)

Please can someone help!
Emma


Sub Macro1()

Call UpdateQueryTablesInWorkBook

Call UpdatePivotTablesInWorkBook

ActiveWorkbook.Save

End Sub


'Updates all queries in the Workbook
Public Sub UpdateQueryTablesInWorkBook()
Dim s As Worksheet
Dim q As QueryTable

For Each s In ActiveWorkbook.Worksheets
For Each q In s.QueryTables
q.Refresh
Next q
Next s
End Sub

'Updates all Pivottables in the workbook
Public Sub UpdatePivotTablesInWorkBook()
Dim s As Worksheet
Dim p As PivotTable

For Each s In ActiveWorkbook.Worksheets
For Each p In s.PivotTables
With p.PivotCache
.Refresh
End With
Next p
Next s
End Sub


This doesn't fix your problem with VBA code but it works. Go to the data tab, click connections in the connections group, click on EVERY connection and click properties, un-check the box that says "enable background refresh".


Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Command button to refresh data Greg Snidow Excel Programming 1 October 8th 07 03:50 PM
When Excel wait for OLE action allow cancel so not have to end pr sferrell615 Excel Discussion (Misc queries) 0 August 4th 06 02:46 PM
refresh data command message Vince Stitt Excel Programming 0 June 2nd 05 10:31 PM
Pre-pending and post-pending cell data in Excel David Neustadt Excel Programming 2 October 8th 03 02:53 AM
cancel query refresh - vb paddy Excel Programming 0 August 7th 03 02:17 AM


All times are GMT +1. The time now is 06:32 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"