Web Scraping

[rangersedge]

I'm trying to learn how to automate my Access database to get information from a web site. Any suggestions on how to learn this or where to learn this?

 

[spikepl]

Depends. It is easiest in Excel. For scraping play with the code in post #11 here http://www.access-programmers.co.uk/forums/showthread.php?t=226807

 

[rangersedge]

Excel is much easier. But the task is to get an Access DB to do it.

 

[rangersedge]

OK. I found the website has an API that returns XML. I know how to use xml once it is on my computer. I need help on how to use VBA to call the website's API, search for the title, then import the xml file that is returned. Any suggestions on where to find this? Google isn't much help on this issue.

 

[jdraw]

Please post the URL for the site with the API so readers can take a look.

This may be of some help regarding the vba.

 

[BlueIshDan]

This is funny, I was just starting work on an access database that sucks the html out of web pages and stores it all.

 

[rangersedge]

I'm trying to learn on a game DB. The site I'm using is...

 

[jdraw]

Dan,

Anything you have will be helpful. There are not many samples using Access and vba. Seems to be some in Excel, but those too are few and far between.

Please post anything you develop/discover.

 

[rangersedge]

http://www.giantbomb.com/forums/api-developers-3017/quick-start-guide-to-using-the-api-1427959/#6

 

[BlueIshDan]

Honestly I have only put about 30 minutes into this database, but I can supply you with what I have thus far.

 

[rangersedge]

ANY help is more than welcome

 

[BlueIshDan]

View attachment web.accdb
It's not much, but it is a start for you/us!

 

[BlueIshDan]

one form is scraping the outer scope of each form's html into a text box (can be recursive in later version)

the other is gathering the options of combo boxes in the web page and storing them into your own.

It is very object orient, but you'll have to get used to looking through the properties of each object in a watch during runtime and also keep in an web based mind set.

 

[BlueIshDan]

Do you have much experience in web design?

 

[rangersedge]

That is way to advanced for me. I started with something like this code below. It brings up the explorer with the information on it. If you could help me figure out how to get it to save the xml, or maybe even import the data without having to save the file...

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

 

[BlueIshDan]

you want to save a file from a website into a predetermined located with/without a prompt?

Does this sound about right?

 

[BlueIshDan]

z = Shell(Path + " " + strURL, vbNormal)

Should be:

Shell Path + " " + strURL, vbNormal

I see people doing this all the time. If you're calling a function without returning a value to anything or using its value in a condition statement, remove its brackets.

Also the & concatenates strings.

Shell Path & " " & strURL, vbNormal

 

[rangersedge]

Baisicly I just want the code to pull the information from the site and import the information into the correct field in Access. I've figured out how to get to the site holding the information. Now I need to get the information from the site and into the DB.

 

[BlueIshDan]

Might want to learn the "complicated" code good sir.

It is also is easier if you use Google Chrome's Inspect Element feature.

 

[rangersedge]

The complicated code? What would that be? I thought it would be easier to just download the already available XML file and import that. I already have a way to import XML and even a kill code to get rid of the file once imported. All I really need now is a way to save the XML once it's called with the API.