Beyond Excel: VBA and Database Manipulation

Nov 10 2015   11:38AM GMT

Web Scraper

Craig Hatmaker Craig Hatmaker Profile: Craig Hatmaker

Tags:

Make your own web scraper apps!

A web scraper copies data displayed by web pages into, in this case, Excel. WWWHelper.xlsm is an XL workbook that creates other web scraper apps without coding. WWWHelper.xlsm can be used readily by normal Excel users but it is intended to teach VBA Developers about how to integrate the web with Excel.

Intro

Many people don’t realize getting web tables into XL has been simple enough with XL’s Get External Data option and now with PowerQuery. As long as the web data is in an HTML table accessible from a static URL, and does not need to merge with other data, XL’s native tools work really well. When web data does not meet these criteria, this tool works better and easier.

WWWHelper.xlsm Demo

My customer requests are increasing for web data in XL. The data often resides in multiple web pages and may require populating and submitting web forms to get the needed data. I use this tool to automate web scraping and overcome XL’s native limitations. Example uses include:

  • Stock values
  • Exchange Rates
  • CPU Benchmarks
  • Applicant data from career sites
  • Government carrier safety statistics
  • Phone number telecoms for texting from Outlook
  • CRM data from social media sites
  • Fantasy football player statistics
  • And more…

To meet customer demand I created a tool to simplify scraping web elements into XL’s tables, or ranges. Simplifying this task is critical because there are no guarantees that methods used to pull data from a particular website today will work tomorrow. We are completely at the website designers’ whim. The importance of being able to maintain web scraping apps quickly and easily cannot be overstated.

While this tool uses VBA, we don’t need to know VBA to use it. This tool uses normal XL tables to hold paths that map where our web data is and how to get it into XL. Thus, the only skill we need is the ability to enter data into normal XL tables.

For those with VBA skills, this tool provides a wealth of code and examples that make creating web scrapers quick and easy. Included are functions to populate and submit web forms opening up websites that require sign-ons or input parameters for selecting data – such as a Google search.

ITKnowledgeExchange readers can get a copy of this workbook by emailing me at: Craig_Hatmaker At Yahoo Dot Com

 Comment on this Post

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when other members comment.

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

Share this item with your network: