Web scrape macro/ VBA


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?

Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Gustav BrockCIOCommented:
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.
xeniumAuthor 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.
xeniumAuthor 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.

C++ 11 Fundamentals

This course will introduce you to C++ 11 and teach you about syntax fundamentals.

Gustav BrockCIOCommented:
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.
xeniumAuthor 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

Debug.Print appIE.Document.body.textContent

Open in new window

Gustav BrockCIOCommented:
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.
xeniumAuthor 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?

Scott Fell, EE MVEDeveloper & EE ModeratorCommented:
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.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
xeniumAuthor 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.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today

From novice to tech pro — start learning today.