Excel VBA open website and paste something in a box

Factual Questions
1

I have an Excel sheet with names and addresses and I want to select certain addresses, copy them to the clipboard, and paste them into a box on a website. I have the code done that copies the addresses and opens the website, but once I get there I have to manually click a link to bring up the box and the manually paste the addresses in. I would like to automate this start-to-finish. Here is the website:

http://gebweb.net/optimap/

Once there I want to click the link for “Add Bulk by Address” which pops up a box, and then paste the addresses from the clipboard, and then click the “Calculate Fastest Roundtrip” link.

Any ideas? Thanks!

2

I wasn’t aware Excel VBA could control applications other than Excel. I await responses to this.

3

Googling suggests that it is possible, but I am stuck.

4

VBA is quite powerful but some of this functionality is not obvious. Don’t Call Me Shirley said he has already got this part working but just to illustrate, here is how you would open Internet Explorer and open a particular web page:


Public Sub test()

    Dim IE As Object
    'Dim shellWins As New ShellWindows
    
    Set IE = CreateObject("InternetExplorer.Application")
    IE.Visible = True
    
    IE.Navigate "http://boards.straightdope.com"
    
    Do While IE.Busy
        DoEvents
    Loop

End Sub

I don’t know how to copy data into HTML forms, though.

5

The quickest way to get from where you are to where you want to be might be to use a (free) Macro Recorder.

Several of them are listed here, but I have been using AutoHotKey for several years and it will almost certainly do what you want.

Run your existing VBA macro, start the Macro Recorder, then go through the steps::
[ol]
[li]click the “Bulk add by address or (lat, lng)” link[/li][li]put your cursor over the highlighted text in the box[/li][li]right-click[/li][li]Paste[/li][li]click “Calculate Fastest Roundtrip” link.[/li][/ol]
Then Stop the Macro Recorder, and save the steps as an EXE file.

Finally, add a line at the end of your VBA macro to launch the above EXE file. In future, when you run the VBA macro you should now see all of the steps execute.

6

I hate VBA and don’t know all it’s capabilities, but if you can imbed Microsoft’s browser control in it, the next steps would not be hard. An alternative would be to write a separate program that manipulates the browser.

7

Because there was some interest in this, I’m posting the solution I worked out. I don’t like that I have to use SendKeys to paste the addresses in the box, so I’m still looking for a better way to do that, but this code works. It loops through the links or buttons on the page until it finds the right one to click.

You must add references to Microsoft Internet Controls and Microsoft HTML object library.


Sub map_it()
Dim appIE As Object ' InternetExplorer.Application
Dim sURL As String
Dim Element As Object ' HTMLButtonElement
Dim btnInput As Object ' MSHTML.HTMLInputElement
Dim ElementCol As Object ' MSHTML.IHTMLElementCollection
Dim Link As Object ' MSHTML.HTMLAnchorElement

Set appIE = GetIE
 
sURL = "http://gebweb.net/optimap/"

With appIE
    .Navigate sURL
    .Visible = True
    Do While .ReadyState <> READYSTATE_COMPLETE
        Application.Wait Now + TimeValue("0:00:01")
    Loop
End With

Do While appIE.Busy
DoEvents
Debug.Print "busy"
Loop

Application.Wait Now + TimeValue("0:00:02")

Set ElementCol = appIE.Document.getElementsByTagName("a")
 
For Each Link In ElementCol
    Debug.Print Link.innerHTML
    If InStr(1, Link.innerHTML, "Bulk add by address or (lat, lng)") Then
        Link.Click
        Exit For
    End If
Next Link

Application.Wait Now + TimeValue("0:00:01")

SendKeys ("^(v)")

Application.Wait Now + TimeValue("0:00:01")

Set ElementCol = appIE.Document.getElementsByTagName("INPUT")
 
' loop through all 'input' elements and find the one with a specific value
For Each btnInput In ElementCol
    Debug.Print btnInput.Value
    If btnInput.Value = "Add list of locations" Then
        btnInput.Click
        Exit For
    End If
Next btnInput

    Do While appIE.Busy
        Debug.Print "waiting to map"
        Application.Wait Now + TimeValue("0:00:01")
    Loop

For Each btnInput In ElementCol
    Debug.Print btnInput.Value
    If btnInput.Value = "Calculate Fastest Roundtrip" Then
        btnInput.Click
        Exit For
    End If
Next btnInput

End Sub

Function GetIE() As Object
  On Error Resume Next
  Set GetIE = CreateObject("InternetExplorer.Application")
End Function