iRadar: How To Make Your Own Basic Internet Browser Using VBA

Thursday, December 15, 2011

How To Make Your Own Basic Internet Browser Using VBA

How To Make Your Own Basic Internet Browser Using VBA:

When you really stop to think about it, an Internet browser in its simplest form isn’t really that impressive an application. I mean, yes, the Internet is amazing by anyone’s standards. The concept of linking so many of the world’s computers and mobile devices within this massive network, that is epic. But the concept of transferring a text file with special code in it, and displaying that transferred text onto the computer screen – that’s really not a big deal.

In fact, thanks to the embedded references in applications that use VBA, you can input and output webpages via your own applications. If you’ve followed our past scripting articles, then you know we love VB script and VBA, like my article on maximizing windows, Paul’s on self-updating charts, and Saikat’s on sending out mass emails.

Using the approach below, you can use Word, Access or Excel to read HTML documents on the web, alter those documents, and then output whatever you want in a new browser window.

It may sound like something that only an experienced VBA programmer can do, but in this article I’m going to show you how you can do this yourself, starting with a basic procedure to output data from within your own application to a webpage.

Enabling Microsoft Internet Controls

The first step, no matter how you want to make use of the ability to input/output your own HTML via VBA, is to enable the Microsoft Internet Controls reference.

In Excel, you do this by clicking on the “Developer” menu item, clicking Design Mode, and then clicking on the “View Code” button. This may appear different in older versions of Excel, but essentially you need to find where you can launch the VBA code editor.

In the VBA editor, click on Tools, and then References.

In the References window, scroll all the way down until you see “Microsoft Internet Controls“. Check the box and then click “OK“.

Now, within your VBA code, you will be able to create objects that can get information off the Internet, and you can output your own HTML documents that the user can view in their own browser.

Inputting and Outputting HTML

The first thing that you’ll want to accomplish here is outputting information to HTML. Think about how much you can do once you can output information to a perfectly formatted HTML webpage. You can extract data from your Excel spreadsheets and output them in a nicely formatted report.

To create this feature, you’ll need to insert a command button on your sheet. Under the Developer menu, click on Design Mode, and then click the “Insert” button. You’ll see a dropdown of controls that you can add to your sheet.

In this case, click the button control and place it in your sheet. Make sure there’s some data in the sheet (or any sheet), that you want to output to a report when the button is clicked. Left click on the button to select it and click “View Code“.

Enter the following code:

Sub Button1_Click()
Dim objIE As Object
Dim HTML As String
'----------The HTML CODE GOES FROM HERE AND DOWN----------
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 GOES 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 outputs the HTML that you’ve defined in that output string. As you can see from my example above, you can embed data from any sheet in Excel into your HTML string. Here is the resulting webpage report:

Now that you have HTML output working, the next step to make your VBA script work virtually like a web browser is to read in HTML from some website, manipulate the data, and then output it into an HTML output 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

What I’ve done here is used the IE object to read HTML from Google into a text variable in VBA called HTML. I then placed my own HTML output text in the top of the page, and then outputted the same HTML to the VBA HTML output. This is what that looks like:

This is a very basic example of what this is capable of. But if you think about it, once you have the HTML from a webpage loaded into your VBA string variable, you can search for specific lines in the page, look for images, search out email addresses of phone numbers – or you could rewrite the entire input page into a new, edited version of the same page in whatever format you like and with any modifications you like.

Doing this would require a bit of string manipulation prowess – but nothing is impossible once you’ve read in the HTML source from the web.

Do you see any potential for using either HTML input or output in your own applications? Can you think of any cool uses for this script? Share your thoughts in the comments section below.

Image credit: Shutterstock