VBA - Refresh Pivottable & Query

schafers

Board Regular
Joined
Jul 5, 2005
Messages
74
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
 

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.
Maybe I'm just dummer'n the average bear, but can't you just do?
Code:
thisworkbook.refreshall
 
Upvote 0
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?
 
Upvote 0
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.
 
Upvote 0
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?
 
Upvote 0
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):
Code:
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
 
Upvote 0
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.
 
Upvote 0
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?
 
Upvote 0
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!!
 
Upvote 0
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?
 
Upvote 0

Forum statistics

Threads
1,213,513
Messages
6,114,064
Members
448,545
Latest member
kj9

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top