Loop through XML files in a folder extracting specific node-CodePudding

Sub test()
   
    Dim diaFolder As FileDialog
    Dim selected As Boolean
    Dim FolderName As String

    Set diaFolder = Application.FileDialog(msoFileDialogFolderPicker)
    diaFolder.AllowMultiSelect = False
    selected = diaFolder.Show

    If selected Then
    FolderName = diaFolder.SelectedItems(1)
    End If

    Set diaFolder = Nothing

Set oXMLFile = CreateObject("Microsoft.XMLDOM")
xmlFileName = foldername & "/*.xml" ' I'm working on it
oXMLFile.Load xmlFileName
Set NameNode = oXMLFile.SelectNodes("/nfeProc/NFe/infNFe")

Range("A1") = NameNode(0).Attributes.getNamedItem("Id").Text ' (Range A1   1 ???)
End Sub

I changed this code to extract a specific string of a .xml file. It worked, but now I'm trying to change it to loop for all .xml files in a folder and put the results on Range A1,A2,A3...until finish all files in the folder

Someone can help me?

Edit 1:

Sub test ()
    Dim NameNode As Object
    Dim xmlIdx As Integer
    Dim xmlFileName As String
    
    
    Set oXMLFile = CreateObject("Microsoft.XMLDOM")
    
    xmlFileName = Dir("C:\Users\default\Desktop\TEST\*.xml")
    xmlIdx = 1
    
    Do While Len(xmlFileName) > 0
    
        oXMLFile.Load xmlFileName
        Set NameNode = oXMLFile.SelectNodes("/nfeProc/NFe/infNFe")
        If Not NameNode Is Nothing Then
            Range("A" & xmlIdx) = NameNode(0).Attributes.getNamedItem("Id").Text
            xmlFileName = Dir
            xmlIdx = xmlIdx   1
        Else
             Set NameNode = Nothing
             End If
             Loop
    
    End Sub

CodePudding user response：

Use concatenation with a loop index to achieve A1, A2, A3 etc

Sub test()
    Dim diaFolder As FileDialog
    Dim selected As Boolean
    Dim FolderName As String

    Set diaFolder = Application.FileDialog(msoFileDialogFolderPicker)
    diaFolder.AllowMultiSelect = False
    selected = diaFolder.Show
    
    If selected Then
    FolderName = diaFolder.SelectedItems(1)
    End If

    Set diaFolder = Nothing

Dim xmlIdx As Integer
Set oXMLFile = CreateObject("Microsoft.XMLDOM")
Dim xmlFileName As String
xmlFileName = Dir(FolderName & "/*.xml") ' I'm working on it
xmlIdx = 1
Do While len(xmlFileName) > 0
    oXMLFile.Load xmlFileName
    Set NameNode = oXMLFile.SelectNodes("/nfeProc/NFe/infNFe")
    Range("A" & xmlIdx) = NameNode(0).Attributes.getNamedItem("Id").Text ' (Range A1   1 ???)
    xmlFileName = Dir
    xmlIdx = xmlIdx   1
Loop
End Sub

CodePudding user response：

This should work, here I've used id and not Id in the xpath (change if needed)

Sub Test()
    Dim folderPath As String
    
    With Application.FileDialog(msoFileDialogFolderPicker)
        .AllowMultiSelect = False
        If .Show = -1 Then
            folderPath = .SelectedItems(1)
            If Len(folderPath) = 0 Then: Exit Sub
            If Right(folderPath, 1) <> Application.PathSeparator Then: folderPath = folderPath & Application.PathSeparator
        End If
    End With
    
    Dim i As Long
    Dim xmlFilaName As String
    Dim xmlDoc As Object
    Dim xmlAttributeNode As Object
    
    Set xmlDoc = CreateObject("Microsoft.XMLDOM")
        xmlDoc.async = False
        xmlDoc.validateOnParse = False
    
    xmlFilaName = Dir(folderPath & "*.xml")
    Do While Len(xmlFilaName) > 0
        xmlDoc.Load folderPath & xmlFilaName
        Set xmlAttributeNode = xmlDoc.SelectSingleNode("//nfeProc/NFe/infNFe/@id") ' These are not the same: ID, id, Id
        If Not xmlAttributeNode Is Nothing Then
            i = i   1
            Cells(i, 1).Value2 = xmlAttributeNode.Text
        End If
        xmlFilaName = Dir()
    Loop
    
    Set xmlDoc = Nothing
End Sub