VBA loop through incrementing URL to download XML

[JEFFtheBeginner]

Hello,

First, thank you for any help given. Second, I am just starting to get my feet wet with VBA and XML, so please excuse the misused terms.

I would like have a script that loops through to increment a value within a URL and then download that XML to save to specified folder. I have seen bits and pieces of what I believe I need, but do not understand the language enough to fully comprehend what I need to do or change to my purposes.

Example: I want to start with this URL https://www.theWebsite.com=##### where ##### is the number I need to increment the value of the variable piece to get the XML from each. So I need https://www.theWebsite.com=00001 XML downloaded and saved, then https://www.theWebsite.com=00002 XML downloaded and saved, and so on. Then save the XML from those to folder on local drive as separate XMLs. Also, needing to know how to use error handling to move to next URL if a URL is in valid within the incrementing values.

End goal is to parse through the XML to obtain and move data into an Access DB into labeled tables as I need instead of whatever format Access auto formats the XML as, but trying to start with basics to learn along the way. First piece is just to get the several thousand XMLs before moving on.

Again, thank you for any help and info given.

 

[VBA Geek]

you can start with something like this:

Const URLBegin = "https://www.theWebsite.com="
Const folderToSaveIn = "C:\Users\Me\Desktop\"

Dim objXML As Object
Dim objDoc As Object

Sub get_XML_ALL()
Dim i As Long

Set objXML = CreateObject("MSXML2.XMLHTTP")
Set objDoc = CreateObject("msxml2.domdocument")

For i = 1 To 20
    SaveXML URLBegin & Format$(i, "0000"), i
Next i

Set objXML = Nothing
Set objDoc = Nothing
End Sub

Function SaveXML(ByVal sUrl As String, _
                 ByVal i As Long) As Boolean

On Error GoTo err_

objXML.Open "GET", sUrl, False
objXML.send

If objDoc.LoadXML(objXML.responseText) Then
    objDoc.Save folderToSaveIn & Format(i, "0000") & ".xml"
    SaveXML = True
Else
    SaveXML = False
End If
Exit Function

err_:
    SaveXML = False
    Err.Clear
End Function

 

[JEFFtheBeginner]

VBA Geek, thank you for the help. I will give it a shot within the next couple days and edit to specific needs. I know what I want to do is possible, but want to try and learn at least how to read and speak a little of the language without getting my whole solution given to me. I appreciate it.