VBA to log in to web site and scrape

[Shankboy]

I am new to the forum. Please excuse any errors on my part...I am trying to log into a web site, post a number into the entry field, click on a lookup button and then retrieve information off of the new page. I know excel quite well and VBA less so. I have never attempted this sort of thing using Excel, but have found code out there that works for other sites. Unfortunately it does not seem to for mine. If have posted the code below - any help would be appreciated. There is some lines in there that are commented out, mostly trial and error stuff that failed. Thanks. There could be some sort of timeout problem?

Sub SearchBot()

'dimension (declare or set aside memory for) our variables
Dim objIE As InternetExplorer 'special object variable representing the IE browser
Dim aEle As HTMLLinkElement 'special object variable for an <a> (link) element
Dim y As Integer 'integer variable we'll use as a counter
Dim result As String 'string variable that will hold our result link


'initiating a new instance of Internet Explorer and asigning it to objIE
Set objIE = New InternetExplorer


'make IE browser visible (False would allow IE to run in the background)
objIE.Visible = True
objIE.Top = 0
objIE.Left = 50
objIE.Height = 1500
objIE.Width = 2400

'navigate IE to this web page (a pretty neat search engine really)
'objIE.navigate "Club Login - The Club at Mediterra"
objIE.navigate "GHIN.com Handicap Lookup"
'objIE.navigate "DuckDuckGo — Privacy, simplified."


'wait here a few seconds while the browser is busy
'Do While objIE.Busy = True Or objIE.readyState <> 4: DoEvents: Loop

Do While (objIE.Busy Or objIE.readyState = 4)
DoEvents
Loop

objIE.document.getElementsByName ("ctl00_bodyMP_tcLookupModes_tpSingle_tbGHIN")
'Set ghin_no = objIE.document.getElementById("ctl00_bodyMP_tcLookupModes_tpSingle_tbGHIN")



'Debug.Print "Here"; result

'Application.Wait (Now + TimeValue("0:00:03"))

'objIE.document.getElementById("search_form_input_homepage").Value = _
'Sheets("Sheet1").Range("A2").Value & " in " & Sheets("Sheet1").Range("C1").Value


objIE.document.getElementById("ctl00_bodyMP_tcLookupModes_tpSingle_tbGHIN").Value = _
Sheets("Sheet2").Range("B1").Value

'click the 'go' button
objIE.document.getElementById("ctl00$bodyMP$tcLookupModes$tpSingle$btnSubmit1").Click

 

[QuietRiot]

That site uses iframes which can get ugly, so it's better to use their widgets.ghin.com for the url:

Sub SearchBot()
    Dim URL As String
    Dim GHIN As String
    Dim Player As String, Club As String, Handicap As String
    Dim EffectiveDate As String, LowHI As String
    
    Cells.ClearContents
       
    GHIN = "0001123"
    URL = "https://widgets.ghin.com/HandicapLookupEntry.aspx"
    
    Set IE = CreateObject("internetexplorer.application")
    
    IE.Visible = False
    IE.navigate URL
    
    Do
        DoEvents
    Loop Until IE.readystate = 4
       
    ' Add GHIN #
    IE.document.querySelector("input[id*= ctl00_bodyMP_tcLookupModes_tpSingle_tbGHIN]").Value = GHIN
    ' Click the lookup button
    IE.document.querySelector("input[id*= ctl00_bodyMP_tcLookupModes_tpSingle_btnSubmit1]").Click
    
    Application.Wait (Now + TimeValue("0:00:02"))
    
    ' Scrape Data
    Player = IE.document.getElementById("ctl00_bodyMP_lblName").innerText
    Club = Trim(IE.document.getElementsByClassName("ClubGridClub")(0).innerText)
    Handicap = Trim(IE.document.getElementsByClassName("ClubGridHandicapIndex")(0).innerText)
    EffectiveDate = Trim(IE.document.getElementsByClassName("ClubGridEffectiveDate")(0).innerText)
    LowHI = Trim(IE.document.getElementsByClassName("ClubGridLowHI")(0).innerText)
    
    ' Post to worksheet
    Range("A1").Value = Player
    Range("A2").Value = Club
    Range("B2").Value = Handicap
    Range("C2").Value = EffectiveDate
    Range("D2").Value = LowHI
    
    ' Bye
    IE.Quit
    Set IE = Nothing

End Sub

 

[Shankboy]

That site uses iframes which can get ugly, so it's better to use their widgets.ghin.com for the url:

Sub SearchBot()
    Dim URL As String
    Dim GHIN As String
    Dim Player As String, Club As String, Handicap As String
    Dim EffectiveDate As String, LowHI As String
   
    Cells.ClearContents
      
    GHIN = "0001123"
    URL = "https://widgets.ghin.com/HandicapLookupEntry.aspx"
   
    Set IE = CreateObject("internetexplorer.application")
   
    IE.Visible = False
    IE.navigate URL
   
    Do
        DoEvents
    Loop Until IE.readystate = 4
      
    ' Add GHIN #
    IE.document.querySelector("input[id*= ctl00_bodyMP_tcLookupModes_tpSingle_tbGHIN]").Value = GHIN
    ' Click the lookup button
    IE.document.querySelector("input[id*= ctl00_bodyMP_tcLookupModes_tpSingle_btnSubmit1]").Click
   
    Application.Wait (Now + TimeValue("0:00:02"))
   
    ' Scrape Data
    Player = IE.document.getElementById("ctl00_bodyMP_lblName").innerText
    Club = Trim(IE.document.getElementsByClassName("ClubGridClub")(0).innerText)
    Handicap = Trim(IE.document.getElementsByClassName("ClubGridHandicapIndex")(0).innerText)
    EffectiveDate = Trim(IE.document.getElementsByClassName("ClubGridEffectiveDate")(0).innerText)
    LowHI = Trim(IE.document.getElementsByClassName("ClubGridLowHI")(0).innerText)
   
    ' Post to worksheet
    Range("A1").Value = Player
    Range("A2").Value = Club
    Range("B2").Value = Handicap
    Range("C2").Value = EffectiveDate
    Range("D2").Value = LowHI
   
    ' Bye
    IE.Quit
    Set IE = Nothing

End Sub

I need to play around with it, but it looks awesome. Thanks so much for your help. I have a version that runs in Python and have navigated iframes. I knew there was some kind of timing issue, but did not put it together in VBA. Thanks again.

 

[Shankboy]

I need to play around with it, but it looks awesome. Thanks so much for your help. I have a version that runs in Python and have navigated iframes. I knew there was some kind of timing issue, but did not put it together in VBA. Thanks again.

One more question - the website shuts down (generates a 404 error) if you make too many inquiries. I need to lookup as many as 100 people and i had to store my results, shut down and run later to get around this problem. Is there something clever i could do to avoid this situation?

 

[QuietRiot]

One more question - the website shuts down (generates a 404 error) if you make too many inquiries. I need to lookup as many as 100 people and i had to store my results, shut down and run later to get around this problem. Is there something clever i could do to avoid this situation?

I've dealt with it in Java and Python and for that I had to include a check of status 429. Then I added a wait and loop until status 200 to continue. You could definitely increase the wait after each lookup here, but it's hard to say.