Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Command button to refresh data | Excel Programming | |||
When Excel wait for OLE action allow cancel so not have to end pr | Excel Discussion (Misc queries) | |||
refresh data command message | Excel Programming | |||
Pre-pending and post-pending cell data in Excel | Excel Programming | |||
cancel query refresh - vb | Excel Programming |