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
 

Greg Truby

MrExcel MVP
Joined
Jun 19, 2002
Messages
9,999
Maybe I'm just dummer'n the average bear, but can't you just do?
Code:
thisworkbook.refreshall
 

schafers

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

Well-known Member
Joined
Jun 11, 2002
Messages
2,129
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

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

MrExcel MVP
Joined
Jun 19, 2002
Messages
9,999
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
 

Greg Truby

MrExcel MVP
Joined
Jun 19, 2002
Messages
9,999
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

Well-known Member
Joined
Jun 11, 2002
Messages
2,129
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

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

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

Forum statistics

Threads
1,081,526
Messages
5,359,280
Members
400,523
Latest member
ExcelNewbie98

Some videos you may like

This Week's Hot Topics

  • VBA (Userform)
    Hi All, I just would like to know why my code isn't working. Here is my VBA code: [CODE=vba]Private Sub OKButton_Click() Dim i As Integer...
  • List box that changes fill color
    Hello, I have gone through so many pages trying to figure this out. I have a 2020 calendar that depending on the day needs to have a certain...
  • Remove duplicates and retain one. Cross-linked cases
    Hi all I ran out of google keywords to use and still couldn't find a reference how to achieve the results of a single count. It would be great if...
  • VBA Copy and Paste With Duplicates
    Hello All, I'm in need of some input. My VBA skills are sub-par at best. I've assembled this code from basic research and it works but is...
  • Macro
    is it possible for a macro to run if the active cell value is different to the value above it
  • IF DATE and TIME
    I currently use this to check if date has passed but i also need to set a time on it too. Is it possible? [CODE=vba]=IF(B:B>TODAY(),"Not...
Top