In addition to the techniques described in Rest to Excel library, it is possible to use an internet explorer object to access the DOM directly from VBA.
This condensed article is from an original by a guest contributor, Petros Chatzipantazis of spreadsheet1.com.
Internet Explorer provides users with the option to disable the loading of pictures from a webpage. Disabling this feature allows your browser to move much faster because images, which can take a while to display due to their significant file size, are not loaded or shown. If you plan to extract data only, you may be interested just in the text information of a webpage. VBA does not have any way to set these options of an internet explorer object directly. This is because they are actually controlled from a registry entry. Here is how to toggle this setting, by tweaking the registry contents.
Although VBA includes the SaveSetting and GetSetting functions to save and retrieve information from the registry, these functions only operate on a specific section of the registry, the Visual Basic and VBA Program Settings of the HKEY_CURRENT_USER root key.
The code below demonstrates how to use 32/64 bit Windows API functions to set values anywhere in the registry
e.g. as required to toggle the 'Show pictures' option of Internet Explorer.
The Win64 conditional compilation constant is used to test whether code is running as 32-bit or as 64-bit.
In versions of VBA prior to Excel 2010, there was no specific pointer data type so the Long data type was used. And because the Long data type is always 32-bits, it breaks when used on a system with 64-bit memory, because the upper 32-bits may be truncated or may overwrite other memory addresses.
Either of these situations can result in unpredictable behavior or system crashes. To resolve this, VBA now contains a true pointer data type: LongPtr. This new data type enables developers to write valid Declare statements for execution with the 64-bit version of Office 2010.
Services > Desktop Liberation - the definitive resource for Google Apps Script and Microsoft Office automation > Guest articles and postings >