Aparna,

This is a good opportunity to use the macro recorder.

Turn on the recorder and manually go through the steps of setting up the
Pivot Table. Turn off the recorder and than edit the code to suit your
needs. Remember that the recorder will record "more" than you really need,
and that it fixes the ranges to what you used.

Post back with your code and additional questions if you need help.

steve

"Aparna Rege" wrote in message
...
Hi,

I want to trigger the code behind an Excel commandbar button through vba
code without user selecting the command. To be specific I want to bring up
the 'Pivot Table and PivotChart Wizard' (Data PivotTable and PivotChart
Report) programmatically.
Does anybody know how to do this?

Thanks in advance,
Aparna

Steve,

I found a way to bring up the wizard, I can use Execute property of
commandbarcontrol.

I had already tried to use the macro, but that did not help. It would not
let me stop the macro recording until I go though all the steps in the
wizard. And it records something like this:

With ActiveWorkbook.PivotCaches.Add(SourceType:=xlExter nal)
.Connection = _
"OLEDB;Provider=MSOLAP.2;Data Source=TESTWIN2KS;Initial
Catalog=TESTDB;Client Cache Size=25;Auto Synch Period=10000"
.CommandType = xlCmdCube
.CommandText = Array("SalesDetail")
.MaintainConnection = True
.CreatePivotTable TableDestination:="[Book5]Sheet1!R3C1",
TableName:= _
"PivotTable5", DefaultVersion:=xlPivotTableVersion10
End With

What I am trying to do is: bring up the wizard window the first time (user
selects a custom created menu).
Next time after user selects another option from the same menu, use the
information he selected in the wizard the first time and take him to the
third or fourth window in the wizard. I thought recording macro is the way
to do it, but it seems it's not.

Thanks,
Aparna

"steve" wrote in message
...
Aparna,

This is a good opportunity to use the macro recorder.

Turn on the recorder and manually go through the steps of setting up the
Pivot Table. Turn off the recorder and than edit the code to suit your
needs. Remember that the recorder will record "more" than you really
need,
and that it fixes the ranges to what you used.

Post back with your code and additional questions if you need help.

steve

"Aparna Rege" wrote in message
...
Hi,

I want to trigger the code behind an Excel commandbar button through vba
code without user selecting the command. To be specific I want to bring
up
the 'Pivot Table and PivotChart Wizard' (Data PivotTable and
PivotChart
Report) programmatically.
Does anybody know how to do this?

Thanks in advance,
Aparna

Aparna,

I don't think that you can really step through a wizard with a macro. What
you can do is mimic the steps of the wizard by knowing what information is
needed. The code you sent shows all the inputs needed and how to write code
to make it happen.

Since you want to walk your people through a wizard type structure why not
set up a user form to gather the inputs. Than you can build variables into
the userform events to feed into the code to build the pivot table.

Let me know if that makes any sense.

steve

"Aparna Rege" wrote in message
...
Steve,

I found a way to bring up the wizard, I can use Execute property of
commandbarcontrol.

I had already tried to use the macro, but that did not help. It would not
let me stop the macro recording until I go though all the steps in the
wizard. And it records something like this:

With ActiveWorkbook.PivotCaches.Add(SourceType:=xlExter nal)
.Connection = _
"OLEDB;Provider=MSOLAP.2;Data Source=TESTWIN2KS;Initial
Catalog=TESTDB;Client Cache Size=25;Auto Synch Period=10000"
.CommandType = xlCmdCube
.CommandText = Array("SalesDetail")
.MaintainConnection = True
.CreatePivotTable TableDestination:="[Book5]Sheet1!R3C1",
TableName:= _
"PivotTable5", DefaultVersion:=xlPivotTableVersion10
End With

What I am trying to do is: bring up the wizard window the first time (user
selects a custom created menu).
Next time after user selects another option from the same menu, use the
information he selected in the wizard the first time and take him to the
third or fourth window in the wizard. I thought recording macro is the way
to do it, but it seems it's not.

Thanks,
Aparna

"steve" wrote in message
...
Aparna,

This is a good opportunity to use the macro recorder.

Turn on the recorder and manually go through the steps of setting up the
Pivot Table. Turn off the recorder and than edit the code to suit your
needs. Remember that the recorder will record "more" than you really
need,
and that it fixes the ranges to what you used.

Post back with your code and additional questions if you need help.

steve

"Aparna Rege" wrote in message
...
Hi,

I want to trigger the code behind an Excel commandbar button through
vba
code without user selecting the command. To be specific I want to
bring
up
the 'Pivot Table and PivotChart Wizard' (Data PivotTable and
PivotChart
Report) programmatically.
Does anybody know how to do this?

Thanks in advance,
Aparna

Steve,

This does make sense, the only problem would be: in the wizard, once you
select your datasource (which is an analysis server in my case) it shows the
databases on the server. After selecting the database, it shows cubes in
that database.
If we create a form, I would have to show them all this from which they can
select. To show all this information, I will probably have to use something
like DSO to connect to analysis server. I am trying to find a simpler way of
doing this, but I may have to go this route anyways.

Thanks,
Aparna

"steve" wrote in message
...
Aparna,

I don't think that you can really step through a wizard with a macro.
What
you can do is mimic the steps of the wizard by knowing what information is
needed. The code you sent shows all the inputs needed and how to write
code
to make it happen.

Since you want to walk your people through a wizard type structure why not
set up a user form to gather the inputs. Than you can build variables
into
the userform events to feed into the code to build the pivot table.

Let me know if that makes any sense.

steve

"Aparna Rege" wrote in message
...
Steve,

I found a way to bring up the wizard, I can use Execute property of
commandbarcontrol.

I had already tried to use the macro, but that did not help. It would
not
let me stop the macro recording until I go though all the steps in the
wizard. And it records something like this:

With ActiveWorkbook.PivotCaches.Add(SourceType:=xlExter nal)
.Connection = _
"OLEDB;Provider=MSOLAP.2;Data Source=TESTWIN2KS;Initial
Catalog=TESTDB;Client Cache Size=25;Auto Synch Period=10000"
.CommandType = xlCmdCube
.CommandText = Array("SalesDetail")
.MaintainConnection = True
.CreatePivotTable TableDestination:="[Book5]Sheet1!R3C1",
TableName:= _
"PivotTable5", DefaultVersion:=xlPivotTableVersion10
End With

What I am trying to do is: bring up the wizard window the first time
(user
selects a custom created menu).
Next time after user selects another option from the same menu, use the
information he selected in the wizard the first time and take him to the
third or fourth window in the wizard. I thought recording macro is the
way
to do it, but it seems it's not.

Thanks,
Aparna

"steve" wrote in message
...
Aparna,

This is a good opportunity to use the macro recorder.

Turn on the recorder and manually go through the steps of setting up
the
Pivot Table. Turn off the recorder and than edit the code to suit
your
needs. Remember that the recorder will record "more" than you really
need,
and that it fixes the ranges to what you used.

Post back with your code and additional questions if you need help.

steve

"Aparna Rege" wrote in message
...
Hi,

I want to trigger the code behind an Excel commandbar button through
vba
code without user selecting the command. To be specific I want to
bring
up
the 'Pivot Table and PivotChart Wizard' (Data PivotTable and
PivotChart
Report) programmatically.
Does anybody know how to do this?

Thanks in advance,
Aparna