VBA - Refresh Pivottable & Query

[schafers]

Thanks in advance to those that can help me out!

I've used this code prior and it worked fine in Excel 2007, now I'm having issues with it. Originally every Pivottable & Query pointed to a different CSV file. Now I have two pivottables built off a Excel worksheet that is built from a Query. This allows me to do some complex lookups that I couldn't do with just the Pivottable and MS Query alone.

Here's the code that's failing out: Any problems with it in Excel 2007?

Private Sub Update_All_Data_Click()
Dim pvt As PivotTable
Dim ws As Worksheet
Dim qt As QueryTable

mytitle = "This will refresh all data for validation, are you sure?"
Msg = "The Refresh process takes about 5 minutes, are you sure you want to continue?"
Response = MsgBox(Msg, vbExclamation + vbYesNo, mytitle)
Select Case Response
Case Is = vbYes
' Do Nothing, continue with program
Case Is = vbNo
Worksheets("instructions").Range("a1").Select
End
End Select

For Each ws In ThisWorkbook.Worksheets
For Each qt In ws.QueryTables
qt.BackgroundQuery = True
qt.Refresh
Next qt
Next ws

For Each ws In ActiveWorkbook.Worksheets
For Each pvt In ws.PivotTables
pvt.RefreshTable
Next pvt
Next ws


mytitle = "Confirmation of data refresh"
Msg = "The data has been refreshed"
Response = MsgBox(Msg, vbExclamation, mytitle)


End Sub

 

[ChrisM]

Does the problem query have any parameters that go with it? Are those parameters pulled from the results of preceding queries?

Try to put things in slow motion. Set a breakpoint in your For-Next loop (click to far left of line, will get a big red line), then run the macro, it will stop at that line. Push F8 to cycle through one line at a time. Do it manually like that and see what happens. Allow each query a few seconds to refresh.

You can see what worksheet and query you are on by hovering your mouse over those variables, or just type in the immediate window at any time:

print ws.name
print qt.name

To let the macro resume full speed, turn off the break point (click far left again), then hit the little play button on toolbar at top center of screen.

 

[ChrisM]

Or, just run the one offending query all by itself from the immediate window, no need to run the whole macro.

Type:

worksheets("mysheet").Querytables("Queryname").Refresh

and see what happens.

 

[schafers]

Finally found the error. I had a bad table at the very end we hadn't worked with yet. Once I removed this table, the macro ran just fine as originally posted.

Thanks to all for the help!
Steven