Excel External Data Query Refreshing issue

[Vogel515]

In my spreadsheet I've got a list of about 400 data items I need to refresh, they are all prices.

This code is supposed to refresh the prices by grabbing the new prices, then enter the new prices into another spreadsheet. What's happening though is that the prices are being copied into the other spreadsheet before excel refreshes my query.

Any thoughts?

 

[Vogel515]

Woah that posted ugly.

Sub refresh_mlb_team_prices_tmtrk()

Dim qt As QueryTable
Dim TMcount As Integer
Dim Colcount As Integer
Dim x As Integer
Dim y As Integer
y = 3

Worksheets("mlb_team_PTdatapull").Activate

For Each qt In ActiveSheet.QueryTables
qt.Refresh
Next qt

Worksheets("mlb_team_price_tracking").Activate

TMcount = Range("b2")
Colcount = Range("b1")

Cells(4, Colcount).Value = Now()
For x = 1 To TMcount
Cells(4 + x, Colcount).Value = Worksheets("mlb_team_PTdatapull").Range("c" & y).Value
y = y + 7
Next
Range("B1").Value = Colcount + 1

Worksheets("mlb_teams").Activate
Range("e1").Value = Now()

Worksheets("mlb_team_price_tracking").Activate

End Sub

 

[mayest]

I've never programmed query tables, but are you sure that your refresh is actually being completed? The Refresh method returns a boolean to indicate success, or lack thereof. So, you might DIM a boolean variable (say, Success) and then use it to check the result while stepping through the code.

For Each qt In ActiveSheet.QueryTables
Success = qt.Refresh
Next qt

If the queries are succeeding, then maybe your code is continuing before the query completes. Does the code work if you add some delay right after running the refresh?

 

[Vogel515]

The code worked fine when I had them run as separate macro's I'm just looking to combine them now.

I know the refresh is working because the numbers are updating - the numbers being printed to the other worksheet are the unupdated ones. I was just looking for a way to make sure the query finishes before those numbers get brought over.

 

[mayest]

I don't know if this will help, but try keeping that refresh in its own function:

Function qtRefresh(qt As QueryTable) as Boolean
qtRefresh = qt.Refresh
End Function

I think (!) that the function won't return until the query is completed. Call that function from your For Each loop instead of doing the query there.

 

[Vogel515]

mayest - good call.

I should have posted but I tried this on Friday and it worked out well.

Thanks