adding namespaces 1

[sjakiePP]

Hello all,

In Excel 2003 I want to copy the contents of XML files. I open the original and copy the content to a new xml file. Also I can change the number of a certain element, the first one under the root element. My xml files contain namespaces.

This is done with help of XPath. But to use this I have to add the namespaces (XMLDoc.setProperty "SelectionNamespaces", strNs. strNS is a variable that contains the namespaces).

When I run the script for the first time, everything works. But the second time I get error number -2147467259 and the text says "Duplicate attribute".

What causes this and how do I resolve it?

If my description is not clear please ask.

Thanks in advance,
Sjakie.

---------------------------------------------
Yes, the world is full of strange people.

 

[sjakiePP]

Above problem occurs when I use MSXML2.DOMDocument60. When I use MSXML2.DOMDocument also, but with "On error resume next" I can continue without problem. This is not the case with MSXML2.DOMDocument60, "XMLDoc.selectNodes(strXPath)" will come with an error.

I hope someone has the answer to my question.

---------------------------------------------
Yes, the world is full of strange people.

 

[tsuji]

>I hope someone has the answer to my question.
Maybe subconciously, you hope the contrary with the info shown.

>But the second time I get error number -2147467259 and the text says "Duplicate attribute".
If at the first execution, you add some attribute named x to certain set of elements which thereby makes the element elem say of this form.
[tt] <elem x="abc">...</elem>[/tt]

Upon the second excution, the same routine will find again "elem" in the nodelist and hence it attempts to add again the attribute x to it. This will result in error of duplicate attribute, as this is inadmissible.
[tt] <elem x="abc" x="123">...</elem> <!-- wrong -->[/tt]
It does not necessarily have anything to do with the setProperty.

 

[sjakiePP]

Thanks tsjui for your answer. I think you are right. But what I do not understand is that at the end of the first run everything is set to Nothing. Should not that prevent things like <elem x="abc" x="123">...</elem>? Also at the begin of the run i create new msxml documents (Dim XMLDoc As New MSXML2.DOMDocument). And as an extra "Set XMLDoc = New MSXML2.DOMDocument".

I start my runs with a click on a button in my Excel worksheet.

---------------------------------------------
Yes, the world is full of strange people.

 

[PHV]

But the second time I get error
Don't you try to work with an already processed file ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[combo]

Try to debug your code with 'Break on all errors' set (tools>options, g'general' tab). What is your strXPath?
I have no problems in executing twice the same XMLDoc.setProperty "SelectionNamespaces", .. .


combo

 

[sjakiePP]

What do you mean with an already processed file? When I create the new xml document, first I delete the old one. Does Excel keep a sort of lock on the file?

After I set 'Break on all errors' the first run stopped when I try to delete the new xml document. After I solved this one, the first run had no problem at all. The second one stopped at XMLDoc.setProperty "SelectionNamespaces", strNs.

---------------------------------------------
Yes, the world is full of strange people.

 

[combo]

.. and the error message is (duplicate attribute?)? Is strNs proper namespaces string?

combo

 

[combo]

I got the error (duplicate attribute) when tried to add two namespaces with the same name (in strNS in your code).

combo

 

[sjakiePP]

The contents of strNS is copied out of the original. But it contains several urls. These are a few of them: xmlns:sch="

http://www.ascc.net/xml/schematron"

xmlns:xsi="

http://www.w3.org/2001/XMLSchema-instance"

xmlns:gml="

http://www.opengis.net/gml"

xmlns:xlink="

http://www.w3.org/1999/xlink".

In Excel I listed these under each other, on several rows. In my code I put them after each other.

My goal was to make a script to copy the contents of xml files, regardless of which one. So I can use it when I need it.

Adding 2 namespaces is what happens, I think. But I do not understand why. Everything is set to Nothing.

---------------------------------------------
Yes, the world is full of strange people.

 

[combo]

Chech if all names (sch, xsi, gml, xlink...) are unique. They can't duplicate.

combo

 

[sjakiePP]

Yes, that was the error message. Duplicate attribute. The error number is -2147467259 (80004005).

---------------------------------------------
Yes, the world is full of strange people.

 

[sjakiePP]

How do I do that? During runtime I used getProperty("SelectionNamespaces"), but it returns an empty string. This is before the namespaces are added to the document.

---------------------------------------------
Yes, the world is full of strange people.

 

[tsuji]

If every time it is executed, strNS is unchanged, why should it be a problem as such? If you have "on error" directive in your function, take it out. You are just extending your imagination, that's all.

 

[combo]

Sjake,
Initially 'SelectionNamespaces' are empty. After 'setProperty' you assign a set of namespaces. If you execute it again, you overwrite them with a new string. The problem is that in the strNS string you have duplicated namespaces (not URLs - they can be the same). Just check the whole string for duplicate names.

combo

 

[sjakiePP]

Because otherwise I cannot use my xpath. This contains the element that has to be copied several times.

This is my current version:

    If StrComp(strNs, vbNullString) <> 0 Then
        On Error Resume Next
        Debug.Print "SelectionNamespaces: " & XMLDoc.getProperty("SelectionNamespaces") & "."
        XMLDoc.setProperty "SelectionNamespaces", vbNullString
        If StrComp(XMLDoc.getProperty("SelectionNamespaces"), vbNullString) = 0 Then XMLDoc.setProperty "SelectionNamespaces", strNs
        
        Debug.Print "SelectionNamespaces: " & XMLDocNew.getProperty("SelectionNamespaces") & "."
        XMLDocNew.setProperty "SelectionNamespaces", vbNullString
        If StrComp(XMLDocNew.getProperty("SelectionNamespaces"), vbNullString) = 0 Then XMLDocNew.setProperty "SelectionNamespaces", strNs
        On Error GoTo Error_Func
    End If

This way I can run my code several times.

---------------------------------------------
Yes, the world is full of strange people.

 

[sjakiePP]

THATS IT!!!!

Thanks Combo!!!

---------------------------------------------
Yes, the world is full of strange people.

 

[combo]

I am talking about the contents of strNS.
You can't have it like

[ignore]xmlns:sch="[URL unfurl="true"]http://www.ascc.net/xml/schematron"[/URL] xmlns:[/ignore][!]sch[/!][ignore]="[URL unfurl="true"]http://www.w3.org/2001/XMLSchema-instance"[/URL][/ignore]

(doubled sch - setProperty fails).

combo

 

[combo]

combo

 

[tsuji]

[1] The whole code block is just a lot of lines about nothing. It is no difference from this.
[tt]
If StrComp(strNs, vbNullString) <> 0 Then
XMLDoc.setProperty "SelectionNamespaces", strNs
XMLDocNew.setProperty "SelectionNamespaces", strNs
End If
[/tt]
[2] If you set up strNs somewhere above. You have to double up the uri, like this.
[tt][ignore]
strMs="SelectionNamespaces","xmlns:sch=""

http://www.ascc.net/xml/schematron""

xmlns:xsi=""

http://www.w3.org/2001/XMLSchema-instance""

xmlns:gml=""

http://www.opengis.net/gml""

xmlns:xlink=""

http://www.w3.org/1999/xlink"""

[/ignore][/tt]
Do you get it right on this?