Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations John Tel on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Create PivotTable for Excel from Javascript

Status
Not open for further replies.

DannyLondon

Programmer
Nov 19, 2002
33
GB
Hello all

I am trying to create a pivot table in Excel, driven by JavaScript from my intranet page. The code works beautifully within VBA, but it keeps falling over on the PivotCache object in JS. The code is below. I would be very grateful for any suggestions on why this may be.

I have looked into the PivotTableWizard method, but I want to use the MSOLAP driver, and this is not possible for the wizard.

// Open Excel
objApplication = new ActiveXObject("Excel.Application.9")
objApplication.visible = true;
objBook = objApplication.Workbooks.Add()

wksReport = objBook.Worksheets(1)
wksReport.Name = "Report"

objPivotCache = objApplication.ActiveWorkbook.PivotCaches.Add(2, "") // xlExternal
objPivotCache.Connection = Connection
objPivotCache.CommandType = 1 // xlCmdCube
objPivotCache.CommandText = "OCWCube"
objPivotCache.MaintainConnection = True

objPivotTable = objPivotCache.CreatePivotTable(Range("A1"), "DrillDown")

Thanks in advance
Danny...
 
What version of excel is running ??
pivotcache is only available from 2002 onwards (I think...it may be 2000 as well) so in xl97, you MUST use the WIZARD method (and maybe in 2000 as well) Rgds
Geoff
"Some cause happiness wherever they go; others whenever they go."
-Oscar Wilde
 
Hi Geoff

It is 2000 that I am running, and PivotCache is available.

Thanks
Danny... [rockband] [wiggle][wiggle][wiggle]
 
hmmm - could be tricky this as I only have xl97 available here but where is the error occuring ?? Rgds
Geoff
"Some cause happiness wherever they go; others whenever they go."
-Oscar Wilde
 
Geoff

The error occurs at:
objPivotCache = objApplication.ActiveWorkbook.PivotCaches.Add(2, "")

I get an error saying "Object doesn't support this property of method".

I have checked that it is not the Add method specifically by trying to return the PivotCaches.Count property. I have also tried substituting the ActiveWorkbook with the object which has been created earlier in the code, objBook.

All attempts result in the same error.

If you are able to shine any light, I would be very grateful.

Thanks
Danny... [rockband] [wiggle][wiggle][wiggle]
 
Have a look in this MSDN link - it's got a coupla examples of creating pivot tables using the pivotcache method. seems that there is no Add method, just a createpivottable method:

watch for word wrap Rgds
Geoff
"Some cause happiness wherever they go; others whenever they go."
-Oscar Wilde
 
Hi Geoff

The example you posted shows what I am trying to do. In the steps before the PivotTable object is created, a PivotCache must be added to the PivotCaches collection.

The first line of the example code shows this being done. This is the step that I cannot do from my intranet page.

Danny... [rockband] [wiggle][wiggle][wiggle]
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top