List of lists - exporting XML

[Rebam98]

Hi,
I get an error regarding "list of lists" when trying to export an XML file.
I read up on it and I found,
"One list of items contains a second list of items."

Am I to understand that if I have the following xml table:

<tagroot>
<tag1>
<tag2>stuff</tag2>
<tag2>stuff2</tag2>
<tag2>1stuff3</tag2>
</tag1>
<tag1>
<tag2>stuff</tag2>
<tag2>stuff2</tag2>
</tag1>
</tagroot>

... that excel can't handle it? Because I have 1 unbounded (repeatable) list of the tag1, and within tag1, I have another repeatable list (tag2)?

Insight is appreciated.
thanks.

 

[Norie]

What is does 'list of lists' mean?

And 'unbounded'?

 

[Rebam98]

Hi, sorry .. the tags I put in to show the xml example didn't show up. So instead of using "<" and ">" I'll use "{" and "}".

"List of lists" is the thing I am trying to figure out ... :-\ ???
"Unbounded" is just the term they use when you set the "max value" of a tag to "unbounded." It just means that the data can repeat itself and is not limited to just 1 (or any number) of entries.

So .... let's try this again.

I am trying to import an XML file to excel, be able to edit it in excel, then export it from excel. I'm using excel 2007.
On the developer tab, they have this import and export XML function.
I import the table but when I go to export it, they say they can't because of the error "List of lists."

I researched the error and it is as I described above, that the problem is that, "One list of items contains a second list of items."

So I'm just looking for confirmation that my interpretation of that is the same as others. What I take that to mean is that if I have the following XML:

{Organization}
{Company}
{Name}Company1{/Name}
{Employee}Emily{/Employee}
{Employee}Joe{/Employee}
{Employee}Sue{/Employee}
{/Company}
{Company}
{Name}Company2{/Name}
{Employee}John{/Employee}
{Employee}George{/Employee}
{Employee}Jen{/Employee}
{/Company}
{/Organization}

... that it could never be exported because I have a "list of lists." So the first "list" is that there are multiple companies. And then within that company I have another list, the list of employees.

That would be really crappy because this is a very basic XML file and if excel can't export a "list of lists" then it is by no means a good software to use to edit XML files.

So I hope my interpretation of the problem is wrong. I did some initial testing and indeed, if I remove the "Employee" tag it can export.

 

[jindon]

Where is all those strings ?

 

[Rebam98]

Can you clarify what you mean? The strings are in an XML file with a *.xml extension that I import into excel. I can get an example of an XML and the XSD file I use if needed.

 

[jindon]

try

Sub test()
Dim fn As String, temp As String
fn = "c:\test.xml"  '<- alter here (file path)
temp = CreateObject("Scripting.FileSystemObject").OpenTextFile(fn).ReadAll
temp = Replace(temp, vbCrLf, Chr(12))
With CreateObject("VBScript.RegExp")
    .Pattern = Chr(12) & "*< Employee ><EMPLOYEE>.+< /Employee ></EMPLOYEE>" & Chr(12) & "*"  '<- delete the sapce
    temp = .Replace(temp, "")
End With
Open Replace(fn, "xml","Revised.xml") For Output As #1
    Print #1, Replace(temp, Chr(12), vbCrLf)
Close #1
End Sub

 

[ian0411]

This worked. Thank you jindon.