Updating an .xml document according to a .csv file

I'm quite of a newbie in VBA, and I'm struggling to find a solution to my problem. Basically, what I need to do is editing some nodes in an .xml file according to the content of a .csv document. In particular, whenever I loop through the .xml document (let call it "C:\Users\xxx\Desktop\ppp.xml") and I stumble upon a particular node (let it be "thing"), I need to read the text of that node and look for it in the .csv file ("let it be C:\Users\xxx\Desktop\mycopy.csv"), and then edit the text of a different node (let it be "qt") in the selfsame .xml file. I was thinking about the following rationale:

  1. Since the .xml file editing (below I used Microsoft XML, v3.0) needs to be done according to the .csv content,
  2. I firstly converted the .csv into a .xlsx file (I don't know much of managing .csv files, so that way was more manageable for me)
  3. and then performed a sort of Vlookup version in VBA.

That works fine, if I run separately this part of the code shown below. Since I know some xml in vba, I have a basic knowledge of how to edit nodes and attributes. However, I struggle to link the .xml file to the .xlsx file. I've taken a look to a lot of .xml editing examples in vba, but the editing is performed according to the selfsame .xml, without looking for a value in a different file. I'll post a sample of my code, which obviously doesn't work, hoping it's clear enough. Thanks.

Option Explicit

Sub editxml()

Dim Obj As DOMDocument  
Dim xmlpath As String
Dim loadcheck As Boolean
Dim Node As IXMLDOMNodeList  
Dim Nm As IXMLDOMNode 
Dim thing As Object, q As Object

Dim wb As Workbook         
Dim ws As Worksheet
Dim mycsvfile As String 
Dim i As Integer, numcol As Integer
Dim line As String
Dim row As Integer 
Dim matrix As Variant  

Dim rngSearch As Range, rngLast As Range, rngFound As Range
Dim strFirstAddress As String

Set Obj = New DOMDocument
Obj.async = False: Obj.validateOnParse = False

xmlpath = "C:\Users\xxx\Desktop\ppp.xml"
Obj.SetProperty "SelectionNamespaces", "xmlns:ns0='http://update.DocumentTypes.Schema.ppp.Xml'"

loadcheck = Obj.Load(xmlpath)
If loadcheck = True Then
    MsgBox "File XML uploaded"
    MsgBox "File XML not uploaded"
End If

Set Node = Obj.DocumentElement.SelectNodes("AA/BB/CC/DD")

For Each Nm In Node
    Set thing = Nm.SelectSingleNode("thing")
    Set q = Nm.SelectSingleNode("qt")
    If thing.Text = rngFound Then
    q.Text = "do somewhat else"
    End If
Obj.Save (xmlpath)

Set wb = Workbooks.Add
wb.SaveAs Filename:="csvtoxlsxfind" & ".xlsx"  
Set ws = wb.Sheets(1)

With ws
    row = 1

    mycsvfile = "C:\Users\xxx\Desktop\mycopy.csv"  

    Open mycsvfile For Input As #1
    Do Until EOF(1)
        Line Input #1, line    
        matrix = Split(line, ",") 
        numcol = UBound(matrix) - LBound(matrix) + 1    
        For i = 1 To numcol     
            Cells(row, i) = matrix(i - 1)      
        Next i
        row = row + 1
    Close #1
    'set the search range, i.e where I have to find the value:
    Set rngSearch = .Range("AR:AR")

    'specify last cell in range:
    Set rngLast = rngSearch.Cells(rngSearch.Cells.Count)

    'Find the "thing" in search range, when it first occurrs (rngFound=1st occurrence).
    Set rngFound = rngSearch.find(What:=thing.Text, After:=rngLast, LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False)

    'if the "thing" is found in search range:
    If Not rngFound Is Nothing Then
        'saves the address of the first occurrence of the "thing" in the strFirstAddress variable:
        strFirstAddress = rngFound.Address
        'Find next occurrence of the "thing". 
        MsgBox rngFound.Address & " " & rngFound.Offset(0, -29).Value * rngFound.Offset(0, -6)
        Set rngFound = rngSearch.FindNext(rngFound)
        rngFound.Font.Color = vbRed
        rngFound.Offset(0, -40).Font.Color = vbRed
        Loop Until rngFound.Address = strFirstAddress
        MsgBox "thing not found"
    End If
End With

End Sub 

I'm well aware that the part of the code that doesn't make sense is the following:

For Each Nm In Node
    Set thing = Nm.SelectSingleNode("thing")
    Set q = Nm.SelectSingleNode("qt")
    If thing.Text = rngFound Then
    q.Text = "do somewhat else"
    End If

since I haven't defined rngFound yet (this would be the result of my Vlookup search). Does the logic I followed make some sense, or the code needs to be rewritten from scratch? Is is possible to avoid the .xlsx conversion of the .csv file, and so doing the search directly in the .csv?

Read more here: https://stackoverflow.com/questions/65703113/updating-an-xml-document-according-to-a-csv-file

Content Attribution

This content was originally published by Nenne at Recent Questions - Stack Overflow, and is syndicated here via their RSS feed. You can read the original post over there.

%d bloggers like this: