0

I wanted to erase the nodes from a XML file and rewrite them as an exercize to practice VBA, but for some reason, the appendCHild command is returning an error 438. As I'm not experienced in programming, I'm having a hard time finding why, have tryed to change the type of variables from element, node, nodelist already

Dim vFolder As String, vFile As String, vFileName As String
Dim vLineFile As Integer, vLineTAG As Integer, vLineResource As Integer
Dim vRange As String, vNode As String, vRange2 As String
Dim vWindowMain As String, vWindowFile As String, vWindowXML As String, vXMLSheetName As String
Dim XDoc As Object
Dim myNodes As IXMLDOMNodeList, myChildNodes As IXMLDOMNodeList
Dim myElement As IXMLDOMElement
Dim myNode As IXMLDOMNode, myChildNode As IXMLDOMNode
Dim NameItem As IXMLDOMAttribute
Dim nNode As Integer
Dim nChildNode As Integer
Dim wb As Workbook
Dim ws As Worksheet

'Remember to delete variables not used after

vWindowMain = ActiveWorkbook.Name

'Sheet that has received the nodes
Sheets("Plan1").Select
vRange = "A1"
vXMLSheetName = Range(vRange).Value + "Orionv5.xlsx"
Workbooks.Open vXMLSheetName, UpdateLinks:=False
vWindowXML = ActiveWorkbook.Name
vLineFile = 2

'main workbook
Windows(vWindowMain).Activate
Sheets("Plan1").Select
vRange = "A1"
vFolder = Range(vRange).Value
vLineTAG = 2

Do While True
    Application.ScreenUpdating = False
    
    vLineFile = vLineFile + 1
    Windows(vWindowMain).Activate
    Sheets("Plan1").Select
    vRange = "A" + Trim(Str(vLineFile))
    vFile = Range(vRange).Value
    vFileName = vFolder + "\" + vFile 'xml arquives are listed on the main workbook
    
    If vFile = "" Then
        Exit Do
    End If
    
    Set XDoc = CreateObject("MSXML2.DOMDocument")
    Set wb = Workbooks(vWindowXML)
    Set ws = wb.Worksheets(1)
    XDoc.async = False: XDoc.validateOnParse = False
    XDoc.load (vFileName)
    Set myNodes = XDoc.SelectNodes("//data")
    For nNode = 0 To myNodes.Length - 1    'erasing all the "data" nodes
        Set myNode = myNodes(nNode)
        myNode.ParentNode.RemoveChild myNode
        XDoc.Save vFileName
    Next nNode
    
    Do While True
        With ws
            vRange2 = "B" + Trim(Str(vLineTAG))
            Set myNode = XDoc.SelectSingleNode("//root")
            Set myElement = XDoc.createElement("data")
            myNode.appendChild (myElement) 'not working

            Set myNode = XDoc.SelectSingleNode("//root/data[vLineTAG]")
            Set myChildNode = XDoc.createAttribute("name")
            myChildNode.Text = .Range(vRange2).Value
            myNode.Attributes.setNamedItem (NameItem) 'this will put the attribute name =""

            vRange2 = "C" + Trim(Str(vLineTAG))
            Set myNode = XDoc.SelectSingleNode("//data[vLineTAG]")
            Set myChildNode = XDoc.createElement("value") 'putting a new node under the data node named value
            myChildNode.Text = .Range(vRange2).Value 
            myNode.appendChild (myChildNode)

            vRange2 = "A" + Trim(Str(vLineTAG))
            XDoc.Save vFileName
            vLineTAG = vLineTAG + 1

            If .Range(vRange2).Value <> vFile Then
                Exit Do
            End If
        End With
        
    Loop
Loop

End Sub

In the first appendChild calling it already stops running, and I don't know if there are more errors nor if this is the best approach to edit a XML file, but it makes sense to me right now. Can someone tell me what is the bug in this error?

Arthur Paiva
  • 15
  • 2
  • `myNode.appendChild myElement` without the parentheses. See: https://stackoverflow.com/questions/10262186/vb6-pass-by-value-and-pass-by-reference/10262247#10262247 – Tim Williams Jul 29 '20 at 16:15
  • Now the line myNode.Attributes.setNamedItem (NameItem) is bugging, so I edited it to myNode.Attributes.setNamedItem myChildNodes, and now it is returning the same error 91 variable from block with not declared... I tried using a IXMLDOMAttribute instead of a IXMLDOMNode, then a IXMLDOMElement, etc... – Arthur Paiva Jul 29 '20 at 17:56
  • Lol nevermind, it was in the selectsinglenode the problem... Iwas referencing the index as if I was using selectnodes – Arthur Paiva Jul 29 '20 at 17:59

0 Answers0