Server-Side OWC10

[Cul]

Using OWC10, I am currently creating a pivot table on my web server,
using an XML file as the command text. After formatting the pivot
table, I then save it on the server and redirect the user to the saved
file. (All in old-fashioned ASP)
Now there is a request to supply a second pivot table on the same excel
file, using the same source data, and I have been struggling to find a
means to accomplish this. A point in the right direction would be
appreciated, or a quick, painless "impossible" would be appreciated as
well. An abbreviated version of my code is below...

Set oPivot = Server.CreateObject("OWC10.PivotTable")
oPivot.ConnectionString = "Provider=MSPersist.1"
oPivot.CommandText = Server.MapPath(strFilePathXML)
Set oView = oPivot.ActiveView
Set oTheaterField = oView.FieldSets("Field1")
'A bunch more pivot table formatting, and then...
oPivot.Export Server.MapPath(strFilePathXLS),0
'Redirect to strFilePathXLS

Thanks for any help!

 

[Alvin Bruney - ASP.NET MVP]

So what exactly is the problem? You should just be able to create a new
pivot and load it to the xml file. If you already have done this and there
is an error, post the error message.

--
Regards,
Alvin Bruney [MVP ASP.NET]

[Shameless Author plug]
The Microsoft Office Web Components Black Book with .NET
Now Available @ www.lulu.com/owc
Forth-coming VSTO.NET - Wrox/Wiley 2006

 

[Cul]

Alvin, thanks for your quick reply. I actually tried hunting down your
black book at Barnes & Noble today with no success. I'll try Borders
this weekend.
Regarding your response, I don't understand your reference to the xml
data. I think the problem was that I didn't elaborate enough in my
original note (that coupled with my meager experience with OWC! ;-)).
Just to recap:
-I create the datasource for the pivot in XML
-I instantiate the Pivottable and link it to the XML through the
commandtext property of the pivot table
-I format the pivot table and save it on the web server as an .xls file
using the export method
-I redirect the user to the saved .xls file

Set oPivot = Server.CreateObject("OWC10.Piv­otTable")
oPivot.ConnectionString = "Provider=MSPersist.1"
oPivot.CommandText = Server.MapPath(sourcedata.xml)
Set oView = oPivot.ActiveView
'A bunch of pivot table formatting, and then...
oPivot.Export Server.MapPath "excelfile.xls­",0
'Redirect to excelfile.xls

The end result is the user gets an xls file with 2 sheets: the pivot
table and the source data. But can I create an xls file with 3 sheets:
two pivot tables sourced from the same data, with the source data in
the third sheet? My research in MSDN & the web leads me to believe
that I can't, because the 2 pivot tables are wholly separate objects in
OWC, but I'd love to hear that I'm mistaken. Perhaps the black book
will reveal an answer?

Thanks again!

 

[Alvin Bruney - ASP.NET MVP]

Ok, short answer. No you can't. I don't have a workaround for that either.

--
Regards,
Alvin Bruney [MVP ASP.NET]

[Shameless Author plug]
The Microsoft Office Web Components Black Book with .NET
Now Available @ www.lulu.com/owc
Forth-coming VSTO.NET - Wrox/Wiley 2006
-------------------------------------------------------



Alvin, thanks for your quick reply. I actually tried hunting down your
black book at Barnes & Noble today with no success. I'll try Borders
this weekend.
Regarding your response, I don't understand your reference to the xml
data. I think the problem was that I didn't elaborate enough in my
original note (that coupled with my meager experience with OWC! ;-)).
Just to recap:
-I create the datasource for the pivot in XML
-I instantiate the Pivottable and link it to the XML through the
commandtext property of the pivot table
-I format the pivot table and save it on the web server as an .xls file
using the export method
-I redirect the user to the saved .xls file

Set oPivot = Server.CreateObject("OWC10.Piv­otTable")
oPivot.ConnectionString = "Provider=MSPersist.1"
oPivot.CommandText = Server.MapPath(sourcedata.xml)
Set oView = oPivot.ActiveView
'A bunch of pivot table formatting, and then...
oPivot.Export Server.MapPath "excelfile.xls­",0
'Redirect to excelfile.xls

The end result is the user gets an xls file with 2 sheets: the pivot
table and the source data. But can I create an xls file with 3 sheets:
two pivot tables sourced from the same data, with the source data in
the third sheet? My research in MSDN & the web leads me to believe
that I can't, because the 2 pivot tables are wholly separate objects in
OWC, but I'd love to hear that I'm mistaken. Perhaps the black book
will reveal an answer?

Thanks again!