copy

Friday, 4 May 2012



Web Browser In The Spreadsheet


Introduction



While I was hanging around the net, I found an easy way to have a copy of internet explorer in a spreadsheet. Why this might be useful? Well, since the explorer will be incorporated in the spreadsheet, you will not have to open a separate (external) instance of internet explorer. So, in cases you want to copy some data from a web page, you will avoid the disturbing switching between excel and internet explorer. Furthermore, since the explorer will be incorporated in a modeless form you will be able to continue your excel work normally.



How to do it



Step 1: In the VBA editor (ALT + F11), create a userform as usual: Right click at any of the objects and then insert -> UserForm.

Web Browser 1

Step 2: In order to add the Webbrowser object you should do the following: Right click on the toolbox and select Additional Controls.

Web Browser 2

Next, from the available choices, select Microsoft Web Browser and then click OK.

Web Browser 3

Step 3: Add controls to your userform and format it according to your needs and taste.

Web Browser 4

Step 4: Finally, use the code below in order to make your browser functional.



VBA code



Option Explicit

    '-------------------------------------------------------------------
    'This is the code for handling the various buttons in web browser.
    
    'Written by:    Christos Samaras
    'Date:          03/05/2012
    'e-mail:        xristos.samaras@gmail.com
    'site:          http://www.myengineeringworld.net
    '-------------------------------------------------------------------

Private Sub btnGo_Click()
        
    'Handles the Go button.
    Me.objWebBrowser.Navigate2 Me.txtURL.Value
    
End Sub

Private Sub btnForward_Click()

    'Handles the Forward button.
    On Error Resume Next
    Me.objWebBrowser.GoForward
    
End Sub

Private Sub btnBackward_Click()

    'Handles the Backward button.
    On Error Resume Next
    Me.objWebBrowser.GoBack
    
End Sub

Private Sub btnHome_Click()

    'Handles the Home button.
    On Error Resume Next
    Me.objWebBrowser.GoHome

End Sub

Private Sub btnExit_Click()

    'Handles the Exit button.
    Me.Hide

End Sub

Private Sub UserForm_Initialize()

    'Aligns the userform at the top left corner.
    Me.StartUpPosition = 0
    Me.Top = 0
    Me.Left = 0
     
    'Setting the initial page of browser.
    Me.objWebBrowser.Navigate2 "http://www.myengineeringworld.net/"

End Sub

Following the procedure that was described above, I created a workbook that contains a modeless form with a webbrowser object. Try yourself and create your “own version” of internet explorer inside Excel.



Update 22/10/2013



Update

What I really like in this blog is the conversation with other people through comments., since I have the opportunity to learn new things. Yesterday for example Mike pointed out that the web browser control doesn't work anymore with Google Maps page. He also suggested a solution, which I would like to add in case someone has the same issue.

Problem: the web browser control uses the Internet Explorer (IE) 6 settings. It seems that Google Maps stopped supporting that version, so if someone tries to open Google Maps the page looks like frozen.  

Solution: it requires a small change in registry. Below there are instructions for Windows 7, although similar steps can be used in other Windows versions:

1. Press the Start button, in the search box write regedit and press enter.

2. If you have 32-bit Office go to HKEY_LOCAL_MACHINE\Software\Microsoft\Internet Explorer\Main\FeatureControl\FEATURE_BROWSER_EMULATION. For 64-bit Office the location is slightly different: HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Microsoft\Internet Explorer\MAIN\FeatureControl\FEATURE_BROWSER_EMULATION.

3. Add a new DWORD (32-bit) Value in the folder (using the right mouse click) and name it Excel.exe.

Registry Edit - Web Browser Control

4. Double click to Excel.exe and in the edit window select a Decimal Base (using the radio button).

5. According to your Internet Explorer version that you have installed at your computer fill one of the following values:
  • 7000 - Internet Explorer 7
  • 8000 - Internet Explorer 8
  • 9000 - Internet Explorer 9
  • 10000 - Internet Explorer 10
Web Browser Control - New Registry Value

More info about these values can be found at Microsoft's site. If you have followed the previous steps successfully you will be able to run Google Maps from the sample file.

Google Maps In Web Browser Control

I would like to thanks once again Mike for his contribution.



Download it from here



Download

This file can be opened with Excel 2007 or newer. Please, remember to enable macros before using this workbook.

Did you like this post? If yes, then share it with your friends. Thank you!


Categories:


Mechanical Engineer (Ph.D. cand.), M.Sc. Cranfield University, Dipl.-Ing. Aristotle University, Thessaloniki - Greece.
Communication: e-mail, Facebook, Twitter, Google+ and Linkedin. More info