VBA to XML Macro

[jc0r]

Hi all

I am trying to create a macro that takes values from an input worksheet, compiles them into a valid XML, then writes this XML to an output worksheet.

The XML Schema (think thats the right word for it ) looks like the following

The values from the input sheet look like the following

These are horse races with H5:H14 being the runners (selections) of race 1 with cloth number referenced in column G, I5:I25 race 2 etc.

The amount of runners on a given day are dynamic so i need to loop until.

I would like the macro to fill in the selections and cloth numbers for each race, again, the amount of races can also change (in this example, there is no race 7 or 8).

On my output sheet, i would like to print something along the lines of the following based on the above example.

Can anyone offer any assistance with this please.

Kind regards

<ArrayOfCompactEventClassifier>
<CompactEventClassifier Name="Great Trethew" IsStartTimeSet="true" StartTime="2023-11-18T15:22:00+00:00" >
 <ChildEventClassifiers/>
<Markets>
<CompactMarket Name="12:00" StartTime="2023-11-18T12:00:00+00:00" >
<Selections>
 <CompactSelection Name="DEVON RED" ClothNumber="1" />
 <CompactSelection Name="DUNADEN ISLAND" ClothNumber="2" />
 <CompactSelection Name="HOLESHOT" ClothNumber="3" />
 </Selections>
 </CompactMarket>
 </Markets>
 </CompactEventClassifier>
 </ArrayOfCompactEventClassifier>