Dim doc As New XMLDocument() compile error

Question

I am trying to remove blank nodes from a XML file using Excel 2007 macro. I get a compile syntax error on line 1 of my code below. I have XML vs 6.0 as a reference. What am I missing?

Sub RemoveEmptyNodes()

Dim doc As New XMLDocument()
Dim nodes As XmlNodeList

doc.Load ("C:\xl_xml_data.xml")
XmlNodeList = doc.ChildNodes

For Each Node In nodes
If (Node Is Nothing) Then
nod.ParentNode.ParentNode.RemoveChild (nod.ParentNode)
End If
Next

End Sub

Answer 1

Are you writing VBA code or .NET code? If that is VBA code and you want to use MSXML 6 then you need to use the MSXML API. Your code however seems to try to use the .NET System.Xml namespace. I am not familiar with Excel macro coding so I am not sure what you want or have to use in that context. A reference to "XML vs 6.0" however sounds as if you want to use MSXML 6, in that case use the API documented at http://msdn.microsoft.com/en-us/library/ms763742%28v=vs.85%29.aspx.

---

MVP Data Platform Development My blog