VBS/VBA difference
 

In vbs, I can parse an XML file with the following code:
Set xmlDoc = CreateObject("MSXML2.DOMDocument.4.0")
If (IsObject(xmlDoc) = False) Then
alert("DOM document not created. Check MSXML version used in
createXmlDomDocument.")
End If
xmlDoc.async = False
xmlDoc.validateOnParse=False
xmlDoc.load xmlFile
If xmlDoc.parseError.errorcode = 0 Then
'Walk from the root to each of its child nodes:
treeWalk(xmlDoc)
Else
Wscript.echo "There was an error in : " & xmlFile &" Line: " &
xmlDoc.parseError.line & " Column: " & xmlDoc.parseError.linepos
End If

Function treeWalk(node)
For Each child In node.childNodes
If (child.hasChildNodes) Then
treeWalk(child)
End If
Next
End Function

When I try to do this in Excel VBA, it chokes on the line "treeWalk(xmlDoc)"
saying Object doesn't support this property or method. I tried changing the
function definition to include As all-sorts-of-things, no change. I do have
Microsoft XML 4.0 included in the VBA Project References.

I'm obviously overlooking something basic (Visually Basic, that is). Can
someone point me in the right direction?

VBS/VBA difference
 

You need to remove the () around your object arguments. In VBA, it violates
syntax to enclose an argument in parens unless you are passing it to a
function that returns a value or you a using the CALL method.

this worked fine for me:

Sub AAA()
xmlFile = "L:\Dataspec.xml"
Set xmlDoc = CreateObject("MSXML2.DOMDocument.4.0")
If IsObject(xmlDoc) = False Then
'alert ("DOM document not created. " & _
"Check MSXML version used in createXmlDomDocument.")
End If
xmlDoc.async = False
xmlDoc.validateOnParse = False
xmlDoc.Load xmlFile
If xmlDoc.parseError.errorcode = 0 Then
'Walk from the root to each of its child nodes:
treeWalk xmlDoc
Else
' Wscript.echo "There was an error in : " & xmlFile &" Line: " &
'xmlDoc.parseError.line & " Column: " & xmlDoc.parseError.linepos
End If
End Sub

Function treeWalk(node)
For Each child In node.childNodes

If child.hasChildNodes Then
treeWalk child
End If
Next
End Function

--
Regards,
Tom Ogilvy


"SteveP" wrote:

In vbs, I can parse an XML file with the following code:
Set xmlDoc = CreateObject("MSXML2.DOMDocument.4.0")
If (IsObject(xmlDoc) = False) Then
alert("DOM document not created. Check MSXML version used in
createXmlDomDocument.")
End If
xmlDoc.async = False
xmlDoc.validateOnParse=False
xmlDoc.load xmlFile
If xmlDoc.parseError.errorcode = 0 Then
'Walk from the root to each of its child nodes:
treeWalk(xmlDoc)
Else
Wscript.echo "There was an error in : " & xmlFile &" Line: " &
xmlDoc.parseError.line & " Column: " & xmlDoc.parseError.linepos
End If

Function treeWalk(node)
For Each child In node.childNodes
If (child.hasChildNodes) Then
treeWalk(child)
End If
Next
End Function

When I try to do this in Excel VBA, it chokes on the line "treeWalk(xmlDoc)"
saying Object doesn't support this property or method. I tried changing the
function definition to include As all-sorts-of-things, no change. I do have
Microsoft XML 4.0 included in the VBA Project References.

I'm obviously overlooking something basic (Visually Basic, that is). Can
someone point me in the right direction?

VBS/VBA difference
 

Thanks, Tom! It was originally written as a function but not returning a
value, so I changed it to a Sub. That probably explains it.

Steve

"Tom Ogilvy" wrote:

You need to remove the () around your object arguments. In VBA, it violates
syntax to enclose an argument in parens unless you are passing it to a
function that returns a value or you a using the CALL method.

this worked fine for me:

Sub AAA()
xmlFile = "L:\Dataspec.xml"
Set xmlDoc = CreateObject("MSXML2.DOMDocument.4.0")
If IsObject(xmlDoc) = False Then
'alert ("DOM document not created. " & _
"Check MSXML version used in createXmlDomDocument.")
End If
xmlDoc.async = False
xmlDoc.validateOnParse = False
xmlDoc.Load xmlFile
If xmlDoc.parseError.errorcode = 0 Then
'Walk from the root to each of its child nodes:
treeWalk xmlDoc
Else
' Wscript.echo "There was an error in : " & xmlFile &" Line: " &
'xmlDoc.parseError.line & " Column: " & xmlDoc.parseError.linepos
End If
End Sub

Function treeWalk(node)
For Each child In node.childNodes

If child.hasChildNodes Then
treeWalk child
End If
Next
End Function

--
Regards,
Tom Ogilvy


"SteveP" wrote:

In vbs, I can parse an XML file with the following code:
Set xmlDoc = CreateObject("MSXML2.DOMDocument.4.0")
If (IsObject(xmlDoc) = False) Then
alert("DOM document not created. Check MSXML version used in
createXmlDomDocument.")
End If
xmlDoc.async = False
xmlDoc.validateOnParse=False
xmlDoc.load xmlFile
If xmlDoc.parseError.errorcode = 0 Then
'Walk from the root to each of its child nodes:
treeWalk(xmlDoc)
Else
Wscript.echo "There was an error in : " & xmlFile &" Line: " &
xmlDoc.parseError.line & " Column: " & xmlDoc.parseError.linepos
End If

Function treeWalk(node)
For Each child In node.childNodes
If (child.hasChildNodes) Then
treeWalk(child)
End If
Next
End Function

When I try to do this in Excel VBA, it chokes on the line "treeWalk(xmlDoc)"
saying Object doesn't support this property or method. I tried changing the
function definition to include As all-sorts-of-things, no change. I do have
Microsoft XML 4.0 included in the VBA Project References.

I'm obviously overlooking something basic (Visually Basic, that is). Can
someone point me in the right direction?

VBS/VBA difference
 

Tom followed up with a nice post ....

Anyway...

Be certain that you add "Microsoft Scripting 1.0" in your reference
library - you might hit the lucky jackpot and get a definition for
that object.