Web scrape macro/ VBA

xenium
xenium used Ask the Experts™
on
hi,

I'm looking to setup a simple webscrape from my PC, i want to batch input specific URLs and output what I get if I were to select all and copy-paste to notepad, ie just the text from the website.

Is there a way to do this with VBA or similar?

Thanks
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Most Valuable Expert 2015
Distinguished Expert 2018

Commented:
You can use the function DownloadFile  found here:

Show pictures directly from URLs in Access forms and reports

to download the page to a file. See the in-line comments for an example.
Then you can parse the file for its data.

However, this will only work for traditional HTML. For dynamic pages or sites with login, you'll need more advanced methods.

Author

Commented:
Thanks this is a great tool, however it does not work for what I need. It seems to download the source code, but unfortunately for the page I need, the displayed data that i need is not shown in the source code, specifically pricing info. I can copy paste the visible page output to get this, but i want to automate this.

Author

Commented:
If there is an alternative way to get the pricing info, eg if it's in a cache somewhere, that might be an option if simple, but i'd like to explore the simple copy-paste scenario first.

thanks
Announcing the Winners!

The results are in for the 15th Annual Expert Awards! Congratulations to the winners, and thank you to everyone who participated in the nominations. We are so grateful for the valuable contributions experts make on a daily basis. Click to read more about this year’s recipients!

Most Valuable Expert 2015
Distinguished Expert 2018

Commented:
You may have to use the Web Browser control (effectively calling Internet Explorer) and/or "Microsoft Internet Controls".

However, I don't use these, but plenty of examples out there if you browse for it.

Author

Commented:
I've done quite a bit of googling and ended up posting here.

The website in question i imagine has measures in place to avoid being scraped from the server. Since i only need a low volume of data daily (i'm just checking the presentation of my own data) a VBA script to copy-paste would do, i tried the following, but the pricing info is being loaded AFTER the doevents have finished (3 little dots still blinking then the price shows a bit later) Also the script below does not copy paste

Dim appIE As Object
Set appIE = CreateObject("internetexplorer.application")


With appIE
    .Navigate "https://www.airbnb.co.uk/rooms/.. any room id... ?checkin=20-05-2018&checkout=22-05-2018&guests=2"
    .Visible = True
End With

Do While appIE.Busy
    DoEvents
Loop

Debug.Print appIE.Document.body.textContent

Open in new window

Most Valuable Expert 2015
Distinguished Expert 2018

Commented:
It seems like they have taken steps to prevent what you are attempting to do.

I haven't worked with such pages, so I can't tell how to work around that.

Author

Commented:
I think they've taken sufficient steps to prevent mass scraping, but i'm looking at very low volumes. it should be possible to automate a simple copy paste operation. It maybe i need to put in a wait of say 5 seconds to allow the page to load fully.

The step i'm missing is how to grab the displayed text. I can do this manually with select all, copy.

How can i do this with vba?

Thanks
Developer & EE Moderator
Fellow 2018
Most Valuable Expert 2013
Commented:
Take a look at their terms https://www.airbnb.co.uk/terms use any robots, spider, crawler, scraper or other automated means or processes to access, collect data or other content from or otherwise interact with the Airbnb Platform for any purpose;

Depending on what you are trying to do there may be a better option as you are not allowed to scrape data that way.  I would say review the api and see if that applies to you https://www.airbnb.com/partner and if it does, it will be a lot easier to get what you need.

Author

Commented:
Thanks Scott, the API sounds like the way to go, I wasn't even aware of it, though I see it's quite new.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial