CODEWITHSUNDEEP
vbaexcelweb-scraping
Mike P.
Mike P.

Reputation: 61

click a button in vba

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

Upvotes: 1

Views: 1791

Answers (2)

QHarr
QHarr

Reputation: 84465

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:

query

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.

Upvotes: 3

Tim Williams
Tim Williams

Reputation: 166755

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.

Upvotes: 2

Related Questions