How to skip to the next available XML node in a Do While Loop in VBA if one node is missing the XML source file

Question

Currently I am using a Excel VBA code to import specific nodes from a XML file into an Excel Spreadsheet. The XML file is a log generated by one of the equipment I am using, and for some reason on some logs, there are nodes missing. Below are example of a complete XML and a XML missing some nodes.

Excerpt of a complete XML file:

-<data>
    <totaltime>992.1</totaltime>
    <totaldist>0.0</totaldist>
   -<avg seg="0">
      <priamp>259.5</priamp>
      <bkgamp>159.2</bkgamp>
      <pritrav>4.99</pritrav>
      <bkgtrav>4.98</bkgtrav>
      <privolt>10.7</privolt>
      <bkgvolt>8.8</bkgvolt>
      <priwire>27.5</priwire>
      <bkgwire>17.8</bkgwire>
      <time_act>992.1</time_act>
      <heat>25.1</heat>
    </avg>
 </data>

Excerpt of a XML file missing nodes, i.e. <priwire> and <bkgwire>:

-<data>
    <totaltime>992.1</totaltime>
    <totaldist>0.0</totaldist>
   -<avg seg="0">
      <priamp>259.5</priamp>
      <bkgamp>159.2</bkgamp>
      <pritrav>4.99</pritrav>
      <bkgtrav>4.98</bkgtrav>
      <privolt>10.7</privolt>
      <bkgvolt>8.8</bkgvolt>
      <time_act>992.1</time_act>
      <heat>25.1</heat>
    </avg>
 </data>

Below is my VBA code that works for what I am doing, however once it finds one XML source with missing nodes the routing stops and return "Run-time error '91': Object variable or With variable not set".

Private Sub Create_Heading()
    ' This subroutine creates the necessary headings in the specified cells.

    Range("A1").Value = "Bead No."
    Range("B1").Value = "Duration (s)"
    Range("C1").Value = "Log #"
    Range("D1").Value = "Sched. ID"
    Range("E1").Value = "System ID"

    Range("A1:E1").WrapText = True
    Range("A1:A2").Merge
    Range("B1:B2").Merge
    Range("C1:C2").Merge
    Range("D1:D2").Merge
    Range("E1:E2").Merge


    Range("F2").Value = "Peak Current"
    Range("G2").Value = "Back Current"
    Range("H2").Value = "Peak Voltage"
    Range("I2").Value = "Back Voltage"
    Range("J2").Value = "Peak Travel Speed"
    Range("K2").Value = "Back Travel Speed"
    Range("L2").Value = "Peak Wire Speed"
    Range("M2").Value = "Back Wire Speed"

    Range("F1:M1").Merge
    Range("F1").Value = "Set"

    Range("N2").Value = "Peak Current"
    Range("O2").Value = "Back Current"
    Range("P2").Value = "Peak Voltage"
    Range("Q2").Value = "Back Voltage"
    Range("R2").Value = "Peak Travel Speed"
    Range("S2").Value = "Back Travel Speed"
    Range("T2").Value = "Peak Wire Speed"
    Range("U2").Value = "Back Wire Speed"

    Range("N1:U1").Merge
    Range("N1").Value = "Actual"

    Range("V2").Value = "Date (DD/MM/YY)"
    Range("W2").Value = "Start (hh:mm:ss)"
    Range("X2").Value = "End (hh:mm:ss)"
    Range("Y2").Value = "Duration (hh:mm:ss)"
    Range("Z2").Value = "Waiting Time (hh:mm:ss)"

    Range("V1:Z1").Merge
    Range("V1").Value = "Timeline"


    Range("A1:Z2").HorizontalAlignment = xlCenter
    Range("A1:Z2").VerticalAlignment = xlCenter
    Range("A1:Z2").Font.Bold = True

    Range("A1").ColumnWidth = 5
    Range("B1:E1").ColumnWidth = 8
    Range("F1:U1").ColumnWidth = 9
    Range("V1:Z1").ColumnWidth = 14

    Range("F2:Z2").WrapText = True

    ActiveSheet.Columns("V").NumberFormat = "dd/mm/yy"
    ActiveSheet.Columns("W").NumberFormat = "hh:mm:ss"
    ActiveSheet.Columns("X").NumberFormat = "hh:mm:ss"
    ActiveSheet.Columns("Y").NumberFormat = "hh:mm:ss"
    ActiveSheet.Columns("Z").NumberFormat = "hh:mm:ss"

End Sub


Sub XMLProcessing_rev0()
    Dim StrFile As String
    Dim Address As String
    Dim i As Integer
    Set xmlDoc = CreateObject("MSXML2.DOMDocument")
    Application.StatusBar = "Initializing..."

    ' Check if heading area is empty, if so create headings.
    If WorksheetFunction.CountA(Range("A1:Z2")) = 0 Then
        Create_Heading
    End If

    ' Prompt user to enter the file location
    Address = InputBox("Enter folder location of data files:", "Welding Parameter XML Processing") & "\"
    ' Macro will look for files that satisfy the path: "UserAddress\LOG*"  i.e. LOG____.xml files
    StrFile = dir(Address & "LOG*")

    i = 0
    ' This loop runs for every file in the folder
    Do While Len(StrFile) > 0
        Application.StatusBar = "Copying row " & i + 1 & "."

        ' Load the XML document
        xmlDoc.Load (Address & StrFile)

        ' Set values in worksheet to data found in XML

        Cells(3 + i, 1).Value = xmlDoc.SelectSingleNode("//log").getAttribute("weld")
        Cells(3 + i, 2).Value = xmlDoc.SelectSingleNode("//data/totaltime").Text
        Cells(3 + i, 3).Value = xmlDoc.SelectSingleNode("//log").getAttribute("number")
        Cells(3 + i, 4).Value = xmlDoc.SelectSingleNode("//sched").getAttribute("id")
        Cells(3 + i, 5).Value = xmlDoc.SelectSingleNode("//log").getAttribute("sn")

        Cells(3 + i, 6).Value = xmlDoc.SelectSingleNode("//seg/priamp").Text
        Cells(3 + i, 7).Value = xmlDoc.SelectSingleNode("//seg/bkgamp").Text
        Cells(3 + i, 8).Value = xmlDoc.SelectSingleNode("//seg/privolt").Text
        Cells(3 + i, 9).Value = xmlDoc.SelectSingleNode("//seg/bkgvolt").Text
        Cells(3 + i, 10).Value = xmlDoc.SelectSingleNode("//seg/pritrav").Text
        Cells(3 + i, 11).Value = xmlDoc.SelectSingleNode("//seg/bkgtrav").Text
        Cells(3 + i, 12).Value = xmlDoc.SelectSingleNode("//seg/priwire").Text
        Cells(3 + i, 13).Value = xmlDoc.SelectSingleNode("//seg/bkgwire").Text

        Cells(3 + i, 14).Value = xmlDoc.SelectSingleNode("//data/avg/priamp").Text
        Cells(3 + i, 15).Value = xmlDoc.SelectSingleNode("//data/avg/bkgamp").Text
        Cells(3 + i, 16).Value = xmlDoc.SelectSingleNode("//data/avg/privolt").Text
        Cells(3 + i, 17).Value = xmlDoc.SelectSingleNode("//data/avg/bkgvolt").Text
        Cells(3 + i, 18).Value = xmlDoc.SelectSingleNode("//data/avg/pritrav").Text
        Cells(3 + i, 19).Value = xmlDoc.SelectSingleNode("//data/avg/bkgtrav").Text
        Cells(3 + i, 20).Value = xmlDoc.SelectSingleNode("//data/avg/priwire").Text
        Cells(3 + i, 21).Value = xmlDoc.SelectSingleNode("//data/avg/bkgwire").Text

        Cells(3 + i, 22).Value = xmlDoc.SelectSingleNode("//log/time/day").Text & "/" & xmlDoc.SelectSingleNode("//log/time/mo").Text & "/" & xmlDoc.SelectSingleNode("//log/time/yr").Text
        Cells(3 + i, 23).Value = xmlDoc.SelectSingleNode("//log/time/hr").Text & ":" & xmlDoc.SelectSingleNode("//log/time/min").Text & ":" & xmlDoc.SelectSingleNode("//log/time/sec").Text
        Cells(3 + i, 24).Value = Cells(3 + i, 23).Value + (Cells(3 + i, 2).Value / 86400)
        Cells(3 + i, 25).Value = Cells(3 + i, 24).Value - Cells(3 + i, 23).Value


        ' Center all cells in the row b/c formatting is nice
        Range(Cells(3 + i, 1), Cells(3 + i, 26)).HorizontalAlignment = xlCenter

        ' Don't remember what this is for, but it probably resets the StrFile _
        variable to what it was before the loop
        StrFile = dir
        i = i + 1
    Loop

    ' Reset status bar
    Application.StatusBar = False
End Sub

I would like to modify the code in a way that if a specific node is missing in the XML file, the code should leave the corresponding cell empty and skip to the next cell, and not return error message.

I would appreciate any help you could give to me.

you need to try something like `dim o as object:set o=xmlDoc.SelectSingleNode("//log/time/day"):if o is nothing then.......else..Cells(3 + i, 22).Value = xmlDoc.SelectSingleNode("//log/time/day").Text & "/"`. — Nathan_Sav, Jan 21 '19 at 08:34
Thank you for the response @Nathan_Sav. I am trying to implement that, but unfortunately I am at the very beginner level and can not get it to work. Would you please modify the code and paste here. I can send you the XML files if you would like to try loading them. Thank you so much. — Emanuel Santos, Jan 21 '19 at 10:19
You might find some helpful hints about parsing and XPath expressions at [...parse MSXML2 DomDocument](https://stackoverflow.com/questions/53558110/xml-parse-vba-excel-function-trip-msxml2-domdocument/53559474#53559474). - Furthermore I encourrage you to use fully qualified range references in order to avoid issues as `Range` only refers to the currently active worksheet. — T.M., Jan 21 '19 at 19:29
I was able to have it working by using "On Error Resume Next" after each "Cells(3+i, *)" statement. — Emanuel Santos, Jan 22 '19 at 00:52

How to skip to the next available XML node in a Do While Loop in VBA if one node is missing the XML source file

0 Answers0