• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Changing SQL query (CommandText) of shared PivotCaches

K

kprincehouse

New Member
I'm trying to write VBA to change the CommandText property of each query in a Workbook, but I'm having some trouble. I'm using Excel 2010.


At first, I had 5 Worksheets and each one had a single PivotTable with its own PivotCache. 5 Worksheets, 5 ODBC connections, 5 PivotCaches, 5 PivotTables. I had a solution written that worked fine. It looped through all Worksheets in the Workbook, then looped through all PivotTables in each Worksheet, and then changed the CommandText property for each PivotTable. The queries changed, the data refreshed, all good.


But then we wanted to add more PivotTables, but PivotTables that are using reusing some of these already-present queries: Some of the data we want to pivot 3 or 4 ways, but we want these 3 or 4 ways to share source data (as well as calculated fields). Easy, right? Just have the PivotTables share a PivotCache. We get our additional views of the data without bloating the filesize (already ~100 MB) or hammering the database.


But with shared PivotCaches, it doesn't make sense to loop through and alter PivotTables like I was doing before. That would, if I understand correctly, result in data being refreshed more than once for the shared PivotCaches. So, I read a bit more (I'm learning VBA as I go but have other programming experience) and realized there's a Workbook.PivotCaches collection; "Aha!", I thought, "I'll just loop through that instead of each PivotTable in each Worksheet."


But it doesn't work. It breaks when (I think) it tries to alter the CommandText property of a shared PivotCache (Run-time error '1004', Application-defined or object-defined error). Also (and I'm not sure exactly why this is happening) instead of modifying the existing PivotCaches it seems to be creating copies of them--after the script runs there are a handful of new data connections named Connection, Connection2, etc. It didn't do this before, when I was modifying PivotTable.CommandText instead of PivotCache.CommandText. [Edit: I think I got this wrong, it must have been PivotTable.PivotCache.CommandText, but I'd have to pull the old version out of git to check.]


I hope that explains what I'm trying to do and where I'm stuck; please ask if I left out something. So, anyone know a good way to change the CommandText property of a shared PivotCache? (And for bonus points, does anyone know why Excel is creating new connections when I try to modify PivotCache.CommandText?)


Thank you!
 
(Update: I did some checking and realized I was wrong on one point. My first method (using non-shared PivotCaches) did also result in the creation of many new Connections, I just hadn't looked closely enough. So the bonus point question is now: How to modify CommandText without creating a new connection in the workbook?)
 
Hi ,


I am not sure this may help , but have you checked this link ?


http://p2p.wrox.com/excel-vba/29037-cant-set-commandtext-property-if-cache-has-1-rpt.html


Narayan
 
Narayank991, yeah, I ran across that in my Googling; I had hoped there may be some other way, as that route (briefly switching the connection to OLEDB from ODBC) has some drawbacks for me. It looks like there's no alternative, though, so I'm implementing and testing that now. I'll update here if it works.
 
It does help, though, I would be remiss if I didn't thank you for replying. I've seen that link before but you did find the one page the internet that holds the best hope for me right now as far as I can tell :).
 
Looks like this approach is a winner; it still seems irredeemably kludgy to me in principle, but in practice it is much more elegant and effective than the alternatives... of which there are none. So.


I've only implemented this in a little test workbook so far but it worked perfectly. Main points:

1. For Each pc in Workbook.PivotCaches

2. pc.Connection = Replace(pc.Connection, "ODBC;DSN", "OLEDB;DSN", 1, 1, vbTextCompare)

3. pc.CommandText = "new SQL"

4. pc.Connection = Replace(pc.Connection, "OLEDB;DSN", "ODBC;DSN", 1, 1, vbTextCompare)

5. pc.Refresh


As a bonus, this doesn't spawn new connections. Not sure exactly what is going with that behavior, but Excel doing what I want for reasons I don't understand is ok with me right now.
 
I believe that the reason that your kludge works is found in the CommandText property reference on MSDN:

For OLE DB sources, the CommandType property describes the value of the CommandText property.

For ODBC sources, setting the CommandText causes the data to be refreshed.
Click to expand...

I'm having the same problem and trying to figure out why refreshing the data causes new connections to be created.


See also:

http://stackoverflow.com/questions/5943976/new-data-connection-created-when-changing-connection-string-using-excel-vba
 
You must log in or register to reply here.
Back
Top