Wait statement in macro
I have a macro that runs a query against an external data source and then
updates the range of a pivot table based on the number of rows and columns the query retutrns. When I run the macro in debug mode everything works fine. When I run it from the command button the range is not updated. I suspect that since the query is running in background mode, that the range calculations happen before the results are returned. What I need to be able to do is have the macro wait until the query results are returned before continuing on. Is there a way to do this? Here is the macro: Option Explicit Sub Kintana_Update() ' ' Kintana_Update Macro ' Macro recorded 02/05/2010 ' This macro runs the data query to Relay and updates ' the range on the Service Level Work pivot table with the ' number of rows returned from the query Dim wks As Worksheet Dim LastRow As Long Dim LastCol As Long ' ' Query Relay Sheets("Relay Data").Select Range("A2").Select Selection.QueryTable.Refresh BackgroundQuery:=True ' Calculate the range of data returned from the query With ActiveSheet LastCol = .Cells(2, .Columns.Count).End(xlToLeft).Column LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row End With ' Refresh the main pivot table with the new range of data Sheets("PF Kintanas Pivot Tables").Select Range("D1").Select ActiveSheet.PivotTables("Service Level Work").PivotSelect "", xlDataAndLabel, _ True ActiveSheet.PivotTableWizard SourceType:=xlDatabase, SourceData:= _ "'Relay Data'!R2C1:R" & LastRow & "C" & LastCol Sheets("PF Kintanas Pivot Tables").PivotTables("Service Level Work").PivotCache.Refresh End Sub |
Wait statement in macro
Try changing the BackgroundQuery to false
-- HTH... Jim Thomlinson "tjfwestcoast" wrote: I have a macro that runs a query against an external data source and then updates the range of a pivot table based on the number of rows and columns the query retutrns. When I run the macro in debug mode everything works fine. When I run it from the command button the range is not updated. I suspect that since the query is running in background mode, that the range calculations happen before the results are returned. What I need to be able to do is have the macro wait until the query results are returned before continuing on. Is there a way to do this? Here is the macro: Option Explicit Sub Kintana_Update() ' ' Kintana_Update Macro ' Macro recorded 02/05/2010 ' This macro runs the data query to Relay and updates ' the range on the Service Level Work pivot table with the ' number of rows returned from the query Dim wks As Worksheet Dim LastRow As Long Dim LastCol As Long ' ' Query Relay Sheets("Relay Data").Select Range("A2").Select Selection.QueryTable.Refresh BackgroundQuery:=True ' Calculate the range of data returned from the query With ActiveSheet LastCol = .Cells(2, .Columns.Count).End(xlToLeft).Column LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row End With ' Refresh the main pivot table with the new range of data Sheets("PF Kintanas Pivot Tables").Select Range("D1").Select ActiveSheet.PivotTables("Service Level Work").PivotSelect "", xlDataAndLabel, _ True ActiveSheet.PivotTableWizard SourceType:=xlDatabase, SourceData:= _ "'Relay Data'!R2C1:R" & LastRow & "C" & LastCol Sheets("PF Kintanas Pivot Tables").PivotTables("Service Level Work").PivotCache.Refresh End Sub |
All times are GMT +1. The time now is 04:27 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com