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

 

[Greg Truby]

Maybe I'm just dummer'n the average bear, but can't you just do?

thisworkbook.refreshall

 

[schafers]

I would use that, except I need the queries updated first, then the pivottables can be updated. 'Cause the pivottables are built off the query data in the worksheet.

Other ideas?

 

[ChrisM]

It'd help if you specified the error you are getting, and the particular line.

Just glancing at the code, don't you want your:

qt.BackgroundQuery = True

to be

qt.BackgroundQuery = False

Setting it to true means control returns to the program immediately, whether the query has finished executing or not. If you try to refresh your pivot tables on query tables that haven't finished querying, you may get some unexpected behavior.

By the way, background query doesn't have to be set everytime you run the query, you can just right click your query in the worksheet, choose options, and set the background setting there.

 

[schafers]

Thanks for all the help, I've modified the following, and now am getting an error on the line highlighted below.

For Each ws In ThisWorkbook.Worksheets
For Each qt In ws.QueryTables
qt.BackgroundQuery = False
qt.Refresh --> ERROR HERE
Next qt
Next ws

The error I'm getting now, and this is new since changing the qt.backgroundquery = false line.

Error I get now is a Run-time Error '1004': General ODBC Error, I have the Options box saying turn on outside sources. Is there a way to automatically set this to always turn on each time, so I don't have to manually turn this on?

 

[Greg Truby]

While I know my stuff on pivots, query tables aren't really my forté. So, since I'm not so whippy on 'em, I'd try to get a bead on which QT was being the troublemaker. So I would probably inject some debug statements to help me get a fix on where things are going awry. Something like (please note the use of CODE tags):

For Each ws In ThisWorkbook.Worksheets
    For Each qt In ws.QueryTables
        debug.print format(now,"hh:mm:ss"), ws.name, qt.name, qt.commandtext, "Start______"
        qt.BackgroundQuery = True
        qt.Refresh
        debug.print format(now, "hh:mm:ss", qt.name, "______Finish"
    Next qt
Next ws

 

[Greg Truby]

Also, you might try commenting out the REFRESH call in the loop of query tables and just print off the connection strings and command texts and compare those to see if one of them stand out as being unique.

 

[ChrisM]

Sorry, not sure what you mean about turning on outside sources. Where are you seeing that?

Unfortunately you are getting a nice generic ODBC error. Only thing I can think to do is manually duplicate the refresh.

First find out which querytable is causing the issue. Run the macro again, and when you get the error, choose debug. You should be in the VBE with the yellow highlighted line.

Open up (or go down to) the Immediate window, and type:

print qt.name

then push enter. This will give you the name of the query causing the error. Stop the macro, hop back to the worksheet, and manually refresh that particular query. Does it work?

 

[schafers]

One last quick note, I'm doing this in Excel 2007, in Excel 2003 on another machine it seems to work fine.

Not sure if 2007 has changed their connection strings/commands, as I've had many VBA things changing in 2007 with minor glitches.

Thanks all for the help!!

 

[schafers]

ChrisM, I've done exactly what you said. I didn't know about the Immediate window, that's cool for debugging!

When I manually refresh each query table, they all work fine. It just won't allow it to run from the macro itself.

The Query it's trying to refresh is Trading_Company_FTP_Files, which is a ODBC connection to a CSV file on my machine.

Any other ideas, obviously I'm not a VBA guru, so I'm trying to piecemeal this together.

I'm wondering if I need to tell it to open a ODBC connection prior to running the code?