Programmatically Mapping XML Elements to Cells in Excel using VBA

Question

I am creating a VBA Macro in Excel that allows me to create worksheets, set column headers, and import CSV files. The last part that I am having trouble with is mapping specific cells and column headers with XML.

I have included a truncated sample of my Macro so far, along with the XSD file and additional code for what I am looking to accomplish.

Macro:

Sub MyMacro()

'Create Sheets, headers
    Sheets("Sheet1").Select
    Sheets("Sheet1").Name = "Gen. Journal Line"
    Range("A1").Select
    ActiveCell.FormulaR1C1 = "UPGRADE"
    Range("B1").Select
    ActiveCell.FormulaR1C1 = "Gen. Journal Line"
    Range("C1").Select
    ActiveCell.FormulaR1C1 = "81"
    Range("A3").Select
    ActiveCell.FormulaR1C1 = "Journal Template Name"
    Range("B3").Select
    ActiveCell.FormulaR1C1 = "Line No."
    Range("C3").Select
    ActiveCell.FormulaR1C1 = "Journal Batch Name"
    Range("D3").Select
    ActiveCell.FormulaR1C1 = "Account Type"
    Range("E3").Select
    ActiveCell.FormulaR1C1 = "Account No."
    Range("F3").Select
    ActiveCell.FormulaR1C1 = "Posting Date"
    Range("G3").Select
    ActiveCell.FormulaR1C1 = "Document Type"
    Range("H3").Select
    ActiveCell.FormulaR1C1 = "Document No."
    Range("I3").Select
    ActiveCell.FormulaR1C1 = "Description"
    Range("J3").Select
    ActiveCell.FormulaR1C1 = "Bal. Account No."
    Range("K3").Select
    ActiveCell.FormulaR1C1 = "Amount"
    Range("L3").Select
    ActiveCell.FormulaR1C1 = "Debit Amount"
    Range("M3").Select
    ActiveCell.FormulaR1C1 = "Credit Amount"
    
'Set CSV paths
    Dim GenJournalLinePath As String
    GenJournalLinePath = Application.ActiveWorkbook.Path + "\CSV Exports\Dynamics NAV Data\Nav 
    Data_GenJournalLine.csv"

'Import CSV data
    Sheets("Gen. Journal Line").Select
    Range("A4").Select
    Application.CutCopyMode = False
    With ActiveSheet.QueryTables.Add(Connection:= _
        "TEXT;" & GenJournalLinePath _
        , Destination:=Range("$A$4"))
        .Name = "Nav Data_GenJournalLine"
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .TextFilePromptOnRefresh = False
        .TextFilePlatform = 437
        .TextFileStartRow = 1
        .TextFileParseType = xlDelimited
        .TextFileTextQualifier = xlTextQualifierDoubleQuote
        .TextFileConsecutiveDelimiter = False
        .TextFileTabDelimiter = False
        .TextFileSemicolonDelimiter = False
        .TextFileCommaDelimiter = True
        .TextFileSpaceDelimiter = False
        .TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1)
        .TextFileTrailingMinusNumbers = True
        .Refresh BackgroundQuery:=False
    End With
    ActiveSheet.UsedRange.EntireColumn.AutoFit

End Sub

XSD:

<?xml version="1.0" encoding="UTF-8"?>
<xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema" elementFormDefault="qualified" attributeFormDefault="unqualified">
    <xs:element name="DataList">
        <xs:complexType>
            <xs:sequence>
                <xs:element name="GenJournalList">
                    <xs:complexType>
                        <xs:sequence>
                            <xs:element name="TableID" type="xs:int"></xs:element>
                            <xs:element name="PackageCode" type="xs:string"></xs:element>
                            <xs:element name="GenJournal" maxOccurs="unbounded">
                                <xs:complexType>
                                    <xs:sequence>
                                        <xs:element name="JournalTemplateName" type="xs:string"></xs:element>
                                        <xs:element name="LineNo" type="xs:string"></xs:element>
                                        <xs:element name="JournalBatchName" type="xs:string"></xs:element>
                                        <xs:element name="AccountType" type="xs:string"></xs:element>
                                        <xs:element name="AccountNo"></xs:element>
                                        <xs:element name="PostingDate" type="xs:string"></xs:element>
                                        <xs:element name="DocumentType"></xs:element>
                                        <xs:element name="DocumentNo" type="xs:int"></xs:element>
                                        <xs:element name="Description" type="xs:int"></xs:element>
                                        <xs:element name="BalAccountNo"></xs:element>
                                        <xs:element name="Amount" type="xs:decimal"></xs:element>
                                        <xs:element name="DebitAmount" type="xs:decimal"></xs:element>
                                        <xs:element name="CreditAmount" type="xs:decimal"></xs:element>
                                    </xs:sequence>
                                </xs:complexType>
                            </xs:element>
                        </xs:sequence>
                    </xs:complexType>
                </xs:element>
            </xs:sequence>
        </xs:complexType>
    </xs:element>
</xs:schema>

VBA Code to Import and Map XML Schema: (appended to end of Macro)

'Set XSD path
    Dim XSDpath As String
    XSDpath = Application.ActiveWorkbook.Path + "\Schema\GenJournalLineSchema.xsd"

'Import and map XML schema
    Set oXMLFile = CreateObject("Microsoft.XMLDOM")
    oXMLFile.Load (XSDpath)
    Range("C1").Select
    Set TableIDNode = oXMLFile.SelectNodes("/DataList/GenJournal/TableID/text()")
    Range("A1").Select
    Set PackageCodeNode = oXMLFile.SelectNodes("/DataList/GenJournal/PackageCode/text()")
    Range("A3").Select
    Set JournalTemplateNameNode = oXMLFile.SelectNodes("/DataList/GenJournal/JournalTemplateName/text()")
    Range("B3").Select    
    Set LineNoNode = oXMLFile.SelectNodes("/DataList/GenJournal/LineNo/text()")
    Range("C3").Select    
    Set JournalBatchNameNode = oXMLFile.SeLectNodes("/DataList/GenJournal/JournalBatchName/text()")
    Range("D3").Select
    Set AccountTypeNode = oXMLFile.SelectNodes("/DataList/GenJournal/AccountType/text()")
    Range("E3").Select
    Set AccountNoNode = oXMLFile.SelectNodes("/DataList/GenJournal/AccountNo/text()")
    Range("F3").Select
    Set PostingDateNode = oXMLFile.SelectNodes("/DataList/GenJournal/PostingDate/text()")
    Range("G3").Select
    Set DocumentTypeNode = oXMLFile.SelectNodes("/DataList/GenJournal/DocumentType/text()")
    Range("H3").Select
    Set DocumentNoNode = oXMLFile.SelectNodes("/DataList/GenJournal/DocumentNo/text()")
    Range("I3").Select
    Set DescriptionNode = oXMLFile.SelectNodes("/DataList/GenJournal/Description/text()")
    Range("J3").Select
    Set BalAccountNoNode = oXMLFile.SelectNodes("/DataList/GenJournal/BalAccountNo/text()")
    Range("K3").Select
    Set AmountNode = oXMLFile.SelectNodes("/DataList/GenJournal/Amount/text()")
    Range("L3").Select
    Set DebitAmountNode = oXMLFile.SelectNodes("/DataList/GenJournal/DebitAmount/text()")
    Range("M3").Select
    Set CreditAmountNode = oXMLFile.SelectNodes("/DataList/GenJournal/CreditAmount/text()")

I would expect that my XML Map is done automatically through this method, but I cant actually tell that anything has been done. I have not yet found an example of another user trying to do these XML maps programmatically through VBA, but I would hope it is possible.

Does anyone have any tips and/or examples of their own XML Maps done through VBA?

My current output