Click button or execute JavaScript function with VBA

Question

I'm trying to get my VBA routine to interact with a page that uses JavaScript, and have been successful in the past. After a recent update, it no longer works. At this point I need to either A) programmatically click a button or B) execute the function the button calls. The tricky part is that the button has no declared name. There are others on the sheet declared in the source code with names, and I can interact with them just fine. Here's the html code from the page source:

<input type="button" class="buttonForm" value='Run Report' onclick="exportData(workOrderSearchForm)"/>

As you can see, it does not declare a name for the button. In the past, the following has worked:

Set ie = CreateObject("InternetExplorer.application")
ie.document.all(79).Click

With "79" being the index of the item number. It now appears that the button has been changed to item "81", but simply putting in:

ie.document.all(81).Click

is not working for some reason. I know the function I want to execute: exportData(workOrderSearchForm), but don't know how to do so outside of using the "click" method.

I've looked for some decent documentation regarding the IE application object, but can't seem to find a good source. Is there a way to execute the function?

score 11 · Accepted Answer · edited Jan 09 '12 at 11:50

11

Try this:

Dim CurrentWindow As HTMLWindowProxy: Set CurrentWindow = ie.Document.parentWindow
Call CurrentWindow.execScript("exportData(workOrderSearchForm)")

But first you will need to include Microsoft HTML Object Library under References (Tools>References)

edited Jan 09 '12 at 11:50

stealthyninja

10,343
11
51
59

answered Jan 08 '11 at 00:09

Arun

126
1
3

score 2 · Answer 2 · answered Jan 08 '11 at 12:11

You can cycle thru all input tags and identify the relevant one by inspecting "the most identifying" attribute (id, name, type, innerHTML, ...whatever). Here's a Sub() I use in an Excel sheet which automatically logs on to a web site

Sub FormAction(Doc As MSHTML.HTMLDocument, ByVal Tag As String, ByVal Attrib As String, ByVal Match As String, ByVal Action As String)
Dim ECol As MSHTML.IHTMLElementCollection
Dim IFld As MSHTML.IHTMLElement
Dim Tmp As String

    Set ECol = Doc.getElementsByTagName(Tag)
    For Each IFld In ECol                                         ' cycle thru all <[tag]> elements
        If VarType(IFld.getAttribute(Attrib)) <> vbNull Then      ' does it contain the attribute
            If Left(IFld.getAttribute(Attrib), Len(Match)) = Match Then
                If Action = "/C/" Then
                    IFld.Click
                Else
                    IFld.setAttribute "value", Action
                End If
                Exit Sub
            End If
        End If
    Next
End Sub

The function takes following parameters:

Doc .... the HTML DOM object
Tag .... the tag you want to work on (usually input, image, a, ...)
Attrib .... the attribute whose value you want to match
Match .... the matching value for the attribute
Action ..... if "/C/" the .Click() action is performed on the matched tag, else the action value is put into the value attribute of the tag

If you want to hook on any attribute containing JavaScript code (like "onclick") don't forget that the code you see in the HTML source is embeded in an anonymous function, like

function anonymous()
{
onclick="exportData(workOrderSearchForm)";
}

You need to consider this by using an Instr() function or similar, if you want to hook on e.g. "exportData(workOrder" .

Also very important: give enough time for the page to navigate to and for the DOM object to be loaded. I notice in my company that once pages are changed the loading times sometimes raise drastically.

I have good success by this:

Sub MyMainSub()
Dim Browser As SHDocVw.InternetExplorer
Dim HTMLDoc As MSHTML.HTMLDocument
' my other Dim's

    ' start browser
    Set Browser = New SHDocVw.InternetExplorer
    Browser.navigate "http://www.whatever.com"
    WaitForBrowser Browser, 5           ' wait for browser, but not more than 5 sec

    ' gain control over DOM object
    Set HTMLDoc = Browser.document
    WaitForBrowser Browser, 5           ' wait for browser, but not more than 5 sec

    ' do actions
    ' FormAction HTMLDoc, w, x, y, z

End Sub


Sub WaitForBrowser(Browser As SHDocVw.InternetExplorer, Optional TimeOut As Single = 10)
Dim MyTime As Single

    MyTime = Timer                   ' seconds since midnight
    Do While Browser.Busy Or (Timer <= MyTime + TimeOut)
        DoEvents                     ' go do something else
    Loop

    If Browser.Busy Then
        MsgBox "I waited for " & Timer - MyTime & " seconds, but browser still busy" & vbCrLf & _
               "exititing Login sequence now"
        End
    End If
End Sub

Hope this is inspiring enough for you to create your solution.

Good luck MikeD

You can use this with Attrib set to "outerHTML" and search for the exact HTML tag from the object, like for instance `` — cyberponk, Mar 12 '19 at 04:26

score 0 · Answer 3 · answered Jul 14 '10 at 14:58

0

Like this:

ie.window.exportData(ie.document.forms.workOrderSearchForm)

answered Jul 14 '10 at 14:58

SLaks

868,454
176
1,908
1,964

Unfortunately it says that's not a supported method or property. It also spaces the code as such "ie.Window.exportData (ie.Document.forms.workOrderSearchForm)" once I put it in. – Michael Jul 14 '10 at 15:24
Try `ie.window.exportData ie.window.workOrderSearchForm` or `ie.window.eval "exportData(workOrderSearchForm)"` – SLaks Jul 14 '10 at 15:25
Neither of those seem to do the trick, either. They produce the same error. – Michael Jul 14 '10 at 15:31

Click button or execute JavaScript function with VBA

3 Answers3

Linked