Excel Macro for Website Scraping – Excel Macro to Execute a Web Query

Lots of our readers were writing to us to provide a sample macro that explains how to scrape website using VBA. Well, we asked them why don’t you use web query browser in excel to import web data and we understood that in most of the cases the requirement was either to execute the web query for multiple URL’s or to execute the web query in a loop. This made a lot of sense to come up with a sample excel macro that demonstrates how to fetch website data and copy in excel worksheet.

Note: To find out the detailed steps on how to create, run or edit a macro, you may refer here for excel 2003 or here for excel 2007.

Here is the VB Code to scrape website data.

Let us explain this macro now. Here variable mURL holds website url from which data is to be fetched.

The code fragment below connects to the website mURL:

Rest of the code fragment is to set various properties on QueryTable object. Each of this property is explained here.

Now, let’s focus on an important property in above code: WebSelectionType. Valid values for WebSelectionType are xlAllTables, xlEntirePage, xlSpecifiedTables. By setting WebSelectionType, we set a value that determines whether an entire Web page, all tables on the Web page, or only specific tables on the web page are imported into a query table. In above code, we have set WebSelectionType as xlSpecifiedTables i.e data should be imported from a specific table. Now this table index is specified using another property: WebTables.

So, using above macro we import data from table with index 15 on mentioned website.

We hope that you like this information and find it useful. Feel free to leave a comment below for any queries or suggestions.

  1. Thanks a lot for this. It’s exactly what I was looking for. I have amended some code to pull data from the bbc sport website. I’m learning VBA for my work. This will be really useful.

    Thanks again :)

  2. Hi! Could you help with a little add-on?
    How can I add a variable to my macro to open the webpage
    based upon that. e.g. fetch the GOOG part from the A1 cell in “http://finance.yahoo.com/q/hp?s=GOOG”

    Thank You very much!

  3. When getting rid of drain clogs, avoid using chemical cleaners.
    There is considerable expense involved with installing a septic tank.

    In fact, there would be plenty of surplus water to irrigate the garden or sell
    to other consumers.

  4. Reed bed technology has a low price of entry and minimal daily functional and upkeep costs.
    There is considerable expense involved with installing a
    septic tank. Tiny video cameras are sometimes used to probe intricate pipes to pinpoint hidden leaks and problems.

  5. We had better use Msconfig or ask help from Windows optimization. Pipes and fittings are often made of the same
    base material such as copper, steel, polyvinyl
    chloride (PVC), and chlorinated polyvinyl chloride (CPVC).
    It is not known what prompted officials to drain that specific septic tank looking
    for Noah’s body.

  6. The entire effect of these rugs to a room should be considered when purchasing one.

    Reed beds present sludge dewatering by plant consumption, evapotranspiration, and drainage.
    While both parents were charged, one of the charges that the boy’s mother is facing is more serious than what the boy’s father is
    facing, according to WUSA-9 on April 3.

  7. Most people would like natural instead of synthetic vitamins.
    Excess consumption of zinc may cause bleeding stomach and severe abdominal pain. There are
    only 2 St Johns wort products that I know of, that have had been properly researched and the Flordis Remotiv is one
    of those.

  8. Here at Createch we have made a variety of supplements, several of which are intended to
    do an exact job with your body. Not only does it help to increase size and strength, it also contributes tremendously to increasing lean muscle mass and gain. Another important fact why this disease is treated
    differently from conventional illnesses and why adrenal fatigue supplements are different from conventional medicine lays
    in the spectrum which it deals with.

  9. There are far too many out there to just search through each
    day. Not only does it help to increase size and strength, it also contributes
    tremendously to increasing lean muscle mass and gain.
    ‘Were the supplements tested independently for purity and strength.

  10. This is especially useful if the person you’re inquiring about has a less than common name.
    No software to buy, no code to learn, all you need is to be a little Internet savvy and know how to click
    your mouse. You can manipulate movie clips to animate
    your buttons to make them perform different functions.

  11. The website will also serve as a portal for all things convention related, from a countdown clock to information on logistics, credentials, media
    walkthroughs and housing. This shows you how user friendly these templates are.
    Well, you may have to consider a lot of things before you even plan to launch your website.

  12. Most of the card dealers sell their cards online through
    websites that specialize in international calling cards.
    The website navigation should cater to both the users and the search
    engine bots. I discovered the way to make money on the internet.

  13. Another 10% do it kind of well although not at a professional stage.
    However, because they are free, users cannot have a custom domain name, rather the URL will be [insertname].
    You will also find 30 free tools, and software programs to help you build a successfull Internet Business.

  14. If you search online, used Bicycles For Sale offers
    significant savings. Best way for you to find out if I
    lie is to try it for yourself. Going against this law
    in most of the European countries will have a fine; same is case with United States of America.

  15. While the public hours are from Noon to 4, new this year,
    CIBA members will be allowed begin shopping a half hour
    early at 11:30 AM. So impressed was the magistrate that
    he decided to give him the five shillings required to pay the fine.
    Going against this law in most of the European countries will
    have a fine; same is case with United States of America.

  16. The tape or string should run parallel across the head, just above
    the ears. ‘ A vehicle overtakes a bicycle and does not
    allow the bicycle enough space to safely pass and caused the cyclist to fall
    off the bike. This mount can be installed either
    vertically or horizontally, depending on the preference of the cyclist.

  17. Once you have bought it, make it a habit to use it every
    time you leave your bike unattended, no matter
    even if you leave it for a short time. As your child
    migrates to cycling on the road, they will be at increased risk and a few new guidelines may
    help:It is important to note that you are not nearly as visible
    as cars and not all cars behave perfectly. With a lower more forward hand position you will also shift more weight to
    your pedals.

  18. This means that the middle gear is set up properly with the middle chain ring on triple.
    Many of the gloves available have cushioning on the palms.
    The first time you need to use it, you’ll be happy you bought it.

Leave a Reply to love chain white gold imitation Cancel reply

Your email address will not be published. Required fields are marked *

*

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code class="" title="" data-url=""> <del datetime=""> <em> <i> <q cite=""> <strike> <strong> <pre class="" title="" data-url=""> <span class="" title="" data-url="">