Changing the Command Text of a Connection w/ VBA

[Crizznitch]

This is something that may be easier now that I have upgraded to Excel 2007 from Excel 2003. What I currently have are several pivot tables that are linked to one SQL table. What I am trying to do is write a macro to change the Command Text (read: query) of the connection, so that I can change the year of data that is getting pulled into the pivot tables.

The command text for this year is:

SELECT Table1.Date, Table1.Field2, Table1.Field3
FROM DB1.dbo.Table1 Table1
WHERE (YEAR(Table1.Date)='2009')

Next year all that will change is the 2009 will become 2010.

Now I realize it is really easy to just select 'Change Data Source' -> 'Connection Properties' -> 'Definition' and change the year in the command text, but there will be other users of this document who will not be as familiar with Excel. They will also need to switch between past years and the current year from time to time which is why I would like to set up a macro to select a year to change the datasource to. If anyone has any experience changing the command text of a connection using VBA, I would love to get some help in fixing this problem.


Further note: In Excel 2003 I had no problem changing the definition, but doing that would only change one pivot table and destroy the connection to the other pivot tables. In Excel 2007, it seems that changing the definition will change all the pivot tables. That's why I am hoping this is easier in the new version of Excel.

 

[Crizznitch]

Bump

 

[Crizznitch]

bump