Download multiple xml files from URL to MS Excel-CodePudding

does someone know how to get multiple xml data to MS Excel? I'm trying to download the xml files from URL to excel, but unfortunately there are multiple files and each file has it's own URL.

https://files.naskorsports.com/xml/products/

Do I have to manually type in each and every URL and "paste" it to Excel or is there a automated method?

Thanks in advance

CodePudding user response：

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