Changing the Command Text of a Connection w/ VBA


Board Regular
Nov 18, 2004
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.

Forum statistics

Latest member
Asraful Alam

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...