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?. 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
– Ken White, Commented Oct 19, 2022 at 23:42

milo5m · Accepted Answer · 2022-10-24 05:38:51Z

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

Whale Shark · Accepted Answer · 2022-10-20 00:16:49Z

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, 2022 at 0:16

Whale Shark

513 bronze badges

6 Comments

Black Mamba Over a year ago

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?

Whale Shark Over a year ago

If it works sometimes then it might be a system/environment error: kb.palisade.com/index.php?pg=kb.page&id=1405

Black Mamba Over a year ago

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?

VBasic2008 Over a year ago

@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?

Black Mamba Over a year ago

@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

|

Collectives™ on Stack Overflow

Loop through XML files in a folder extracting specific node

2 Answers 2

Comments

6 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

6 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related