I have spent two days looking for a solution, this is driving me nuts...
First let me explain what I'm doing. We're bringing in half a million records, with these records driving a dozen pivot tables. To keep the workbook manageable file size, I built the pivot table directly from the external data connection. It's an odbc connection that I configured manually.
It all works fine, I can hit "refresh all" in the workbook and all the pivot tables update automatically, wonderful.
But now I need to be able to restrict the entire recordset with manual start and end date... changing the date filters on the pivot tables is not ideal, because it is not just a single field affected by the end date, there are fields that need to be calculated prior to the pivoting, which have values that depend on formulas involving the end date.
After a whole afternoon of crashing Excel repeatedly, I figured out the restriction that if your connection is directly to a pivot table, you can't use ? and the parameter dialog to point to a cell reference, the cell references are lost once you close the book.
So my next approach was to do this:
Dim ReportStartDate, ReportEndDate
' Get parameters from Intro sheet
ReportStartDate = "'" & ActiveWorkbook.Worksheets("Intro").Range("$B$1").Value & "'"
ReportEndDate = "'" & ActiveWorkbook.Worksheets("Intro").Range("$B$2").Value & "'"
' There are 3 directpivot odbc connections/caches that need to be modified.
' In each query, the default report-end-date is specified by CURDATE().
' The default report-start-date is specified as '2010-01-01'
' Replace these defaults with the values retrieved above.
Dim cn As WorkbookConnection
Dim odbcCn As ODBCConnection
Dim originalsqltext, newsqltext
For Each cn In ThisWorkbook.Connections ' loop through the connections
If cn.Type = xlConnectionTypeODBC Then
Set odbcCn = cn.ODBCConnection
originalsqltext = odbcCn.CommandText
If odbcCn.Parent = "Calls" Then
newsqltext = Replace(originalsqltext, "CURDATE()", ReportEndDate)
newsqltext = Replace(newsqltext, "'2010-01-01'", ReportStartDate)
ElseIf odbcCn.Parent = "Suboutcomes" Then
newsqltext = Replace(originalsqltext, "CURDATE()", ReportEndDate)
newsqltext = Replace(newsqltext, "'2010-01-01'", ReportStartDate)
ElseIf odbcCn.Parent = "QtyCallsPerDay1" Then
newsqltext = Replace(originalsqltext, "CURDATE()", ReportEndDate)
Else
newsqltext = originalsqltext
End If
odbcCn.CommandText = newsqltext
odbcCn.Refresh
odbcCn.CommandText = originalsqltext
End If
cn.Refresh ' refresh the other connection without modification
Next
Set cn = Nothing
Set odbcCn = Nothing
But it's throwing me an error when it gets to the odbcCn.CommandText = newsqltext Run-time error '1004: Application-defined or object-defined error. which is so not helpful...
I verified that newsqltext contained what I intended, it just won't assign back into CommandText.
After another day of googling, and some simple macro-recording experiments, it looks like changing the CommandText requires syntax like
.CommandText = Array( _
"SELECT C1.CALLID, C1.TAKENON, C1A.TAKENAT, CAST(CONCAT(DATE_FORMAT(TAKENON,'%c/%e/%y'),' ',TIME_FORMAT(TAKENAT,'%H:" _
, _
"%i:%s')) AS CHAR) AS CallDateTime, YEAR(C1.TAKENON) AS Year, CEILING(MONTH(C1.TAKENON)/3) AS Quarter, MONTH(C1.TAKE" _
, _
(I'm leaving off the rest because it's huge)... at first I thought that was my problem, since when I tried recording the macro initially, I was getting a "too many line continuations" error, so I shortened the query as much as possible, getting it down to 1428 chars before the substitution. After substitution, it ends up at 1448 chars... but how do I parse it into the array format the code wants? Or is there some better way to do this?
I really don't want to mangle my queries like this, just to be able to edit them with vba, and I feel like I'm just missing something for how to alter the CommandText...
There were a couple of troubling things that my searching turned up, like this issue about not being able to change the CommandText on an odbc connection unless you changed it to an oledb first, then you could change the CommandText, then change the connection back to odbc... but that was prior to Excel 2010, which no longer uses these... http://p2p.wrox.com/excel-vba/29037-cant-set-commandtext-property-if-cache-has-1-rpt.html
The KnowledgeBase article linked to in there, http://support.microsoft.com/kb/816562, is even more alarming... I thought I was onto a solution when I saw the StringToArray function, but then I read further and saw
Note The previous code may not work as you expect if you are using shared PivotCaches, an OLAP-based PivotTables, or a Multiple Consolidation Range-based PivotTables to connect to the database.
and then
If multiple PivotTables on a worksheet are derived from the same PivotTable, the subroutine does not work after it processes the first PivotTable. As of March 2003, there is no known workaround for this problem.
although it notes that the article only applies to Excel 2000 through 2003.
One other thing I tried, I thought maybe I could use ? parameters, and just set them with vba... but when I created a simple query with parameters, then recorded a macro while I pointed the parameters to new cell references, the macro contained only this: Sub PARAMEDIT5() ' ' PARAMEDIT5 Macro '
'
With ActiveWorkbook.Connections("PARAMEDIT").ODBCConnection
.BackgroundQuery = False
.CommandText = Array("SELECT * FROM Calls1 where TAKENON > ?" _
)
.CommandType = xlCmdSql
.Connection = _
"ODBC;DRIVER={MySQL ODBC 5.1 Driver};UID=xxxxxxx;PWD=xxxxxxxx;SERVER=xxxxxx;PORT=3306;BIG_PACKETS=1;"
.RefreshOnFileOpen = False
.SavePassword = True
.SourceConnectionFile = ""
.SourceDataFile = ""
.ServerCredentialsMethod = xlCredentialsMethodIntegrated
.AlwaysUseConnectionFile = False
End With
With ActiveWorkbook.Connections("PARAMEDIT")
.Name = "PARAMEDIT"
.Description = ""
End With
ActiveWorkbook.Connections("PARAMEDIT").Refresh
End Sub
I tried this with both the direct-into-pivot type of connection, and with a regular table connected to an external data source, which I know does support parameters.
So... does anyone know what is the correct way to parameterize the query for a shared pivot cache odbc connection ?
UPDATE: I tried this:
Dim cn, originalCn, newCn As WorkbookConnection
Dim odbcCn As ODBCConnection
Dim originalsqltext, newsqltext
Dim connStr As String
For Each cn In ThisWorkbook.Connections ' loop through the connections
If cn.Type = xlConnectionTypeODBC Then
Set odbcCn = cn.ODBCConnection
originalsqltext = odbcCn.CommandText
Set originalCn = cn
connStr = odbcCn.Connection
Select Case odbcCn.Parent
Case "Calls", "Suboutcomes"
newsqltext = Replace(originalsqltext, "CURDATE()", ReportEndDate)
newsqltext = Replace(newsqltext, "'2010-01-01'", ReportStartDate)
Case "QtyCallsPerDay1"
newsqltext = Replace(originalsqltext, "CURDATE()", ReportEndDate)
Case Else
newsqltext = originalsqltext
End Select
Set newCn = ActiveWorkbook.Connections.Add(odbcCn.Parent & "New", "WhoCares", connStr, newsqltext)
Set cn = newCn
cn.Refresh
Set cn = originalCn
newCn.Delete
Else
cn.Refresh ' refresh any other connections without modification
End If
Next
Set cn = Nothing
Set odbcCn = Nothing
Set newCn = Nothing
Set originalCn = Nothing
And while it seems to do what I want in terms of getting the commandtext to what I want, cn.Refresh does nothing when I step through. If it was refreshing, but my pivots weren't updating, I could see where maybe they're looking for Calls1 and at the moment the refresh happens, it's named to Calls1New, but the connection is just not doing anything (the query usually takes a few minutes to complete). Or maybe I can't assign it to the existing connection with the same name? After I set cn = newCn, they both look exactly the same, with same name.
I'll poke around some more, but if anybody else has done something like this, I'd appreciate more help. Thanks much for what you've given so far!
EDIT: so I'm back to the original
odbcCn.CommandText = newsqltext
cn.Refresh
odbcCn.CommandText = originalsqltext
I also tried odbcCn.CommandText = StringToArray(newsqltext) cn.Refresh odbcCn.CommandText = StringToArray(originalsqltext) that I found at http://support.microsoft.com/kb/816562. neither one worked.
I'll post the originalsqltext and newsql text, as they are right before the error. Note the the originalsqltext works fine if I manually paste it into the dialog box for the query, as does newsqltext
**removed earlier edit, due to new information **
note - I found a thread Excel VBA: Update Pivot Sourcedata that seems like a similar problem - because I have tested trying to assign odbcCn.CommandText = originalsqltext (which hasn't been altered in any way) and it fails also. However, this thread is from 2009, so it is most likely not using excel 2010, because I tried to write
For Each pvtC In ThisWorkbook.PivotCaches
name = pvtC.WorkbookConnection.name
originalsqltext = pvtC.CommandText
pvtC.CommandText = originalsqltext
Next
and it also fails right at pvtC.CommandText = originalsqltext
UPDATE: I'm certain now that this has nothing to do with the query itself, but rather the condition of having multiple pivot tables pointed to the same pivotcache. I created a new external datasource with the simple query
SELECT * FROM clientdashboard1.Calls1 WHERE TAKENON BETWEEN '2010-01-01' AND CURDATE()
as its query. I named the connection AlphaTest, and created a pivot table from it, then copied that pivot table to another sheet and used different fields. I modified my code to run through this first:
For Each pvtC In ThisWorkbook.PivotCaches
name = pvtC.WorkbookConnection.name
If name = "AlphaTest" Then
originalsqltext = pvtC.CommandText
pvtC.CommandText = originalsqltext
End If
Next
It failed at exactly the same point, pvtC.CommandText = originalsqltext
then I removed the second pivot table, and stepped through again, and it worked.
Then just for kicks, I plopped my original huge query in, and stepped through again. It worked. However, it uncovered another wrinkle... changing CommandText via code causes it to refresh. So my original plan, of making substitutions, doing a refresh, and then setting back to the original, is not going to work, as the tables will refresh again at the second assignment (if it worked, that is).
UPDATE This just gets better and better. I though about making dummy copies of my pivot caches, with just 1 single record perhaps, point each pivot table to its own cache, then have vba alter the query of the "real" one, then loop through and point each pivot table to that one. Do the reporting tasks (copy out sheets, break links, save-as, close is how we usually do it). Then back in the original book, point all the pivot tables to their respective dummy caches. Well, you know that AlphaTest datasource that I had? I thought that pvtC.CommandText = originalsqltext actually changed the query and caused AlphaTest to refresh... oh no my friends. It created a new connection, called Connection, which is a copy of AlphaTest. Ok Fine. How can I use this ? ..... I have some ideas I'm going to try, but if anyone else has dealt with this, please, please.... I'm going to try something like the approach Tim suggested... it's just that I won't be able to directly alter the pivotcache that the tables are connected to, I'll have to have them hooked to a default shared connection, have an editable connection (with no pivot tables connected), edit that one with my parameters, point each pivot table to it, do the reporting tasks, then point the pivot tables back to the default shared connection... If this works I'll be very happy.
Sub ChangeCon() Dim wc As WorkbookConnection Dim pvt As PivotTable Set wc = ActiveWorkbook.Connections.Add("NewConn", "Nice method", "Connstring", "SQL-STRING") Set pvt = ActiveSheet.PivotTables(1) pvt.ChangeConnection wc End Sub
If I'm reading it correctly... the suggestion is to create a new connection with the new query text, and then set the pivot table to point to the new connection... – CompanionCube