Web-Scraper for Excel
This README gives a summary of the main points of Web-Scraper for Excel.
A sample of source code showing the two main functions are included in this GitHub listing to evaluate its merits. If needed, you will find information on how to import bas files into Excel using the Google AI search assistant.
As this Excel programme includes features that do not lend themselves easily to a text-only description, you may find it better to study the entire working programme using Excel's built-in VBA IDE editor and its built-in debugger to single-step through the code. (F8 key)
This programme can be found at this website:
A downloadable and runnable version of the WebScraper program with the example data can be found in the download files section, on the second page, in (Other--For Programmers >), in the downloadable zip file: Scrape4T.zip
Its checksum can be found by a right click in 7-Zip > CRC SHA context option > SHA-256
Name: Scrape4T.zipSize: 1133740 bytes : 1107 KiBSHA256: 9c80758088cd06e836ab87afbd83b0a311814b20990fbdafff1f0e304ed9df46
You will also need to download a copy of SeleniumVBA from:
https://github.com/GCuser99/SeleniumVBA
Of Interest:
The code for the CAL100 project has implemented all the points mentioned in this document since 2022 without issues.
The Clipboard reader code for HTML is based on:
Public Function GetClipboardDataAsString ' * Programmer Name : Waty Thierry ' * Date : 7/10/98
(No License mentioned)
Which I am guessing was written for Excel 97 (v8.0). I updated this code in 2021 for my CAL100 project and again in 2024 to support 64-bit Excel.
Listing the code of Web-Scraper for Excel
The central idea of listing this programme on GitHub is to highlight the advantages of storing HTML files with line feeds added for readability
Apart from the variations of a Pretty Print approach to newlines, I believe there is a better way if web scraping is the primary reason for adding them. This uses the same placement technique as the Microsoft HTMLDoc object does in Excel 2007 for adding newlines.
This placement (in essence) follows the rule of one element class per line for CSS block elements.
This format seems to break down the HTML into easy-to-read, bite-sized chunks rather than presenting it as complete and valid XML lines.
In addition, each text line containing viewable data will almost always store the text near the end of each line. e.g.
Line 294
<P class="chakra-text css-1p1nmwo" style="border-width: 0px; border-style: solid; box-sizing: border-box; overflow-wrap: break-word; margin: 0px; border-color: rgb(238, 238, 238); font-size: 14px; line-height: 20px; letter-spacing: 0px; font-weight: 400; color: rgb(61, 61, 61);">Carbohydrate</p></td>
Line 295 More….
It should be noted that the line breaks can be selectively removed with a Replace command to suit your preference for the importance of maintaining an XML bracket structure.
This bite-size format also means that table column & row position information largely remains with the displayed text on the same line.
A second advantage immediately apparent upon opening these files is that they can now include header information, such as the SourceURL address, before the main HTML body.
You will find a second README in the ZIP file download that hopefully gives you a better idea of my reasons for using this format.
The code has been mainly developed in Excel2007 (32bit) but has been also been tested in Excel2016 (32bit), Excel2021(64bit) and Excel2024 (32bit).
The offline scraper files were extensively used (about 800 scrapes) to test the CAL100 program.
CAL100, however, completes further stages of table processing, including additional unicode processing, handling of transposed and missing lines and values, conversion of North American format food labels to a UK/Australia/Europe format, formatting of numbers with a variety of thousand and decimal marks, and a simple spell-check.
It also uses a "double-entry accountancy" method to verify all the table values.
This processing uses a hybrid of normal logic and regular expressions.
At some point, I may also release the code for this.
Incidentally, I would be interested in any comments on the CAL100 project if you have time.
My hope is that this program will be adopted as a tool that GCSE home economics students, when familiar with its use, should be able to advise on healthy diets nearly as well as a qualified nutritionist.
Hopefully, you find the VBA code of WebScraper easy to read and useful. My philosophy is that code should be easy enough to read three years after it was written (and forgotten about), and the boss is shouting for a change by 8 am.