Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

adding namespaces 1

Status
Not open for further replies.
sjakiePP

sjakiePP

Programmer
Apr 6, 2004
104
0
0
NL
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.
 
Sort by date Sort by votes
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.
 
Upvote 0 Downvote
>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.
 
Upvote 0 Downvote
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.
 
Upvote 0 Downvote
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
 
Upvote 0 Downvote
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
 
Upvote 0 Downvote
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.
 
Upvote 0 Downvote
.. and the error message is (duplicate attribute?)? Is strNs proper namespaces string?

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

combo
 
Upvote 0 Downvote
The contents of strNS is copied out of the original. But it contains several urls. These are a few of them: xmlns:sch=" xmlns:xsi=" xmlns:gml=" xmlns: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.
 
Upvote 0 Downvote
Chech if all names (sch, xsi, gml, xlink...) are unique. They can't duplicate.

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

---------------------------------------------
Yes, the world is full of strange people.
 
Upvote 0 Downvote
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.
 
Upvote 0 Downvote
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.
 
Upvote 0 Downvote
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
 
Upvote 0 Downvote
Because otherwise I cannot use my xpath. This contains the element that has to be copied several times.

This is my current version:
Code: 
    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.
 
Upvote 0 Downvote
THATS IT!!!!

Thanks Combo!!!

---------------------------------------------
Yes, the world is full of strange people.
 
Upvote 0 Downvote
I am talking about the contents of strNS.
You can't have it like
Code: 
[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
 
Upvote 0 Downvote
[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="" xmlns:xsi="" xmlns:gml="" xmlns:xlink=""[/ignore][/tt]
Do you get it right on this?
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

BeckyMc
  • Locked
  • Question
Adding a Horizontal Range to an Array Varaible in Excel 7.1
Replies
9
Views
93
SkipVought
S
peterpcu
  • Locked
  • Question
Adding a logo image to word documents 1
Replies
7
Views
44
kjv1611
kjv1611
LGMan
  • Locked
  • Question
Adding Color to figures within the result of a formula 1
Replies
7
Views
26
LGMan
LGMan
olrac
  • Locked
  • Question
Excel 2010 -- adding date parameters to the SLQ query via the Connections properties
Replies
7
Views
50
Andrzejek
Andrzejek
JasonEnsor
  • Locked
  • Question
Excel 2010 VBA - Adding a class instances to a collection 1
Replies
2
Views
37
JasonEnsor
JasonEnsor

Part and Inventory Search

Sponsor

Back
Top