Download multiple xml files from URL to MS Excel

I have developed a VBA code that download all the xml files and fill a worksheet.

Some keys you must understand:

• It's a VBA code (programming language)
• It will download all the files, so it will take a time depending of your internet connection, cpu and ram
• Some of the xml files have a different number of properties, you have to handle it. As I implemented a generic logic, if a xml has fewer properties or they are in a different order the worksheet will be inconsistently.

Steps

• Open a new Excel file
• Right-Click on Tab sheet and click on View Code https://i.stack.imgur.com/mTGW9.png
• Select Sheet1 on the left window (Project) https://i.stack.imgur.com/I6Eby.png
• Paste all the code below
• Go to Tools > References and check in https://i.stack.imgur.com/ELKv8.png
  • "Microsoft XML, v6.0"
  • "Microsoft VBScript Regular Exception 5.5" and click on OK
• Press the play button on the toolbar and wait

Code

Const BaseUrl = "https://files.naskorsports.com/xml/products/"

Sub Main()

    Dim Matches As Object
    Set Matches = GetFileList()
    
    Dim Row As Integer: Row = 2
    
    Dim objXML As MSXML2.DOMDocument60
    Set objXML = New MSXML2.DOMDocument60
    
    For Each Match In Matches
    
        objXML.LoadXML (GetContent(BaseUrl & Match.SubMatches(0)))
    
        'Fill the columns headers
        If (Row = 2) Then
            With objXML.DocumentElement.FirstChild.ChildNodes
                For ColNum = 1 To 16
                    Worksheets(1).Range(Cols(ColNum - 1) & 1).Value = .Item(ColNum - 1).BaseName
                Next
            End With
        End If
        
        'Fill the data
        With objXML.DocumentElement.FirstChild.ChildNodes
            For ColNum = 1 To 14
                Worksheets(1).Range(Cols(ColNum - 1) & Row).Value = .Item(ColNum - 1).Text
            Next
        End With
        
        Row = Row + 1

    Next Match
End Sub

Function GetContent(Url As String) As String
    Debug.Print Url

    With CreateObject("MSXML2.XMLHTTP")
        .Open "GET", Url, False
        .setRequestHeader "User-Agent", "Mozilla/5.0"
        .send
        GetContent = .responseText
    End With
End Function

Function GetFileList() As Object

    Dim Regex As Object
    
    Set Regex = CreateObject("vbscript.regexp")
    
    With Regex
        .Pattern = ">(.*.xml)<"
        .MultiLine = True
        .Global = True
    End With
    
    Set GetFileList = Regex.Execute(GetContent(BaseUrl))
End Function

Function Cols()
    Cols = Array("A", "B", "C", "D", "E", "F", "G", "H", "I", "J", "K", "L", "M", "N", "O", "P", "Q", "R", "S", "T", "U", "V", "W", "X", "Y", "Z")
End Function