Thursday, February 18, 2010

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:

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


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

Call OpenGoogle


'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 ""

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

Sleep 500


End Sub

Private Sub EnterAddress()

'Check to see if map page was navigated away from
If ie.document.URL <> "" Then

'Reload map page
ie.Navigate ""

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

Sleep 500


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

Wednesday, February 17, 2010

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 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.