Hi, I figured it out, but in a very convoluted way.
Basically, write the contents of the dataset into a worksheet, in my case
called "Work", then construct the pivot table programatically . After all
this, clean the workarea.
I would be very pleased if someone has a shorter, more efficient way to do
this.
Code below: (Note, Office XP Web Services toolkit and Xml Parser 6 must be
installed on user machine to consume the web service and parse the Xml)
Sub Get_A21_Pivot()
On Error GoTo Errhandler
Dim objXmlDoc As New DOMDocument
Dim objNList As IXMLDOMNodeList
Dim objWS As New clsws_TNAServic e
Dim objTableNodes As IXMLDOMNodeList
Dim objDataNode As IXMLDOMNode
Dim RowCount As Integer
Dim ColCount As Integer
Set objNList = objWS.wsm_Get_A 21_Pivot
If Not objNList Is Nothing Then
objXmlDoc.LoadX ml objNList(1).XML
Set objTableNodes = objXmlDoc.getEl ementsByTagName ("Table")
If Not objTableNodes Is Nothing Then
RowCount = objTableNodes.L ength
If objTableNodes.L ength > 0 Then
ColCount = objTableNodes(0 ).childNodes.Le ngth
Else
MsgBox ("No Data Available For DownLoad."), vbInformation
Exit Sub
End If
If objTableNodes.L ength > 0 Then
ColCount = objTableNodes(0 ).childNodes.Le ngth
Else
MsgBox ("No Data Available For DownLoad."), vbInformation
Exit Sub
End If
Worksheets("Wor k").Activate
Range("A1") = "#"
Range("b1") = "Occupation al Category"
Range("c1") = "Class"
Range("d1") = "Gender"
Range("e1") = "Qty"
For i = 0 To RowCount - 1
Set objDataNode = objTableNodes(i )
For j = 0 To ColCount - 1
Worksheets("Wor k").Cells(i + 2, j + 1) =
objDataNode.chi ldNodes(j).Text
Next j
Next i
'Make pivot
Worksheets("Sec tion A2").Activate
Dim PTCache As PivotCache
Dim pt As PivotTable
Set PTCache = ActiveWorkbook. PivotCaches.Add _
(SourceType:=xl Database, _
SourceData:=She ets("Work").Ran ge("A1").Curren tRegion.Address )
Set pt = PTCache.CreateP ivotTable _
(TableDestinati on:="A.2.1.", TableName:="Piv ot1")
With pt
.PivotFields(1) .Orientation = xlRowField 'Number
.PivotFields(2) .Orientation = xlRowField 'Category
.PivotFields(3) .Orientation = xlColumnField 'Race
.PivotFields(4) .Orientation = xlColumnField 'Gender
.PivotFields(5) .Orientation = xlDataField 'Count
End With
'Remove grand totals, set the order of the columns
ActiveSheet.Piv otTables("Pivot 1").RowGrand = False
ActiveSheet.Piv otTables("Pivot 1").PivotFields (4).PivotItems( "M").Positi on = 1
ActiveSheet.Piv otTables("Pivot 1").PivotFields (4).PivotItems( "F").Positi on = 2
ActiveSheet.Piv otTables("Pivot 1").PivotFields (4).PivotItems( "Disabled").Pos ition = 3
Call CleanWorkArea
Else
MsgBox ("No Data Available For DownLoad."), vbInformation
Exit Sub
End If
End If
"Andrew van Zyl" wrote:
Hi Folks
Can anyone direct me to a simple example of using a pivot table to consume a
dataset from a web service?
Using Web Service Reference Tool, Office 11. Web service comes from .NET
application
Thanks in advance!
Andrew
