Changing the Command Text of a Connection w/ VBA

Crizznitch

Board Regular
Joined
Nov 18, 2004
Messages
120
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:
Code:
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.
 

Some videos you may like

Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.

Watch MrExcel Video

Forum statistics

Threads
1,122,262
Messages
5,595,154
Members
413,971
Latest member
User786

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top