is there any way to change datasource dynamically

Example.

I will three Command Button, each button refer to three data source

Button A - Production
Button B - Development
Button C - QA

based on click, pivot table will change the data source, if i press refresh
i will populate corresponding data

thanks

make the datasource for your pivot table a defined name such as Database.

then just have each button redefine the range Database refers to and then
refresh the pivot table.

--
Regards,
Tom Ogilvy


"Kalyan" wrote:

is there any way to change datasource dynamically

Example.

I will three Command Button, each button refer to three data source

Button A - Production
Button B - Development
Button C - QA

based on click, pivot table will change the data source, if i press refresh
i will populate corresponding data

thanks

Thanks Tom, situation here is, my datasource is three different SQL Server
(analysis server (AS2000)) popluating data from cubes.

Help please

Thanks
kalyan

"Tom Ogilvy" wrote:

make the datasource for your pivot table a defined name such as Database.

then just have each button redefine the range Database refers to and then
refresh the pivot table.

--
Regards,
Tom Ogilvy


"Kalyan" wrote:

is there any way to change datasource dynamically

Example.

I will three Command Button, each button refer to three data source

Button A - Production
Button B - Development
Button C - QA

based on click, pivot table will change the data source, if i press refresh
i will populate corresponding data

thanks

Create each individually with the macro recorder turned on.

then use the recorded code to get the code for making each the datasource.



--
Regards,
Tom Ogilvy


"Kalyan" wrote:

Thanks Tom, situation here is, my datasource is three different SQL Server
(analysis server (AS2000)) popluating data from cubes.

Help please

Thanks
kalyan

"Tom Ogilvy" wrote:

make the datasource for your pivot table a defined name such as Database.

then just have each button redefine the range Database refers to and then
refresh the pivot table.

--
Regards,
Tom Ogilvy


"Kalyan" wrote:

is there any way to change datasource dynamically

Example.

I will three Command Button, each button refer to three data source

Button A - Production
Button B - Development
Button C - QA

based on click, pivot table will change the data source, if i press refresh
i will populate corresponding data

thanks

THANKS, I am new to Excel programming do have any sample,

"Tom Ogilvy" wrote:

Create each individually with the macro recorder turned on.

then use the recorded code to get the code for making each the datasource.



--
Regards,
Tom Ogilvy


"Kalyan" wrote:

Thanks Tom, situation here is, my datasource is three different SQL Server
(analysis server (AS2000)) popluating data from cubes.

Help please

Thanks
kalyan

"Tom Ogilvy" wrote:

make the datasource for your pivot table a defined name such as Database.

then just have each button redefine the range Database refers to and then
refresh the pivot table.

--
Regards,
Tom Ogilvy


"Kalyan" wrote:

is there any way to change datasource dynamically

Example.

I will three Command Button, each button refer to three data source

Button A - Production
Button B - Development
Button C - QA

based on click, pivot table will change the data source, if i press refresh
i will populate corresponding data

thanks

I don't have access to anything that supports cubes - but that was the whole
point of my suggestion

Go into Tools=Macro=Record a new macro. In the dialog, specify personal
workbook.

Now create a pivot table to one of your sources.

when done, goto Tools=Macro=Stop Recording

Now do Alt+F11 and look in the project explorer for Personal.xls. find the
last module in that workbook and you should see the code recorded when you
created your pivot table. That is a code sample specific to your situation.

It will have a lot of extraneous select commands because that is what you
were physically doing, but it should also have commands that actually
created the pivot table and show the SQL string that was used. Examine
the code.

--
Regards,
Tom Ogilvy



"Kalyan" wrote in message
...
THANKS, I am new to Excel programming do have any sample,

"Tom Ogilvy" wrote:

Create each individually with the macro recorder turned on.

then use the recorded code to get the code for making each the
datasource.



--
Regards,
Tom Ogilvy


"Kalyan" wrote:

Thanks Tom, situation here is, my datasource is three different SQL
Server
(analysis server (AS2000)) popluating data from cubes.

Help please

Thanks
kalyan

"Tom Ogilvy" wrote:

make the datasource for your pivot table a defined name such as
Database.

then just have each button redefine the range Database refers to and
then
refresh the pivot table.

--
Regards,
Tom Ogilvy


"Kalyan" wrote:

is there any way to change datasource dynamically

Example.

I will three Command Button, each button refer to three data
source

Button A - Production
Button B - Development
Button C - QA

based on click, pivot table will change the data source, if i
press refresh
i will populate corresponding data

thanks