SaveSquirrels
New Member
- Joined
- Jul 26, 2012
- Messages
- 29
Hi,
I've been programming my VBA script to import data from an XML file which looks like this:
So far everything works great and it imports everything correctly to Excel 2010. This is the code I'm using:
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
I've been programming my VBA script to import data from an XML file which looks like this:
HTML:
<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:
Code:
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