Help with this

[Chris]

Hi,
I have toyed for a while but can't get it to work. I an trying to populate
an excel sheet via a webservice. I am having a problem getting the column
names

Const XMLFileName = "C:\Test.XML"
Const Prod = "YES"
Const Date = "2006-10-10"
Const Location = "N"
Option Explicit



Private Sub CommandButton1_Click()


Dim obj As New clsws_Reports
'Set obj = New clsws_Reports

Dim strReturnValue As String 'XML string from the Web service
Dim bSaveFile As Boolean 'Saved the XML file

Dim intI As Integer 'Local Counters
Dim intJ As Integer

Dim strColumn As String
Dim strValue As String 'Values to be copied into cells
Dim strFieldName As String

Dim intOffset As Integer
intOffset = 10

Dim oRoot As MSXML2.IXMLDOMNode 'XML files's root
Dim NewXMLdocument As New MSXML2.DOMDocument

strReturnValue = obj.wsm_GetTotalQuantityByRoute(Prod, Date,
Location).Item(1).XML

bSaveFile = SaveToXML(XMLFileName, strReturnValue)


If bSaveFile Then
Call NewXMLdocument.Load(XMLFileName)
Set oRoot = NewXMLdocument.documentElement

'For intI = 0 To oRoot.childNodes.Length - 1
'
' For intJ = 0 To oRoot.childNodes.Item(intI).childNodes.Length - 1
'
' strValue =
oRoot.childNodes.Item(intI).childNodes.Item(intJ).Text
' strFieldName =
oRoot.childNodes.Item(intI).childNodes.Item(intJ).baseName
'
' Select Case strFieldName
' Case "route": strColumn = "A"
' Case "number": strColumn = "B"
' Case "name": strColumn = "C"
' Case "issue-number": strColumn = "D"
' Case "issue-name": strColumn = "E"
' Case "cover-date": strColumn = "F"
' Case "quantity": strColumn = "G"
' End Select
'
''ActiveSheet.Cells(intI + intOffset, strColumn).Value = strValue
'
'MsgBox (strFieldName)
''Debug.Print strFieldName, strValue
'
' Next
'Next

strValue = oRoot.childNodes.Item(0).childNodes.Item(1).Text
'strFieldName = oRoot.childNodes.Item(0).childNodes.Item(5).baseName
strFieldName = oRoot.childNodes.Item(0).childNodes.Item(5).nextSibling
-----> Here

MsgBox (strValue)
MsgBox (strFieldName)


End If
Set obj = Nothing

End Sub





This is a sample of the excel file

- <diffgr:diffgram xmlns:msdata="urn:schemas-microsoft-com:xml-msdata"
xmlns:diffgr="urn:schemas-microsoft-com:xml-diffgram-v1">
- <NewDataSet xmlns="">
- <Table diffgr:id="Table1" msdata:rowOrder="0">
<route>N000</route>
<number>7</number>
<name>SERA (NYC)</name>
<inumber>64155</issue-number>
<iname>WED 10/11/</issue-name>
<date>2006-10-11T00:00:00-04:00</cover-date>
<quantity>1</quantity>
</Table>
- <Table diffgr:id="Table2" msdata:rowOrder="1">
<route>N000</route>
<number>106</number>
<name>IL TEST</name>
<Inumber>641555</issue-number>
<iname>TUE 10/10/</issue-name>
<date>2006-10-10T00:00:00-04:00</cover-date>
<quantity>1</quantity>
</Table>
</NewDataSet>
</diffgr:diffgram>

The columns should be route,number,name,inumber,iname,date,quantity. I am
getting Table

Thanks