Microsoft xml v6.0 not working in excel 2013

[moe0303]

Related thread: http://www.mrexcel.com/forum/excel-questions/783092-new-computer-3.html

Hello,

I have a weird issue. I have spreadsheet with some macros that parse xml files and presents the data in relevant columns. The code was written in excel 2010 and works fine in that environment. When I try opening the file in 2013, I get a "User defined type not defined" error. If I change the reference to xml v3.0 instead of v6.0 it gets a little farther but eventually fails as well.

The code highlighted for the above error is:

Dim xmlFile As New MSXML2.DOMDocument

I tried changing DOMDocument to DOMDocument60, but also got errors (documented in the related thread). Internet searches have said something about they way DOMDocuments are exposed in 2013 is different. I'm not sure what any of that means. Have any of you guys ever heard of this problem?

-Moe

 

[chicagocomputerclasses]

Sorry, that should have been

sSTIG = xmlFile.SelectSingleNode("/a:Benchmark/a:title").Text
Debug.Print sSTIG

not

sSTIG = xmlElement.SelectSingleNode("/a:Benchmark/a:title").Text

 

[moe0303]

Sorry, that should have been

sSTIG = xmlFile.SelectSingleNode("/a:Benchmark/a:title").Text
Debug.Print sSTIG

not

sSTIG = xmlElement.SelectSingleNode("/a:Benchmark/a:title").Text

Run-time error '91': Object variable or With block variable not set on

sSTIG = xmlFile.SelectSingleNode("/a:Benchmark/a:title").Text

BTW, I really appreciate your help.

 

[chicagocomputerclasses]

What does your XML file look like?

 

[chicagocomputerclasses]

OK, thanks for the XML file.

The problem you have is that you need to define the namespace with the new library.

The namespace I gave you before was not accurate for your XML.
Use this one instead

 xmlFile.setProperty "SelectionNamespaces", "xmlns:a='http://checklists.nist.gov/xccdf/1.1'"

and later you should be able to retrieve the parts with the following syntax

sSTIG = xmlFile.SelectSingleNode("/a:Benchmark/a:title").Text

and something like this when you have a group of items

Set xGroupNodeList = xmlFile.SelectSingleNode("/a:Benchmark").SelectNodes("a:Group")

 

[moe0303]

I'm not sure I am making the changes appropriately (I'm not). Can you tell me which lines of code to change?

 

[moe0303]

ok, I think I am figuring it out now. It looks like I am having to change the syntax for a number of other lines. Can you tell me what the "/a:" part does, or why it is necessary? Just curious.

The code seems to be erroring wherever I see "SelectSingleNode" until I change the syntax.

 

[chicagocomputerclasses]

On this line we are selecting a namespace and call it "a".

 xmlFile.setProperty "SelectionNamespaces", "xmlns:[B]a[/B]='http://checklists.nist.gov/xccdf/1.1'"

Later we select something from that namespace by using "/a:"

You have to update the syntax everywhere in the code.