Scraping the web

[SaskiaHe]

Who can help me, please?

I'm getting crazy as the code underneath sometimes works and sometimes doesn't. I haven't got a clue when it is working and when it doensn't ...

Function pbnInternet(Condition)
Dim objIE As Object, ele As Object
Dim pos As Integer
    Set objIE = CreateObject("InternetExplorer.Application")
    objIE.Visible = True
    objIE.navigate "http://www.bridgebase.com/tools/dealer/dealer.php"
    
    Do While objIE.Busy = True Or objIE.readyState <> 4: DoEvents: Loop
    
    objIE.document.all("i").Value = Condition
    
    For Each ele In objIE.document.getElementsByTagName("input")
        Debug.Print ele.Value
        If ele.Value = "Deal" Then
            ele.Click
            Do While objIE.Busy = True Or objIE.readyState <> 4: DoEvents: Loop
            pbnInternet = objIE.document.body.innerHTML
        End If
    Next ele
    
    objIE.Quit
    
    pos = InStr(1, pbnInternet, "")

    If pos > 0 Then pbnInternet = Mid(pbnInternet, pos + 5)

End Function

It lookslike that VBA is loosing its focus sometimes. It is not getting the right innerhtml and it is not closing the IE-window at objie.quit. But sometimes it just does... I have been playing around with the sleep-function as i thought it might have to do with not being ready yet with downloading the informatiion. However, it doesnt't make any difference.



Your help is very much appreciated.



Saskia

 

[John_w]

Are you running IE11? If so, you've come across a problem/feature I've seen with some other websites when automating IE11.

For unknown reasons, when VBA submits a form, IE creates a second IE11 window (not visible to the user) which contains the web page with the HTML form populated but no results, whilst the original visible IE11 window contains the web page response with the results. The problem is that the VBA InternetExplorer object variable references this second IE11 window and therefore it thinks that no results are available to extract.

In your case this happens when your code clicks the 'Deal' button. Your objIE.Quit closes the second (invisible) IE window, hence why the visible window doesn't close. The simple answer is to hide the IE window (objIE.Visible = False) before clicking the 'Deal' button, and then show it if necessary (objIE.Visible = True) after the page has loaded, although you don't need to do this because you Quit it anyway. I don't know why hiding the window fixes this problem.

Here is a variation of your code which clicks 'Deal' and extracts the results into an Excel cell.

Option Explicit

Public Sub IE_Bridgebase_Deal()

    Dim condition As String
    
    condition = "produce 5" & vbNewLine & _
                "condition shape(north, any 4333 + any 4423) and hcp(north)>=13" & vbNewLine & _
                "action printoneline"

    ActiveSheet.Range("A1:B1").ClearContents
    ActiveSheet.Range("B1").Value = Get_Deal_Result3(condition)
    ActiveSheet.Range("A1").Value = Now

End Sub

Private Function Get_Deal_Result3(Condition As String) As String

    Static IE As Object 'InternetExplorer
    Dim HTMLdoc As Object 'HTMLDocument
    Dim inputTextArea As Object 'HTMLTextAreaElement
    Dim dealButton As Object 'HTMLInputElement
    Dim preElement As Object 'HTMLGenericElement
    Dim URL As String
    
    URL = "http://www.bridgebase.com/tools/dealer/dealer.php"
    
    If IE Is Nothing Then Set IE = CreateObject("InternetExplorer.Application") 'New InternetExplorer
    
    With IE
        .Visible = True
        .Navigate URL
        While .Busy Or .ReadyState <> 4: DoEvents: Wend
        Set HTMLdoc = .Document
    End With
    
    Set inputTextArea = HTMLdoc.getElementsByTagName("TEXTAREA")(0)
    inputTextArea.Value = Condition
    
    'With IE11, clicking the Deal button creates a second IE11 window (not visible to the user) which contains
    'the web page with the HTML form populated but no results, whilst the original visible IE11 window contains the web page response with the results.
    'However the VBA IE object somehow references this second IE11 window, not the original window containing the response.  Therefore
    'an error ('Object variable or With block variable not set') will occur on the Replace line below because the preElement variable is Nothing.
    'We can make the VBA IE object reference the original IE11 window containing the web page response by setting IE.Visible = False before
    'clicking the Deal button and then setting IE.Visible = True after the page has loaded.
    
    IE.Visible = False
    
    Set dealButton = HTMLdoc.getElementsByName("submit")(0)
    dealButton.Click
        
    With IE
        While .Busy Or .ReadyState <> 4: DoEvents: Wend
        While .Document.ReadyState <> "complete": DoEvents: Wend
        .Visible = True  'show IE window again
    End With

    'Result is in < pre > element
    '< pre >n T82.974.AKT9.AQ8 e 6.J2.QJ8432.KJ42 s AJ97.AKQ6.7.T765 w KQ543.T853.65.93

    Set preElement = HTMLdoc.getElementsByTagName("PRE")(0)
    Get_Deal_Result3 = Replace(preElement.innerText, vbCrLf, vbLf)
        
End Function

 

[SaskiaHe]

You made my day. Thank you very much!!