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

Tom,

Thanks!

steve

"Tom Ogilvy" wrote in message
...
Online analytical processing - uses the cube paradigm. It is like pivot
tables on steroids.

his statement:

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.

see, he says Analysis Server in my case - so I suspect he is going to a
data
server that has OLAP implemented. Cubes within the database indicate that
dimensions have been declared/established for the data to facilitate
online
analytical process.

I don't think he is seeing squares or question marks in his data.

Anyway, I answered his original question yesterday on how to fire the
wizard, but I guess he missed it. The macro recorder won't produce that
code.

--
Regards,
Tom Ogilvy


"steve" wrote in message
...
Tom,

What's an OLAP cube?

I do remember a while back a series of discussions about removing
rectangular symbols from text. Can't remember all of it, but the gist
was
that these were varied and many making Find/Replace difficult to use.

Here's one from Ariel font -
?

steve

"Tom Ogilvy" wrote in message
...
I wonder if he is talking about OLAP Cubes?

--
Regards,
Tom Ogilvy

steve wrote in message
...
Aparna,

Not sure but it sounds like you are seeing symbols (cubes or
rectangles)
that show up when you pull data from an outside source.

The hard way to get rid of them is to use find and replace. But
each
of
the
cubes could be a "different" character and this can be very
frustrating.

Another way is to let the pivot table filter them out. Once the
table
is
complete (manual construction) you can go into each field and set
the
filter
criteria (recording while you do it). One draw back is that it will
suppress the complete row of data so this may not be what you want.

Now you are getting out of my league and will need help from the
more
gifted
in the group.

steve

"Aparna Rege" wrote in message
...
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