mercredi 6 mai 2015

How can I create dynamic objects in VBA for creating MSXML

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