Switching page numbers during XML import using VBA

[SaveSquirrels]

Hi,

I've been programming my VBA script to import data from an XML file which looks like this:

<response responseStatus="success"> <result xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"xsi:type="apiPagination"> <currentPage>1</currentPage> <list xsi:type="apiReport"> <Variable1>Name1</Variable1> <Variable2>41</Variable2> </list> <list xsi:type="apiReport"> <Variable1>Name2</Variable1> <Variable2>20</Variable2> </list>

So far everything works great and it imports everything correctly to Excel 2010. This is the code I'm using:

Sub test()
    
    Dim WS As Worksheet: Set WS = ActiveSheet
    Dim req As New XMLHTTP
    Dim Link As String
    Dim PageNumber As Integer
    Dim Url As String
         
    PageNumber = 1
    Link = "https://myserver.com/services/rest/g...rts/mypassword/"
    Url = Link & PageNumber
    
    req.Open "GET", Url, False
    req.Send
    
    
    Dim Resp As New DOMDocument
    Resp.LoadXML req.responseText
    
    Dim i As Integer
    Dim list As IXMLDOMNode
    
    For Each list In Resp.getElementsByTagName("list")
    i = i + 1
    WS.Range("A1:A100").Cells(i, 1).Value = list.SelectNodes("Variable1")(0).Text
    Next list
     
 End Sub

The problem is that I only import page1 (only link https://myserver.com/services/rest/g...rts/mypassword/1)

As you can see I've started to define the url so that other page numbers are possible. How do I get all URLs so that it also imports the data located on other pagenumbers? For example https://myserver.com/services/rest/g...rts/mypassword/2

 

[SaveSquirrels]

Never thought that I would solve it but i did (in only two hours)

        Dim AmountofPages As String    
    
        For Each result In Resp.getElementsByTagName("result")
        AmountofPages = result.SelectNodes("nbPages")(0).Text
        Next result
   
          
        For i2 = 1 To AmountofPages
        PageNumber = PageNumber + 1
        Url = Link & PageNumber
        req.Open "GET", Url, False
        req.Send
        Resp.LoadXML req.responseText
        For Each list In Resp.getElementsByTagName("list")
        i = i + 1
        WS.Range("C2:C500").Cells(i, 1).Value = list.SelectNodes("variable1")(0).Text
        Next list
             
       Next i2