Loop through XML files in a folder extracting specific node

Question

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

Does this answer your question? [Loop through files in a folder using VBA?](https://stackoverflow.com/questions/10380312/loop-through-files-in-a-folder-using-vba). I didn't even have to search for it - it was the first suggested possible duplicate you were shown when you wrote your question. It's the top listing in the **Related** list, just to the right of your post. Please don't ignore suggested duplicates, and make a serious effort to search the site before posting a new question. — Ken White, Oct 19 '22 at 23:42

milo5m · Accepted Answer · 2022-10-24T05:38:51.687

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 xmlFileName As String
    Dim xmlDoc As Object
    Dim xmlAttributeNode As Object
    
    Set xmlDoc = CreateObject("Microsoft.XMLDOM")
        xmlDoc.async = False
        xmlDoc.validateOnParse = False
    
    xmlFileName = Dir(folderPath & "*.xml")
    Do While Len(xmlFileName) > 0
        xmlDoc.Load folderPath & xmlFileName
        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
        xmlFileName = Dir()
    Loop
    
    Set xmlAttributeNode = Nothing
    Set xmlDoc = Nothing
End Sub

score 1 · Answer 2 · answered Oct 20 '22 at 00:16

1

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

answered Oct 20 '22 at 00:16

Whale Shark

51
3

Sometime works, sometimes doesn't works...i don't know why. Maybe is because Excel prompt a window saying the that the variable `With` was not set? – Black Mamba Oct 20 '22 at 01:57
If it works sometimes then it might be a system/environment error: https://kb.palisade.com/index.php?pg=kb.page&id=1405 – Whale Shark Oct 20 '22 at 06:21
It prompts `error run time error 91 object variable or with block variable not set` refering to this line of the code `Range("A" & xmlIdx) = NameNode(0).Attributes.getNamedItem("Id").Text`. I tried on another machine and the same happened. Can you check it please? – Black Mamba Oct 20 '22 at 10:32
@Black Mamba: The error is most probably because `NameNode Is Nothing`. So you need to account for that with e.g. `If Not NameNode Is Nothing Then: Range...: Set NameNode = Nothing: End If`. Also, you forgot to declare it with `Dim NameNode As Object`. Aren't you using `Option Explicit`? – VBasic2008 Oct 20 '22 at 12:02
@VBasic2008 I tried declaring `Dim NameNode As Object` but not changed. I'm not using `Option Explicit` too. Can you show me what you mean with `if not NameNode...etc` ? But every file must to have the `Id` field so thinking by this way will always have `NameNode` – Black Mamba Oct 20 '22 at 12:21
@VBasic2008 i did what you suggest and the `run time error 91` is still ocurring, also no values from the `.xml` files are beeing extracted to the sheet. Can you check my Edit 1 please? – Black Mamba Oct 21 '22 at 12:09

Loop through XML files in a folder extracting specific node

2 Answers2