I am looking for a less verbose way of creating hierarchical repeating XML with VBA and MSXML. At the moment I have a spreadsheet that lays out my XML levels like so
Intro Level 1 - Exam Section Level 2 - Exam Section Level 2 - Exam Section Level 2 - Exam Section Level 2 - Exam Section Level 1 - Exam Section Level 2 - Exam Section Level 2 - Exam Section Exit
This is a variant in VBA and the order is maintained
I have the following code that works fine - the Intro and Exit creation are fine as they are different to the section node. But the section node has repeating factors - rather than using the if logic of what level am I at and doing 5 times the same code (making it a nightmare to maintain) - is there a simpler way for me to iterate the levels and add to the parent.
We can assume that the if there is a level1 and then a level2 the parent of that level 2 would be the preceding level1
'loop the sections to add to dom structure - this uses a range in the spreadsheet called ExamSections
For i = LBound(varExamSections) To UBound(varExamSections)
'dont add blank values from the spreadsheet
If varExamSections(i, 1) <> "" Then
If varExamSections(i, 1) = "Intro" Then
'add intro section element
Set introSection = dom.createElement("section")
assessment.appendChild introSection
introSection.setAttribute "ident", varExamSections(i, 2)
'add intro section proc extension
Set sectionprocExtension = dom.createElement("sectionproc_extension")
introSection.appendChild sectionprocExtension
'add intro section control
Set SectionControl = dom.createElement("sectioncontrol")
sectionprocExtension.appendChild SectionControl
SectionControl.setAttribute "end_assessment", "false"
End If
If varExamSections(i, 1) = "Level 1 - Exam Section" Then
'add level section element
Set sectionLvl1 = dom.createElement("section")
assessment.appendChild sectionLvl1
sectionLvl1.setAttribute "ident", varExamSections(i, 2)
If varExamSections(i, 3) <> "" Then
sectionLvl1.setAttribute "title", varExamSections(i, 3)
End If
End If
If varExamSections(i, 1) = "Level 2 - Exam Section" Then
'add level section element
Set sectionLvl2 = dom.createElement("section")
sectionLvl1.appendChild sectionLvl2
sectionLvl2.setAttribute "ident", varExamSections(i, 2)
If varExamSections(i, 3) <> "" Then
sectionLvl2.setAttribute "title", varExamSections(i, 3)
End If
End If
If varExamSections(i, 1) = "Level 3 - Exam Section" Then
'add level section element
Set sectionLvl3 = dom.createElement("section")
sectionLvl2.appendChild sectionLvl3
sectionLvl3.setAttribute "ident", varExamSections(i, 2)
If varExamSections(i, 3) <> "" Then
sectionLvl3.setAttribute "title", varExamSections(i, 3)
End If
End If
If varExamSections(i, 1) = "Level 4 - Exam Section" Then
'add level section element
Set sectionLvl4 = dom.createElement("section")
sectionLvl3.appendChild sectionLvl4
sectionLvl4.setAttribute "ident", varExamSections(i, 2)
If varExamSections(i, 3) <> "" Then
sectionLvl4.setAttribute "title", varExamSections(i, 3)
End If
End If
If varExamSections(i, 1) = "Level 5 - Exam Section" Then
'add level section element
Set sectionLvl5 = dom.createElement("section")
sectionLvl4.appendChild sectionLvl5
sectionLvl5.setAttribute "ident", varExamSections(i, 2)
If varExamSections(i, 3) <> "" Then
sectionLvl5.setAttribute "title", varExamSections(i, 3)
End If
End If
If varExamSections(i, 1) = "Exit" Then
'add level section element
Set sectionExit = dom.createElement("section")
assessment.appendChild sectionExit
End If
'exam_reporting_groupref.setAttribute "linkrefid", varReportingGroups(i, 2)
End If
Next i
Any help would be greatly appreciated.
Aucun commentaire:
Enregistrer un commentaire