DYNAMICALLY data source for pivot table

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" <[email protected]> wrote in message
news:[email protected]...
> 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
>> > > >