Pinterest Stumbleupon Whatsapp
Ads by Google

make your own internet browserWhen 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 3 Ways To Open Applications & Windows Maximized With VB Script [Windows] 3 Ways To Open Applications & Windows Maximized With VB Script [Windows] If there is one thing I love to do, it's write VB scripts. Whether it's a Windows Script file or a VBA script inside an application, scripting provides the ability to add functionality to applications... Read More , Paul’s on self-updating charts How To Create Self-Updating Excel Charts In Three Easy Steps How To Create Self-Updating Excel Charts In Three Easy Steps Read More , and Saikat’s on sending out mass emails How To Send Personalized Mass Emails in Outlook How To Send Personalized Mass Emails in Outlook Read More .

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.

Ads by Google

make your own internet browser

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

make an internet browser

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

make an internet browser

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.

make an internet browser

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:

make your own internet browser

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

  1. mona
    August 18, 2016 at 10:51 am

    I have excel sheet rows of about 100 lines, however using above code, i can have html output of 20 rows /lines only. for next 60 lines do i need a separate page. how to write in VBA

  2. GoodGravey
    February 18, 2012 at 11:58 pm

    Sorry - didn't think about how the code would look in the submitted comment.  Delete if you want and I'll re-post in a better format.

    As for the comments about VBA being archaic, I have found it really useful.  It allowed me to turn a week-long process in my team into a 10 minute process.  It allowed me to scale back most of the activities my team does by automating almost everything.

    Been able to apply AD validations, extract data from various operational systems databases, and cut back all the pesky SQL queries then cutting and pasting.

    Changed a job that required multiple queries, manually generating multiple emails into a single button-click.  

    I absolutely love it, archaic or not.

    • Ryan Dube
      February 19, 2012 at 4:56 am

      GG - I agree completely. Harnessing the power of VBA allows just about anyone with a bit of programming prowess (you don't need to be a guru) with the ability to automate tasks, increase overall efficiency, and get the job done. It's a great tool.

  3. GoodGravey
    February 18, 2012 at 11:53 pm

    I found something along these lines some time ago and developed an Excel-based application that allowed me to track the number of "Likes" on a set of specified Facebook pages.  I've since extended that to use VBA to get all sorts of information on various websites.

    My problem at the moment is that I am having some trouble with IE and tend to use Chrome.

    How do I translate the sort of code you have above so that it does the same thing but using Chrome as the browser.  I can use a Shell command to open the Chrome browser and go to a specific URL, but can't figure out how to extract information off that page.

    As an example, my Facebook code is:

    Sub GetLikes()Dim IeApp As InternetExplorerDim sURL As StringDim IeDoc As ObjectDim i As Longdebugmode = False        If debugmode Then Open "c:VBAoutputvbaOutput.txt" For Append As #1    'Create new instance of IE    Set IeApp = New InternetExplorer    'Make it visible - some things don’t work unless it’s visible    IeApp.Visible = debugmode        For Each mc In Selection'define the page to open        sURL = mc            'Find the column            chkDate = Date            Set c = Range("2:2").Find(chkDate)            If c Is Nothing Then                myCol = Range("2:2").Find(what:="*", searchdirection:=xlPrevious).Column + 1                Cells(1, myCol) = "Status"                Cells(1, myCol + 1) = "Likes"                Cells(1, myCol + 2) = "Links"                Cells(2, myCol) = chkDate                Cells(2, myCol + 1) = chkDate                Cells(2, myCol + 2) = chkDate            Else                myCol = c.Column            End If    '        mc.Offset(0, 1).Value = Date                isDown = mc.Offset(0, 3).Value        If isDown = "Page Down" Then            pageDown = "Page Down"            numlikes = ""            numlinks = ""            doNothing = True        Else            'navigate to the page            IeApp.Navigate sURL            'Pause the macro using a loop until the page is fully loaded            Do            Loop Until IeApp.ReadyState = READYSTATE_COMPLETE            'store the Document object            Set IeDoc = IeApp.Document            On Error Resume Next            codes = IeDoc.Body.innertext            If Err.Number 0 Then                Err.Clear                GoTo skiploop            End If            On Error GoTo 0            If debugmode Then Write #1, codes & vbCrLf & vbCrLf                        numlinks = IeDoc.Links.Length            a = IeApp.LocationName        '        Debug.Print codes                pageDown = True            isPageStats = InStr(codes, "placePageStatsNumber")            isGroup = InStr(codes, "uiButtonText"""">Join")            isOldGroup = InStr(codes, "This group is scheduled to be archived")            isOpenGroup = InStr(codes, "Open Group")            isEvent = InStr(codes, "Public Event")            isClosedGroup = InStr(codes, "Closed Group")            IsOpen = InStr(codes, a)            isCommonInterest = InStr(codes, "Common Interest")            isMovie = InStr(codes, "Movieu003c")                        isNumberGiant = InStr(codes, "uiNumberGiant")            notFound = InStr(codes, "The page you requested was not found")            profileUnavailable = InStr(a, "Profile Unavailable")                        titlePos = InStr(codes, "")            If titlePos > 0 Then titlePos2 = InStr(titlePos, codes, "")            If titlePos > 0 Then titleName = Mid(codes, titlePos + 7, titlePos2 - titlePos - 7)                        If isPageStats > 0 Then                isPageStats = isPageStats + 23                pos2 = InStr(isPageStats, codes, "")                pageDown = "Current"                likeTxt = Mid(codes, isPageStats, pos2 - isPageStats)            ElseIf isGroup > 0 Then                likeTxt = "n.k."                pageDown = "Group"            ElseIf isOldGroup > 0 Then                likeTxt = "n.k."                pageDown = "Old Group"            ElseIf isOpenGroup > 0 Then                pos1 = InStr(isOpenGroup, codes, "Members (") + 9                pos2 = InStr(pos1, codes, ")")                numMembers = Mid(codes, pos1, pos2 - pos1)                pageDown = "Open Group"                likeTxt = LTrim(numMembers)            ElseIf isEvent > 0 Then                pos1 = InStr(1, codes, "Help Center")                pos2 = InStr(pos1 + 1, codes, "See All") + 9                pos3 = InStr(pos2, codes, "Attending") - 1                If pos3 = -1 Then                    pos1 = InStr(codes, "pagelet_event_guests_going") + 28                    pos2 = InStr(pos1, codes, ">Going (") + 8                    pos3 = InStr(pos2, codes, ")")                    numAttending = Mid(codes, pos2, pos3 - pos2)                Else                    numAttending = Mid(codes, pos2, pos3 - pos2) 'pos2 - pos1)                End If                likeTxt = LTrim(numAttending)                pageDown = "Event"            ElseIf isClosedGroup > 0 Then                pageDown = "Closed Group"                pos1 = InStr(isClosedGroup, codes, "Members") + 9                pos2 = InStr(pos1, codes, ")")                likeTxt = Mid(codes, pos1, pos2 - pos1)            ElseIf isNumberGiant > 0 Then                pos2 = InStr(isNumberGiant, codes, ">")                pos3 = InStr(pos2, codes, "")                likeTxt = Mid(codes, pos2 + 1, pos3 - pos2 - 1)                pageDown = "Current"            ElseIf isCommonInterest > 0 Then                pageDown = "Common Interest"                likeTxt = "n.k."            ElseIf IsOpen > 0 And a "Facebook" Then                pageDown = "Current"                likeTxt = "n.k."            ElseIf isMovie > 0 Then                pageDown = "Movie"                likeTxt = "n.k."            End If            If pageDown = True Then                pageDown = "Page Down"                likeTxt = "n.a."                numlinks = "n.a."            End If                End If        mc.Offset(0, myCol - 2).Value = pageDown        mc.Offset(0, myCol - 1).Value = likeTxt        mc.Offset(0, myCol - 0) = numlinksskiploop:        pageDown = ""        likeTxt = ""        numlinks = ""    Next mc'Clean up        IeApp.Quit    Set IeApp = Nothing    If debugmode Then Close #1    End Sub

    It's clunky but does the job.

    I guess all I am after is how to extract .Body.innertext from a Google Chrome instance.

  4. Andrew
    December 18, 2011 at 3:51 pm

    Like Jeff said, not to replace your browser, but to utilize it for a specific application.  I've used this method twice.

    I belong to a local hobby club, but you must also be a member of the national club.  I used to use an Excel web query to  cycle through our membership (by national member ID number) and verify members were paid up in the national organization.  But a few years back, the national organization changed they way their site runs and the web query would no longer work.  But this method still lets me quickly cycle through the membership and visually verify the national membership.  (I embed the browser in an Excel form that simultaneously displays our local club's member information.)

    Then last week, at work, we needed to perform a accuracy audit on the information contained in about 10,000 images; comparing the information captured on the image to the related information in our database and assuring they matched.  So I needed a way for users to quickly look at an image, type in some information contained on the image, and repeat this over-and-over 10,000 times.  We put the images on a web server and I used this method to pull the image into a browser window on an Excel data entry form.  Users would see the image, type in the required information on the data entry form, press the enter-key, and instantly be taken to the next image for review.

    For applications such as I've described above, it is quite handy.  I'm likely never going to take the time to become proficient in any 'more serious' programming language, but I can record macros and snippet my way through VBA well enough.  The accuracy audit took me less than a day to fully develop, test, and implement.  I didn't need to pull developer resources off their assigned tasks (and I seriously doubt any of our Java or PL-SQL developers could have done it any faster than I did using vanilla Excel and VBA.

    • Belleye
      February 5, 2012 at 8:32 am

      I agree; some people make money making software, others make money by getting the job done.

  5. Chompinski
    December 14, 2011 at 7:47 pm

    wow... VBA?  talk about archaic.

    • Jeff Fabish
      December 15, 2011 at 7:29 am

      This language makes me cringe, it's a bad language to learn as it teaches poor habits and enforces sloppy syntax.

      • Ryan Dube
        December 17, 2011 at 8:36 pm

        VBA isn't too bad - although I agree that it is very scaled down from some of the more structured languages I've used before.

        However, it isn't at all archaic - it is still used significantly throughout most businesses, in manufacturing data analysis and elsewhere. Unfortunately (in my opinion), I think it's so prevalent because it's the backbone of MS Office scripting, and it's very easy for managers and non-programmers to use (because, as Jeff correctly points out, you're allowed to be sloppy).

        But the fact remains that it is very much in use, and if you know how to play around with it, you can accomplish some pretty cool things.

  6. Anonymous
    December 14, 2011 at 1:51 pm

    Cool. But WHY? Google Chrome is good enough for me....

    • Jeff Fabish
      December 15, 2011 at 7:28 am

      When programming, it is best to apply the knowledge you have gained in a practical application. This by no means was meant to replace your mainstream browser (in the background, it still uses Internet Explorer controls anyway). 

      • Anonymous
        December 15, 2011 at 12:44 pm

        I see...interesting article.

      • Ryan Dube
        December 17, 2011 at 8:38 pm

        Yes, Jeff is right. Technically you aren't really creating a new browser - you're utilizing the embedded IE controls to read HTML into your application and then process it as you wish.

        Maybe more of a novelty in the simple form I laid out in my example, but embellished with a bit of your own code, it could become a pretty useful script. It really comes down to requirements.

Leave a Reply

Your email address will not be published. Required fields are marked *