XML help needed

[naygl]

I have an xml file:

<Summary>
<JobNumber>111112</JobNumber>
<ClientCode>Client01</ClientCode>
<StartDate>Thursday, 21 April 2011</StartDate>
<StartTime>11:36:35</StartTime>
<SpoolList>
<SpoolFile>
<InputData>filename1.txt</InputData>
<Mailsets>7160</Mailsets>
<MailsetSheets>7160</MailsetSheets>
<MailsetImpressions>14320</MailsetImpressions>
<Tray1>0</Tray1>
<Tray2>0</Tray2>
<Tray3>0</Tray3>
<Tray4>0</Tray4>
<Tray5>0</Tray5>
<Tray6>0</Tray6>
<FirstMailset>Nathan and Rachel Paki</FirstMailset>
<LastMailset>Norman William Jack</LastMailset>
</SpoolFile>
<SpoolFile>
<InputData>filename2.txt</InputData>
<Mailsets>7160</Mailsets>
<MailsetSheets>7160</MailsetSheets>
<MailsetImpressions>14320</MailsetImpressions>
<Tray1>0</Tray1>
<Tray2>0</Tray2>
<Tray3>0</Tray3>
<Tray4>0</Tray4>
<Tray5>0</Tray5>
<Tray6>0</Tray6>
<FirstMailset>John and Jamie</FirstMailset>
<LastMailset>Tilman</LastMailset>
</SpoolFile>
</SpoolList>
<TotalSpoolFiles>2</TotalSpoolFiles>
</Summary>

...and I need to import the xml, create a sheet for each spool file using VBA and diplay the data. The sheets will be in the same format (currently one sheet is manually filled out and printed per spoolfile) so, for example, the InputData element will be in C11 and the Mailsets element will be in L14.

Any help would be greatly appreciated as I have not had much experience with xmlMaps etc. in VBA.

Cheers.

[Bob Phillips]

Have you tried importing the XML into a table, Data>From Other Sources>From XML Data Import?

[naygl]

Sorry I should have mentioned that it needs to be automated and the cardinality of the spoolfile element isn't always going to be two. It will probably be between one and 20. So I need to code this to create copies of sheet1 and enter data for each spoolfile in each sheet that is created.

Hope that makes sense.

[Bob Phillips]

My suggestion could be automated. Although I am not sure how the cardinality of the spoolfile is determined, assuming it can then you could iterate them all importing into new sheets.