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_TNAService
Dim objTableNodes As IXMLDOMNodeList
Dim objDataNode As IXMLDOMNode
Dim RowCount As Integer
Dim ColCount As Integer
Set objNList = objWS.wsm_Get_A21_Pivot
If Not objNList Is Nothing Then
objXmlDoc.LoadXml objNList(1).XML
Set objTableNodes = objXmlDoc.getElementsByTagName("Table")
If Not objTableNodes Is Nothing Then
RowCount = objTableNodes.Length
If objTableNodes.Length > 0 Then
ColCount = objTableNodes(0).childNodes.Length
Else
MsgBox ("No Data Available For DownLoad."), vbInformation
Exit Sub
End If
If objTableNodes.Length > 0 Then
ColCount = objTableNodes(0).childNodes.Length
Else
MsgBox ("No Data Available For DownLoad."), vbInformation
Exit Sub
End If
Worksheets("Work").Activate
Range("A1") = "#"
Range("b1") = "Occupational 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("Work").Cells(i + 2, j + 1) =
objDataNode.childNodes(j).Text
Next j
Next i
'Make pivot
Worksheets("Section A2").Activate
Dim PTCache As PivotCache
Dim pt As PivotTable
Set PTCache = ActiveWorkbook.PivotCaches.Add _
(SourceType:=xlDatabase, _
SourceData:=Sheets("Work").Range("A1").CurrentRegi on.Address)
Set pt = PTCache.CreatePivotTable _
(TableDestination:="A.2.1.", TableName:="Pivot1")
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.PivotTables("Pivot1").RowGrand = False
ActiveSheet.PivotTables("Pivot1").PivotFields(4).P ivotItems("M").Position = 1
ActiveSheet.PivotTables("Pivot1").PivotFields(4).P ivotItems("F").Position = 2
ActiveSheet.PivotTables("Pivot1").PivotFields(4).P ivotItems("Disabled").Position = 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
