Create a Basic Web Browser with VBA - Tutorial
The Surprisingly Simple Nature of Web Browsers
Upon closer examination, the core functionality of an internet browser isn't particularly complex. While the internet itself represents a monumental achievement, the fundamental process it relies on is relatively straightforward.
The interconnectedness of global computers and mobile devices within a vast network is undeniably impressive. However, the act of transmitting a text file containing specific code and then rendering it on a screen is, in essence, a basic operation.
Leveraging VBA for Web Interaction
Applications utilizing VBA offer the capability to both receive and transmit webpages directly from within their environment. Readers familiar with our previous scripting guides will recognize our enthusiasm for VB script and VBA.
Examples include articles detailing window maximization techniques, the creation of self-updating charts, and methods for dispatching mass emails.
This approach allows users to utilize programs like Word, Access, or Excel to retrieve HTML documents from the web.
Modifying and Outputting Web Content
These retrieved documents can then be modified within the application, and the resulting content can be displayed in a new browser window. This provides a powerful level of control over web content.
Accessibility for All Skill Levels
Although it might appear to be a task reserved for seasoned VBA developers, this article will demonstrate how individuals with varying levels of experience can achieve this functionality.
We will begin with a foundational procedure illustrating how to output data from your application to a webpage. This will serve as a stepping stone to more complex operations.
Activating Microsoft Internet Controls in Excel
To begin utilizing the capability of handling HTML input and output through VBA, the initial step involves enabling the Microsoft Internet Controls reference within Excel.
Within Excel, navigate to the "Developer" tab, select Design Mode, and subsequently click the "View Code" button. The appearance of this process may vary across different Excel versions, but the core objective is to access the VBA code editor.
Once inside the VBA editor, click on Tools, then select References.
In the References window, locate "Microsoft Internet Controls" by scrolling down the list. Check the corresponding box and then confirm your selection by clicking "OK".
With this reference enabled, your VBA code will be capable of creating objects to retrieve data from the Internet and generate HTML documents for user viewing within a web browser.
Handling HTML Input and Output
A primary task is to output information to HTML. Consider the possibilities that arise when you can generate a perfectly formatted HTML webpage. Data can be extracted from Excel spreadsheets and presented in a well-structured report.
To implement this functionality, insert a command button onto your worksheet. Under the Developer menu, activate Design Mode, and then click the "Insert" button. A dropdown menu will appear, displaying the available controls for addition to your sheet.
Select the button control and position it on your sheet. Ensure that data exists within the sheet (or any sheet) that you intend to output to a report when the button is activated. Left-click the button to select it, then click "View Code".
Enter the following code:
Sub Button1_Click()
Dim objIE As Object
Dim HTML As String
'----------The HTML CODE STARTS HERE AND CONTINUES DOWNWARD----------
HTML = "<HTML><TITLE>HTML Report Page</TITLE>" & _
"<BODY><FONT COLOR = BLUE><FONT SIZE = 5>" & _
"<B>The Following Are Results From Your Daily Calculation</B>" & _
"</FONT SIZE><P>" & _
"Daily Production: " & Sheet1.Cells(1, 1) & "<p>" & _
"Daily Scrap: " & Sheet1.Cells(1, 2) & "<p></BODY></HTML>"
'----------The HTML CODE ENDS HERE AND ABOVE---------
On Error GoTo error_handler
Set objIE = CreateObject("InternetExplorer.Application")With objIE
.Navigate "about:blank"
Do While .Busy: DoEvents: Loop
Do While .ReadyState <> 4: DoEvents: Loop
.Visible = True
.Document.Write HTML
End With
Set objIE = Nothing
Exit Sub
error_handler:
MsgBox ("Unexpected Error, I'm quitting.")objIE.Quit
Set objIE = Nothing
End Sub
This code generates the HTML you've defined within the output string. As illustrated, data from any Excel sheet can be embedded into your HTML string.
Having established HTML output functionality, the subsequent step towards emulating a web browser within your VBA script is to read HTML from a website, manipulate the data, and then output it to an HTML page.
Sub Button1_Click()
Dim objIE As Object
Dim HTML As String
On Error GoTo error_handler
Set objIE = CreateObject("InternetExplorer.Application")With objIE
.Navigate "http://www.google.com"
Do While .Busy: DoEvents: Loop
Do While .ReadyState <> 4: DoEvents: Loop
.Visible = True
HTML = objIE.Document.Body.innerHTML
.Document.Write "<html><title>My Own Google Results!</title><body><h1>This is an Edited Version of the Google Page!</h1>" & HTML & "</body></html>"
End With
Set objIE = Nothing
Exit Sub
error_handler:
MsgBox ("Unexpected Error, I'm quitting.")objIE.Quit
Set objIE = Nothing
End Sub
This code utilizes the IE object to retrieve HTML from Google and store it in a VBA string variable named HTML. It then prepends custom HTML text and outputs the combined content. The resulting webpage is shown below:
This is a fundamental illustration of the capabilities of this approach. However, once the HTML from a webpage is loaded into a VBA string variable, you can search for specific lines, locate images, extract email addresses or phone numbers, or even rewrite the entire page into a new, modified version in any desired format.
Achieving this would necessitate some proficiency in string manipulation, but it is entirely feasible once the HTML source is read from the web.
Can you envision any potential applications for either HTML input or output within your own projects? Share your ideas in the comments section below.
Image credit: Shutterstock
