Excel VBA to Login to Website and Grab TR & TD Elements

Question

I am testing the code below. I think this is very close, but I can't seem to login to the site for some reason.

Sub Website_Login_Test()

Dim oHTML_Element As IHTMLElement
Dim sURL As String

On Error GoTo Err_Clear
sURL = "https://login.my_site_here.jsp?"
Set oBrowser = New InternetExplorer
oBrowser.Silent = True
oBrowser.timeout = 60
oBrowser.navigate sURL
oBrowser.Visible = True

Do
' Wait till the Browser is loaded
Loop Until oBrowser.readyState = READYSTATE_COMPLETE

Set HTMLDoc = oBrowser.document


For Each oHTML_Element In HTMLDoc
    Debug.Print oHTML_Element
Next

HTMLDoc.all.UserId.Value = "my_id"
HTMLDoc.all.Password.Value = "my_pass"

For Each oHTML_Element In HTMLDoc.getElementsByTagName("Button")
Debug.Print oHTML_Element.Name
'oHTML_Element.Click: Exit For
'Debug.Print oHTML_Element.Type
'If oHTML_Element.Type = "submit" Then oHTML_Element.Click: Exit For
Next

' oBrowser.Refresh ' Refresh If Needed
Err_Clear:
If Err <> 0 Then
Err.Clear
Resume Next
End If

Call Test
End Sub

Sub Test()

Dim ie As Object, i As Long, strText As String

Dim doc As Object, hTable As Object, hBody As Object, hTR As Object, hTD As Object
Dim tb As Object, bb As Object, tr As Object, td As Object

Dim y As Long, z As Long, wb As Excel.Workbook, ws As Excel.Worksheet

     Set wb = Excel.ActiveWorkbook
     Set ws = wb.ActiveSheet

     Set ie = CreateObject("InternetExplorer.Application")
     ie.Visible = True

      y = 1   'Column A in Excel
      z = 1   'Row 1 in Excel

     ie.navigate "https://after_login_move_to_page_for_scraping.jsp"

     Do While ie.busy: DoEvents: Loop
     Do While ie.readyState <> 4: DoEvents: Loop

     Set doc = ie.document
     Set hTable = doc.getElementsByTagName("table")


     For Each tb In hTable

        Set hBody = tb.getElementsByTagName("tbody")
        For Each bb In hBody

            Set hTR = bb.getElementsByTagName("tr")
            For Each tr In hTR


                 Set hTD = tr.getElementsByTagName("td")
                 y = 1 ' Resets back to column A
                 For Each td In hTD
                   ws.Cells(z, y).Value = td.innertext
                   y = y + 1
                 Next td
                 DoEvents
                 z = z + 1
            Next tr
            Exit For
        Next bb
    Exit For
  Next tb

End Sub

I can't login to the site, so I can't do the scraping, but I think the code is pretty close. Here is the HTML for the id object, the password object and the button object. What am I doing wrong?

please don't post pics of code – QHarr Nov 02 '20 at 16:57 — QHarr, Nov 02 '20 at 16:57

score 1 · Accepted Answer · answered Nov 02 '20 at 16:21

1

I think you must trigger the keypress event of the input fields. If there are other events you must trigger, have a look here, how you can find them:
Automate IE via Excel to fill in a dropdown and continue

Sub WebsiteLogin()

Const url As String = "https://login.my_site_here.jsp"
Const userName As String = "Here Your LogIn Name"
Const passWord As String = "Here Your Password"

Dim ie As Object
Dim htmlDoc As Object
Dim nodeInputUserName As Object
Dim nodeInputPassWord As Object

  'Initialize Internet Explorer, set visibility,
  'call URL and wait until page is fully loaded
  Set ie = CreateObject("InternetExplorer.Application")
  ie.Visible = True
  ie.navigate url
  Do Until ie.readyState = 4: DoEvents: Loop
  Set htmlDoc = ie.document
  
  'Set the log in name
  Set nodeInputUserName = htmlDoc.getElementById("USERID")
  nodeInputUserName.Value = userName
  Call TriggerEvent(htmlDoc, nodeInputUserName, "onkeypress")
  
  'Set the password
  Set nodeInputPassWord = htmlDoc.getElementById("PASSWORD")
  nodeInputPassWord.Value = passWord
  Call TriggerEvent(htmlDoc, nodeInputPassWord, "onkeypress")
  
  'Click submit button
  htmlDoc.querySelector("a[role='button']").Click
  
End Sub

This is the procedure to trigger events:

Private Sub TriggerEvent(htmlDocument As Object, htmlElementWithEvent As Object, eventType As String)

  Dim theEvent As Object

  htmlElementWithEvent.Focus
  Set theEvent = htmlDocument.createEvent("HTMLEvents")
  theEvent.initEvent eventType, True, False
  htmlElementWithEvent.dispatchEvent theEvent
End Sub

answered Nov 02 '20 at 16:21

Zwenn

2,147
2
8
14

So, when I get to this line: 'Do Until ie.readyState = 4' I get this error: 'run time error 462: the remote server machine does not exist or is unavailable' It seems very weird to get that right at the beginning of the process. I've done these kinds of things before and I never had this kind of issue. Any thoughts? Thanks. – ASH Nov 02 '20 at 17:22
If the site is in your intranet, try this: `Set ie = GetObject("new:{D5E8041D-920F-45e9-B8FB-B1DEB82C6E5E}")` instead of `Set ie = CreateObject("InternetExplorer.Application")` (more infos here: https://learn.microsoft.com/de-de/archive/blogs/ieinternals/default-integrity-level-and-automation) Another aproach is to use early binding like you do in your question. I use late binding with `As Object` for variables instead of e.g. `As InternetExplorer`, sorry. – Zwenn Nov 02 '20 at 18:07
It's working! Thanks a lot!! Last question. Is this TriggerEvent a new thing now? I've done some screen scraping exercises in the past. I've never come across a TriggerEvent before. – ASH Nov 02 '20 at 18:15
HTML events are not new. Click a button, moseover or mousedown are also events. What changed is how to trigger an event you can't use like `.click`. In the past that works with `fireevent` but with more modern techniques `fireevent` is outdated. The new way is to set an event and dispatch it. The `Sub()` I named `TriggerEvent()` bundels all you need to trigger any events. It's a comfortable way. But it wasn't my idea. The code is from a German Excel forum from someone I learned a lot about web scraping. – Zwenn Nov 02 '20 at 19:12
1

Like I see when I look for links for you now, `initEvent` is also outdated, but it works with the (also very old) IE (https://developer.mozilla.org/en-US/docs/Web/API/Event/initEvent) I don't know if it is still important for VBA if you don't use it with IE. But the current method for handling events is described here: https://developer.mozilla.org/en-US/docs/Web/Guide/Events/Creating_and_triggering_events – Zwenn Nov 02 '20 at 19:34
Ok. Thanks again. – ASH Nov 03 '20 at 14:33

Excel VBA to Login to Website and Grab TR & TD Elements

1 Answers1

Linked