Using Excel VBA to Open and Autofill Web Pages

This post is more about manipulating web pages than creating them.

My employer had a 2007 excel spreadsheet that they used to track several hundred projects. They would open a browser then copy/paste the information into a modified Google map. My boss asked me if there was a way I could automate this process.

I found parts to the solution here and there. I hope this will help someone else. It was a fun challenge and led to other similiar projects. Please remember, I am not a professional programmer so feel free to add better suggestions to comments.

Since the actual project cannot be duplicated here let’s just say we have a spreadsheet with a customer’s Name, Address, City, State and Zip. We want to send that information to Google and see the map.

Click here to download the example created in 2007 and saved as xls: http://www.webdebris.com/test/GetMapExample.zip

First thing I will need is a button. Switching over to the developer tab I add an active X button and call it GetMap. Adding it above the header, which can be froze, will let the button be available always.

Next I need to add a reference to Microsoft’s web browser to the excel spreadsheet. In the visual basic editor, go to Tools References and add Microsoft Internet Controls.

Now it is time to start coding.

Option Explicit

Public ie As Object

Public strAddress As String 'Builds the address to send to Google

'The following line will let the code sleep while waiting for the page to load
Private Declare Sub Sleep Lib "kernel32.dll" (ByVal dwMilliseconds As Long)
'When working in excel the browser will stay in the background. This will bring the browser to the top
Private Declare Function BringWindowToTop Lib "user32" (ByVal hwnd As Long) As Long

Private Sub GetMap_Click()

'There is a bug/feature with Excel that when the IE window closes the ie object still has a reference.
'Normally you would check for the IE window to close and then set the reference to nothing.
'Web Control does not have an on_quit event. So you have to trap by error.
On Error GoTo ErrHandle:

  Dim strCursorLocation As String 'The location of the cursor when the button was clicked
  Dim strRow As String 'Will hold the row number
  Dim i As Integer

  strCursorLocation = ActiveCell.Address(False, False) 'Get the current cursor location
  strRow = ""
  
  'Remove letters from strCursorLocation
  For i = 1 To Len(strCursorLocation)
  
    If IsNumeric(Mid(strCursorLocation, i, 1)) Then
        
            strRow = strRow & Mid(strCursorLocation, i, 1)
            
    End If
    
  Next i

  'Build the address by adding columns B thru E
  strAddress = Range("B" + strRow).Value + " "
  strAddress = strAddress + Range("C" + strRow).Value + " "
  strAddress = strAddress + Range("D" + strRow).Value + " "
  strAddress = strAddress + Range("E" + strRow).Value + " "

  'Check to see if an IE window has been opened
  If ie Is Nothing Then

    Call OpenGoogle

  End If

  'Bring IE to top. Does not bring to top 100 percent of the time.
  If Not ie Is Nothing Then

    BringWindowToTop (ie.hwnd)

  End If

  Call EnterAddress

  Exit Sub

ErrHandle:

  'Reopen IE window if it was closed
  If Err.Number = -2147417848 Or Err.Number = 462 Then

    Call OpenGoogle
    Resume
    
  Else

    'Something else has happened
    MsgBox "Error: " + CStr(Err.Number) + " " + Err.Description, vbCritical
    
  End If
    
End Sub

Private Sub OpenGoogle()

  'Create IE Window
  Set ie = CreateObject("Internetexplorer.Application")
  'Show IE Window
  ie.Visible = True
  'Navigate to the google map page
  ie.Navigate "http://maps.google.com/maps?hl=en&tab=wl"

  'Wait for the page to load
  Do While ie.Busy = True

    DoEvents
    Sleep 500

  Loop

End Sub

Private Sub EnterAddress()

  'Check to see if map page was navigated away from
  If ie.document.URL <> "http://maps.google.com/maps?hl=en&tab=wl" Then
    
    'Reload map page
    ie.Navigate "http://maps.google.com/maps?hl=en&tab=wl"

    'Wait for the page to load
    Do While ie.Busy = True

      DoEvents
      Sleep 500

    Loop

  End If

  With ie

    .document.all("q_d").Value = strAddress  'enter the address
    .document.all("q-sub").Click  'click the search button
    
  End With

End Sub

Liquid, left, or middle? What to do, what to do.

I used to be all about liquid layouts. But these days there are more and more people using wide screen monitors. The liquid layout design can turn ugly quick on one of these displays. If you have a lot of information on web page it seems to work okay. For me, more often then not the amount of information on a page will vary greatly.

When I see sites built on the left through a wide screen it all seems out of balance. I think the key to left built sites could be a background graphic that would balance it out.

On the other hand, I am now seeing web sites that have been developed on wide screens. When I display them on my older CRT monitor the columns are really scrunched up and hard to read. When I move the page to my wide screen it looks fine.

So now I am becoming a fan of centered pages. All the sites I have done recently are centered and usually have a width of 1000 pixels. I spent some time on http://www.statowl.com/ and looked through the information they have. Looks like 800 x 600 display modes and less are almost gone. 1024 x 768 and greater are the norm.

Even though I like strict, clean, and bare bones code I do not like to sacrifice design because of a few people using older browsers or displays. (Hmmm... wonder if the people developing sites on wide screens feel the same way about non wide screen monitors.)

Just curious as to other viewpoints.