VBA loop through incrementing URL to download XML

JEFFtheBeginner

New Member
Joined
Jan 23, 2018
Messages
3
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.
 

Some videos you may like

Excel Facts

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.

VBA Geek

MrExcel MVP
Joined
Dec 16, 2013
Messages
2,857
you can start with something like this:

Code:
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

New Member
Joined
Jan 23, 2018
Messages
3
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,335
Messages
5,595,569
Members
413,996
Latest member
mabelO

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top