Why does my Excel VBA macro works for only one of two similar web sites to lookup golfer handicaps and capture results?

Question

I think the problem has to do with iframes. I got Selenium to record and run the process on both sites. The only difference I see in the resulting Selenium code is that on the GHIN site, Selenium runs a Command: "select frame" Target: "index=0". I believe this gets to the iframe(0) frame but I have not found the VBA code to penetrate access to the element id's I need. More on this later. A less likely possibility is that the use of AJAX on the GHIN site must be handled differently? The iframe and AJAX were the most obvious differences to me on inspection of the HTML. Both sites appear to use JSCRIPT, Tables and Forms.

I lookup and capture Golf Handicap updates for a list of about 50 golfers in an Excel spreadsheet. Revised handicaps are issued twice a month. My golfers are split between 2 handicap service sites:

https://www.golfhandicapnetwork.com/DefaultLookup.aspx (Handicomp) http://www.ghin.com/lookup.aspx (GHIN)

I programmed an Excel Macro that gets the job done for Handicomp but can't get the same approach to work on the GHIN website. Here's the VBA Macro for GHIN using a suggested approach I tried to access the iframe elements (Error 424 Object required). I annotate the code with remarks to show the replacement code for the very similar Handicomp handicap service lookup web site. I exclude the code to get the lookup criteria from the spreadsheet, extract the lookup results and enter them in the spreadsheet to minimize the code and focus on my inability to getElementById on the GHIN website within an iframe. I list more specifics about my failed adjustments to gain access to the iframe elements after the CODE. This sub just looks up one common name (Smith) in the state of Arizona. I also leave InternetExplorer open to view the lookup results.

Sub HCLookupGHIN()
  Dim objIE As InternetExplorer 
  Dim doc As HTMLDocument
  Set objIE = CreateObject("InternetExplorer.application")
With objIE
  .Visible = True
         .navigate "http://www.ghin.com/lookup.aspx" '1
  'Wait for loading
Do While objIE.Busy Or objIE.readyState <> 4: DoEvents: Loop
End With
Set doc = objIE.document
' for GHIN need to click the Name & State lookup tab 
        doc.frames(0).document.getElementById("__tab_ctl00_bodyMP_tcLookupModes_tpNameState").Click  '2 Err 424 Object Required (Also Run-time error Access is denied)
' Find and Select State from Drop Down List
        doc.frames(0).document.getElementById("ctl00_CPH0_ddlState").Click  '3
        Set oState = doc.frames(0).document.getElementById("ctl00_bodyMP_tcLookupModes_tpNameState_cboState")  '4
  For i = 1 To oState.Options.Length
    If oState.Options(i).Text = State Then
      oState.selectedIndex = i
      Exit For
    End If
  Next i
        doc.frames(0).document.getElementById("ctl00_bodyMP_tcLookupModes_tpNameState_tbLastName").Value = LastN  '5
        doc.frames(0).document.getElementById("ctl00_bodyMP_tcLookupModes_tpNameState_tbFirstName").Value = FirstN  '6
        doc.frames(0).document.getElementById("ctl00_bodyMP_tcLookupModes_tpNameState_btnSubmit2").Click  '7

'Replacement code for Handicomp Sub. This code works for me. Replace annotated statements '1-'7 
'1  .navigate "https://www.golfhandicapnetwork.com/DefaultLookup.aspx"
'2    no code required for Handicomp. Name & State input available 
'3  doc.getElementById("ctl00_CPH0_ddlState").Click
'4  Set oState = doc.getElementById("ctl00_CPH0_ddlState") 
'5  doc.getElementById("ctl00_CPH0_lastNameLookupTxtBox").Value = LastN
'6  doc.getElementById("ctl00_CPH0_firstNameLookupTxtBox").Value = FirstN
'7  doc.getElementById("ctl00_CPH0_submit_lookup_nam_btn").Click 'Click submit button

End Sub

I can't enter data in the default GHIN Single Player lookup either using:

doc.getElementById("ctl00_bodyMP_tcLookupModes_tpSingle_tbGHIN").Value = "0000" (or the other frames(0) and iframe ID constructs)

So, I conclude that I just haven't found the secret passage to access the iframe. It is not just a problem clicking the Name & State lookup tab.

Nor could I get to the GHIN Name & State lookup form (which is what I really need) using: doc.getElementById("__tab_ctl00_bodyMP_tcLookupModes_tpNameState").Click

I also tried the following VBA code to getElementById in iframe (0) (statement '2) using:

doc.getElementById(ghinwidget_iFrame_0).contentDocument.getElementById("__tab_ctl00_bodyMP_tcLookupModes_tpNameState").Click [Run-time error 91 object variable or with block not set]

doc.getElementById(ghinwidget_iFrame_0).document.getElementById(__tab_ctl00_bodyMP_tcLookupModes_tpNameState).Click [Run-time error 91 object variable or with block not set]

Tried a few other similar constructs to no avail. I've spent a lot of time looking for answers. Sorry if I missed it.

These two web sites appear so similar. Help resolve my ignorance. I'm a beginner with internet automation and VBA programming, but I think I'm close to automating this handicap lookup task and putting the results into my spreadsheet. Any help will be greatly appreciated by a bunch of old golfers (especially me). Hope you have the simple answer or can suggest another approach.

Answer 1

Permission denied and same origin policy:

Your problem is due to a different src link for the iframe housing the tab you want to click. You therefore get an access denied when trying to click; I think due to same origin policy.

With IE you can get round this by directly navigating to that src link. With selenium, which I also show you how to solve with, you can switch frames to access. Also, this may be an issue in particular for IE.

---

Target src housing new document required

---

Internet Explorer:

Public Sub GetInfo2()
    Dim ie As New InternetExplorer, html As New HTMLDocument, state As String, surname As String
    state = "AZ": surname = "Smith"
    Application.ScreenUpdating = False
    With ie
        .Visible = True
        .navigate "http://162.245.224.193/Widgets/HandicapLookupEntry.aspx?widget=HandicapLookupEntry&small=0&css=default&showheader=1&showheadertext=1&showfootertext=1&TestDB=0"

        While .Busy Or .readyState < 4: DoEvents: Wend

        With .document
            .getElementById("__tab_ctl00_bodyMP_tcLookupModes_tpNameState").Click
            .querySelector("option[value=" & state & "]").Selected = True
            .getElementById("ctl00_bodyMP_tcLookupModes_tpNameState_tbLastName").innerText = surname
            .getElementById("ctl00_bodyMP_tcLookupModes_tpNameState_btnSubmit2").Click
        End With

        While .Busy Or .readyState < 4: DoEvents: Wend
        Dim flagElement As Object
        Do: DoEvents: On Error Resume Next: Set flagElement = .document.getElementById("ctl00_bodyMP_lnkGoBack"): On Error GoTo 0: Loop While flagElement Is Nothing

        Dim hTable As HTMLTable, iRow As htmltablerow, iCell As HTMLTableCell, r As Long, c As Long
        Set hTable = .document.getElementsByTagName("form")(0).getElementsByTagName("table")(1)

        With Worksheets("Sheet1")
            For Each iRow In hTable.getElementsByTagName("tr")
                r = r + 1: c = 0
                For Each iCell In iRow.getElementsByTagName("td")
                    c = c + 1
                    .Cells(r, c) = iCell.innerText
                Next
            Next
        End With
    End With
    Application.ScreenUpdating = True
End Sub

---

References required:

VBE > Tools > References >

1. Microsoft Internet Controls
2. HTML Object Library

---

Selenium Basic:

Here you go with selenium basic. After install add reference to selenium type library via tool > references.

Option Explicit
Public Sub GetInfo()
    Dim d As WebDriver
    Set d = New ChromeDriver
    Const url = "http://www.ghin.com/lookup.aspx"
    Application.ScreenUpdating = False
    With d
         '.AddArgument ("--no-sandbox")           '<== Uncomment me if dev extension error
         '.AddArgument ("--disable-extensions") '<== Uncomment me if dev extension error
        .AddArgument "--headless".AddArgument "--headless"
        .Start "Chrome"
        .get url
        .SwitchToFrame .FindElementByTag("iframe", timeout:=7000)
        .FindElementById("__tab_ctl00_bodyMP_tcLookupModes_tpNameState").Click
        .FindElementById("ctl00_bodyMP_tcLookupModes_tpNameState_cboState").AsSelect.SelectByText "Arizona"
        .FindElementById("ctl00_bodyMP_tcLookupModes_tpNameState_tbLastName").SendKeys "Smith"
        .FindElementById("ctl00_bodyMP_tcLookupModes_tpNameState_btnSubmit2").Click
        Dim e As Object
        Set e = .FindElementById("ctl00_bodyMP_lnkGoBack", timeout:=10000)
        Dim a As Object, iRow As Object, iCell As Object, r As Long, c As Long
        Set a = .FindElementByTag("form").FindElementsByTag("table")(2)
        With Worksheets("Sheet1")
            For Each iRow In a.FindElementsByTag("tr")
                r = r + 1: c = 0
                For Each iCell In iRow.FindElementsByTag("td")
                    c = c + 1
                    .Cells(R, c) = iCell.Text
                Next
            Next
        End With
        .Quit
        Application.ScreenUpdating = True
    End With
End Sub

---

And for your other site:

Option Explicit
Public Sub GetOtherInfo()
    Dim d As WebDriver
    Set d = New ChromeDriver
    Const URL = "https://www.golfhandicapnetwork.com/DefaultLookup.aspx"
    Application.ScreenUpdating = False
    With d
        .AddArgument "--headless"
        .Start "Chrome"
        .get URL
        With .FindElementByTag("form")
            .FindElementById("ctl00_CPH0_ddlState").AsSelect.SelectByText "Arizona"
            .FindElementById("ctl00_CPH0_lastNameLookupTxtBox").SendKeys "Smith"
            .FindElementById("ctl00_CPH0_submit_lookup_nam_btn").Click
        End With
        Dim a As Object, iRow As Object, iCell As Object, R As Long, c As Long
        Set a = .FindElementByCss("#ctl00_CPH0_wucDefaultLookup_pnlEntirePage table")
        Dim clipboard As Object
        Set clipboard = New MSForms.DataObject
        clipboard.SetText a.Attribute("outerHTML")
        clipboard.PutInClipboard
        ActiveSheet.Cells(1, 1).PasteSpecial
        .Quit     
    End With
    Application.ScreenUpdating = True
End Sub

---

References:

VBE> Tools > References:

1. Microsoft Forms 2.0 Object Library (automatically added if you add a Form object to your project)
2. Selenium Type Library