Best way to web scrape using excel as database

[DianaBanana]

Does anyone have a recommended way to scrape data off the web and get it to populate into an excel document? For example, I am going through a hotel site and I'd like to scrape all the different hotel room rates for that particular hotel location and store it in an excel document. I'd like to be able to re-run this (macro or code) weekly and see the different rates? I'm manually doing it and putting it into excel but there must be a better way.

 

[DianaBanana]

Follow these instructions from my earlier post to set a reference to each of the libraries listed at the top of the code:

Ok, I'm sorry I missed that, I enabled that. Now, I'm getting an error message on the next line - Dim lnk As HTMLLinkElement - same error message as before

 

[AngelJ]

Ok, I'm sorry I missed that, I enabled that. Now, I'm getting an error message on the next line - Dim lnk As HTMLLinkElement - same error message as before

lol, yup, you have to do the same thing for the other two libraries: Microsoft HTML Object Library, and Microsoft VBScript Regular Expressions #. Just choose the regular expressions one with the highest number. Mine said 5.5.

 

[DianaBanana]

OK I did that now. It started running, but for some reason between each hotel it sent me a form with an "OK" button message - like La QUinta Inn Hotel in AZ with 2 floors Con't?
Is there a way for it to run w/out these forms? And I could not see the output because I cancelled..is there a way to see output after say 5 hotels (just to see what it's doing)?

 

[AngelJ]

That's correct. There is no output besides those message boxes. The purpose of that sample was to show you how to navigate through the pages and retrieve information from them. The rest is up to you or anyone nice enough to take on the entire project for you.

 

[DianaBanana]

That's correct. There is no output besides those message boxes. The purpose of that sample was to show you how to navigate through the pages and retrieve information from them. The rest is up to you or anyone nice enough to take on the entire project for you.

Oh man - so that's not you? Ok, I am going to try to work on this but can you help me with some questions, which of these words indicate where exactly it's looking? Is it possible for me to "record" a macro from excel into html for one hotel and then just repeat it 800+ times? - Wend
regex.Pattern = "(Floors|Rooms): \d+"
For Each mch In regex.Execute(ie2.Document.DocumentElement.innerHTML)
If MsgBox(ie2.Document.Title & vbCr & mch & vbCr & vbCr & "Continue?", vbYesNo) <> vbYes Then
GoTo Quit

 

[AngelJ]

Oh man - so that's not you?

Nope, sorry. I actually thought the code might scare you into doing this manually until you learned VBA, which wouldn't be such a bad idea as this is not a great project to start VBA on. But, I'll never discourage someone who wants to learn. In fact, I think your desire to get this project done will only drive your learning.

I've tried teaching people VBA before and one common problem was that they didn't have a project like this to challenge them.

...which of these words indicate where exactly it's looking?

This pattern tells it what to look for:

regex.Pattern = "(Floors|Rooms): \d+"

Look here for a visual demonstration of how that works: Regex Tester (Permalink)
And research Regular Expressions for more info. I used regular expressions here because the elements that contained the info didn't have id's. When they do have id's you can use this method: getElementById method (Internet Explorer)

Is it possible for me to "record" a macro from excel into html for one hotel and then just repeat it 800+ times?

No, not that easy, unfortunately.

 

[DianaBanana]

Nope, sorry. I actually thought the code might scare you into doing this manually until you learned VBA, which wouldn't be such a bad idea as this is not a great project to start VBA on. But, I'll never discourage someone who wants to learn. In fact, I think your desire to get this project done will only drive your learning.

I've tried teaching people VBA before and one common problem was that they didn't have a project like this to challenge them.


This pattern tells it what to look for:

regex.Pattern = "(Floors|Rooms): \d+"

Look here for a visual demonstration of how that works: Regex Tester (Permalink)
And research Regular Expressions for more info. I used regular expressions here because the elements that contained the info didn't have id's. When they do have id's you can use this method: getElementById method (Internet Explorer)


No, not that easy, unfortunately.

Angel,
1) How does it know to go from the hotel listings to click on each link? Is it the "navigate lnk"?
2) I am simplifying the process. I am just going to pull up the best available rate that is on the first page along with the room #. Where it says on the page "enjoy rates from". I cannot seem to replicate the regex test.
3) Once it can identify those two items (number of rooms and lowest rate), what tells it to bring it back into excel?

 

[DianaBanana]

Well If you know HTML and VBA, one way to do it is with the Microsoft company On-line collection. I'm sure you will discover some examples by searching on the 'screen' or something like that.

I just started learning vba so when I google for codes not following it that well...

 

[AngelJ]

Angel,
1) How does it know to go from the hotel listings to click on each link? Is it the "navigate lnk"?

Yes, the Navigate method follows the links. This code uses a For Each loop to iterate through the links. Look into VBA loops for a better understanding of that.

2) I am simplifying the process. I am just going to pull up the best available rate that is on the first page along with the room #. Where it says on the page "enjoy rates from". I cannot seem to replicate the regex test.

The pattern for this gets much more complex. This one should work: (remember you're testing against the actual source HTML, not what you see in your web browser)

Enjoy Rates From:(?:.|\n)+\$((?:\d+,+)*\d+\.\d+)

You can use the SubMatches property to extract just the dollar amount like so:

            regex.Pattern = "Enjoy Rates From:(?:.|\n)+\$((?:\d+,+)*\d+\.\d+)"
            For Each mch In regex.Execute(ie2.Document.DocumentElement.innerHTML)
                If MsgBox(ie2.Document.Title & vbCr & mch.SubMatches(0) & vbCr & vbCr & "Continue?", vbYesNo) <> vbYes Then

3) Once it can identify those two items (number of rooms and lowest rate), what tells it to bring it back into excel?

Currently nothing does. Getting the data onto a spreadsheet should be one of the easier parts of this project though. Look at some samples and give it a shot. Then, report back here and we'll help you out.

I just started learning vba so when I google for codes not following it that well...

That post, which looked like a mangled version of my first post was made by a spam bot. Forget about it, lol.

 

[DianaBanana]

Yes, the Navigate method follows the links. This code uses a For Each loop to iterate through the links. Look into VBA loops for a better understanding of that.


The pattern for this gets much more complex. This one should work: (remember you're testing against the actual source HTML, not what you see in your web browser)

Enjoy Rates From:(?:.|\n)+\$((?:\d+,+)*\d+\.\d+)

You can use the SubMatches property to extract just the dollar amount like so:

            regex.Pattern = "Enjoy Rates From:(?:.|\n)+\$((?:\d+,+)*\d+\.\d+)"
            For Each mch In regex.Execute(ie2.Document.DocumentElement.innerHTML)
                If MsgBox(ie2.Document.Title & vbCr & mch.SubMatches(0) & vbCr & vbCr & "Continue?", vbYesNo) <> vbYes Then

Currently nothing does. Getting the data onto a spreadsheet should be one of the easier parts of this project though. Look at some samples and give it a shot. Then, report back here and we'll help you out.


That post, which looked like a mangled version of my first post was made by a spam bot. Forget about it, lol.

im trying to run the macro and it says the macros in this workbook are disabled?