click a button in vba

Question

I was able to create my vba code to login to a password protected website but now i'm stuck on how to be able to automate the click through to where I need to extract my data. I'm not able to share the website but here is the line of html that I'm trying to click. It's a "Reports" button but I cant

This is the html code that I would like to click the button

    <a href="gso_list_etrader_reports?vsCurrUser=2753" 
     class="menuitem">Reports</a>

This is the code for the website and login/password and click to login which is working flawlessly up to this point, next step is to click reports and extract the data through web query in excel. Any help is greatly appreciated. Thanks!

Dim HMTLDoc As HTMLDocument
Dim MyBrowser As InternetExplorer
Sub daily()
'
' daily Macro
'

 Dim MyHTML_Element As IHTMLElement
    Dim MYURL As String
    On Error GoTo Err_Clear

    '  website
    MYURL = ""
    Set MyBrowser = New InternetExplorer
    MyBrowser.Silent = True
    MyBrowser.navigate MYURL
    MyBrowser.Visible = True
    Do
    Loop Until MyBrowser.readyState = READYSTATE_COMPLETE
    Set HTMLDoc = MyBrowser.document
    ' user login and password
    HTMLDoc.all.user_login.Value = ***
    HTMLDoc.all.user_password.Value = ***
    For Each MyHTML_Element In HTMLDoc.getElementsbyTagName("input")
    'click submit to login
    If MyHTML_Element.Type = "submit" Then MyHTML_Element.Click: Exit For
    Next

 'gives debug error***   If MyHTML_Element.Type = "Reports" Then MyHTML_Element.Click: Exit For
    Next


Err_Clear:
    If Err <> 0 Then
    Err.Clear
    Resume Next
    End If


End Sub

Requested html of the site I am trying to click, I want the macro to click the reports button.

Trader Home | Transaction | Reports

New code that wont work when I press F5 but will work if I press F8 through the code:

Dim HMTLDoc As HTMLDocument Dim MyBrowser As InternetExplorer Sub Delmarva_daily() ' ' Delmarva_daily Macro '

Dim MyHTML_Element As IHTMLElement Dim MYURL As String On Error GoTo Err_Clear

' website
MYURL = ***
Set MyBrowser = New InternetExplorer
MyBrowser.Silent = True
MyBrowser.navigate MYURL
MyBrowser.Visible = True
Do
Loop Until MyBrowser.readyState = READYSTATE_COMPLETE
Set HTMLDoc = MyBrowser.document
' user login and password
HTMLDoc.all.user_login.Value = ***
HTMLDoc.all.user_password.Value = ***
' click submit
For Each MyHTML_Element In HTMLDoc.getElementsbyTagName("input")

If MyHTML_Element.Type = "submit" Then MyHTML_Element.Click: Exit For
Next
' click reports
HTMLDoc.getElementsByClassName("menuitem")(1).Click
While MyBrowser.Busy Or MyBrowser.readyState < 4: DoEvents: Wend
'click Billing Analysis Report (Industrial)
HTMLDoc.getElementsByClassName("firstlink")(0).Click
While MyBrowser.Busy Or MyBrowser.readyState < 4: DoEvents: Wend
For Each MyHTML_Element In HTMLDoc.getElementsbyTagName("input")

If MyHTML_Element.Type = "submit" Then MyHTML_Element.Click: Exit For
Next

Err_Clear: If Err <> 0 Then Err.Clear Resume Next End If

End Sub

You have two Next and only one For Each. Also those one-liner If's may not be doing what you expect. — Tim Williams
@Qharr how am I suppose to post the html when the character limit wont allow me too? — Mike P.
@ Qharr I'm receiving an error "Your post appears to contain code that is not properly formatted as code. Please indent all code by 4 spaces using the code toolbar button or the ctrl +k keyboard shortcut. ? I'm not sure what to do here when I copy and paste the HTML — Mike P.
imgur.com/a/QGVIdeJ here is the html code, I'm trying to click the "reports" button — Mike P.

QHarr QHarr · Accepted Answer · 2018-08-03T19:41:07

Reports button:

You can try the following CSS selector to target the element of interest:

HTLMDoc.querySelector("a[href='gso_list_etrader_reports?vsCurrUser=2753']").Click

Or the more traditional element by classname selection:

HTMLDoc.getElementsByClassName("menuitem")(1).Click

Or by tag (if there are indeed only 2 a tags!)

HTMLDoc.getElementsByTagName("a")(1).Click

The CSS selector:

a[href='gso_list_etrader_reports?vsCurrUser=2753']

is looking for an element with an a tag having attribute href whose value is gso_list_etrader_reports?vsCurrUser=2753.

CSS query on your HTML sample:

Submit button:

For the rest of your code: You can use the following to avoid a loop when clicking the input button depending on how many submit input buttons you have.

HTLMDoc.querySelector("input[type=submit]").Click

You may also be able to use

HTMLDoc.forms(0).submit

After each Click|Submit:

You want While myBrowser.Busy Or myBrowser.readyState < 4: DoEvents: Wend after each click|submit event, to allow time for page events to occur; and to reduce the chances of object not found due to trying to select an element before it is available on the page.

click a button in vba

2 Answers

After each Click|Submit: