Web Scraping

[BlueIshDan]

Private Sub Command510_Click()
Dim z As Variant
Dim strURL1 As String
Dim strURL2 As String
Dim strURL As String
Dim MyResult As String
Dim Path As String
Dim ID As String


readHTML = ""
readText = ""


strURL1 = "http://www.giantbomb.com/api/game/"
strURL2 = "3030-4725/?api_key=fd273aa2a677d4931b8efda98573eebad7589ec8"
strURL = (strURL1 + strURL2)
Path = "C:\Program Files (x86)\Internet Explorer\iexplore.exe"
z = Shell(Path + " " + strURL, vbNormal)


Debug.Print readHTML
Debug.Print readText

End Sub

You provided me with this code, this does not look to do anything that you just described.
It opens a web page that displays the contents of an xml file.

 

[rangersedge]

That's why I'm stuck. So far, this is the only code I've managed to piece together (with absolutely no code schooling) and actually get anything from the API. I have a few sites that provide a web API like this one. That's the reason I'm trying to figure out how to use an API like this and import it into the DB. Should I be using a different code to call the API and then import the info or....?

 

[BlueIshDan]

From what I can tell, you're not even using an api. Just viewing a file on their site lol.

 

[rangersedge]

Ok. How do I call the API and get the info into the DB?

 

[BlueIshDan]

couldn't tell you. The page is blocked for me, sorry!

 

[rangersedge]

OK. So I have the code so that it puts the data into a table... now I can't seem to get it to loop through...

Private Sub Command514_Click()


Dim objSvrHTTP As ServerXMLHTTP
Dim objXML As DOMDocument
Dim site1 As String
Dim site2 As String
Dim sitenumber As String
Dim intCounter As Integer

intCounter = 0
Do While intCounter < 10000
KillFile = "C:\Users\bgidcomb\Desktop\Temp\Test.xml"

site1 = "http://www.giantbomb.com/api/games/?api_key=fd273aa2a677d4931b8efda98573eebad7589ec8&offset="
sitenumber = intCounter
site2 = ""
site = site1 & sitenumber

Set objSvrHTTP = New ServerXMLHTTP
objSvrHTTP.Open "GET", site, False, _
CStr(txtUserName), CStr(txtPassword)
objSvrHTTP.setRequestHeader "Accept", "application/xml"
objSvrHTTP.setRequestHeader "Content-Type", "application/xml"
objSvrHTTP.send



'Load Highrise response into an XML document object and
'save it to the harddrive
Set objXML = New DOMDocument
objXML.LoadXML objSvrHTTP.responseText
objXML.Save "C:\Users\bgidcomb\Desktop\Temp\Test.xml"
'If you want better structured XML, use this code
txtXML = objXML.XML
'even though this code is quicker
'txtXML = objSvrHTTP.responseText
Exit_Procedure:
On Error Resume Next
Set objSvrHTTP = Nothing
Set objXML = Nothing
Set objSvrHTTP = Nothing

Application.ImportXML _
DataSource:="C:\Users\bgidcomb\Desktop\Temp\Test.xml", _
ImportOptions:=acAppendData

Exit Sub
intCounter = intCounter + 100
Loop

End Sub

if the sitenumber field would increment by 100 and the loop back through the code until the number reaches 1,000 then I would have all the data. If I manually change this and then run the code each time then it works perfectly. How do I get it to cycle through automatically?