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
– Tim Williams, Commented Aug 3, 2018 at 19:14
@Qharr how am I suppose to post the html when the character limit wont allow me too? — Mike P.
– Mike P., Commented Aug 3, 2018 at 20:29
@ 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.
– Mike P., Commented Aug 3, 2018 at 20:40
imgur.com/a/QGVIdeJ here is the html code, I'm trying to click the "reports" button — Mike P.
– Mike P., Commented Aug 3, 2018 at 20:44

QHarr · Accepted Answer · 2018-08-04 18:31:59Z

3

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.

edited Aug 4, 2018 at 18:31

answered Aug 3, 2018 at 19:41

QHarr

84.5k14 gold badges58 silver badges105 bronze badges

Sign up to request clarification or add additional context in comments.

8 Comments

Mike P. Over a year ago

This worked to allow me to click the next button thanks! "HTMLDoc.getElementsByClassName("menuitem")(1).Click" but now I have 3 links to go to the next page and they are the same "class" name

Mike P. Over a year ago

please disregard my previous message. - I'm still having an issue, when I press F8 (Step into ) and go line by line through the code, I can get to where I want to go, but when I press F5 (Run/Continue) The browser that opens at the report click doesn't continue. I tried to save the code but still nothing. Let me include the updated code

QHarr Over a year ago

So the button is clicked but there are problems with the extract data part?

Mike P. Over a year ago

Sorry QHarr, Let me try to be more specific. The button "reports" isn't clicked when I click F5 (Run Sub/userform), I thought testing by pressing F8 through the code would be useful as it works this way but when I click F5 the macro stops at the reports click page. I've included my updated code. Any reason for the difference? so for example, the code stops after HTMLDoc.forms(0).submit when I click f5, it doesn't continue compiling the rest of the code.

Mike P. Over a year ago

I took out the on error code and now it works when I click F5 now.

|

Tim Williams · Accepted Answer · 2018-08-03 19:15:25Z

2

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

is the same as

If MyHTML_Element.Type = "submit" Then 
    MyHTML_Element.Click
End If
Exit For

...which is probably not what you wanted to do

"Reports" doesn't seem like a valid element type, so maybe you should post some of your HTML.

answered Aug 3, 2018 at 19:15

Tim Williams

169k8 gold badges104 silver badges143 bronze badges

Collectives™ on Stack Overflow

click a button in vba

2 Answers 2

After each Click|Submit:

8 Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

After each Click|Submit:

8 Comments

Comments

Related