none
VBA Post to URL And read Text HTML RRS feed

  • Question

  • Greetings, 

    I'm trialing a youtube VBA Module, and not getting the results.  I'm attempting to post a URL, which gets a Text HTML return.

    Then reading the text with a split at the response text.  

    As follows:


    Public Function GetDistanceURL(urlk As String) As Double
        Dim url, Str As String
        Dim WinHttp As Object
        On Error Resume Next
    'cccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccc
        'Application.ScreenUpdating = False
         With CreateObject("WinHttp.WinHttpRequest.5.1")
            'cccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccc
            'cccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccc
            'cccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccc
            'cccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccc
                   
                .Open "GET", urlk, False
                .send
               
                'cccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccc
                '   during With, .responseText is processed as a text.
                '       strResponseText = WinHttpRequest.responseText
                'cccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccc
               
                lineS = Split(.responseText, vbLf)
                    For K = 25 To UBound(lineS)
                        If Trim(lineS(K)) = """distance"" : {" Then
                            Exit For
                        End If
                    Next K
                   
                GetDistanceURL = lineS(K + 1)
                Application.Wait (Now() + TimeValue("0:00:05"))
        End With
        'Application.ScreenUpdating = True
    End Function


    I have contrived the Tools/References in VBA to include WinHTTP5.1 Services.

    I am using my API Key from google to get the distance reading at the URL.

    I believe I am not getting Excel to Access the URL.  Anyone see a reason?

    What are Microsoft Reward Points?


    RO

    Sunday, April 28, 2019 6:28 PM

Answers

  • I've given up on Microsoft Internet Controls (MSIC).  Either through IE 11 updates or webpage complexity MSIC no longer works.  If you want to control a browser use Selenium.   C# supports Selenium.  Starting with W10 1809 the Selenium Driver is built into W10.  Someone did a DLL for VBA called SeleniumBasic.  It no longer works with FireFox and Edge.  It still works with Chrome if you update chrome driver.
    • Marked as answer by RefugioM Tuesday, April 30, 2019 9:58 PM
    Monday, April 29, 2019 2:01 PM

All replies

  • Greetings, I've continued to find another source where the Internet Controls are used through the VBA Tools/References.  However, I get the glitch, when using JSON, and this is beyond me.  Being somewhat averse to changing my browser, I'd hope to get it to open the website with JSON into Edge or Internet Explorer 11 as on this computer.  I've adjusted my registry with these classes by a recommendation : 

    reg add "HKEY_CLASSES_ROOT\MIME\Database\Content Type\application/json" /t REG_SZ /d "{25336920-03F9-11cf-8FD0-00AA00686F13}"; reg add "HKEY_CLASSES_ROOT\MIME\Database\Content Type\application/json" /v Encoding /t REG_DWORD /d 0x08000000

    However, there does not seem to be any direction cue to access Edge.  At IE 11, I get the following response: (see images). Which is a continuous loop, returning to "Do you want to save" and so on.  Adding the reg as above, is it for Internet Explorer 11?  I'm going to trial FireFox to see if it operates.


    RO

    Monday, April 29, 2019 5:42 AM
  • I've given up on Microsoft Internet Controls (MSIC).  Either through IE 11 updates or webpage complexity MSIC no longer works.  If you want to control a browser use Selenium.   C# supports Selenium.  Starting with W10 1809 the Selenium Driver is built into W10.  Someone did a DLL for VBA called SeleniumBasic.  It no longer works with FireFox and Edge.  It still works with Chrome if you update chrome driver.
    • Marked as answer by RefugioM Tuesday, April 30, 2019 9:58 PM
    Monday, April 29, 2019 2:01 PM
  • Thanks Mr. Mogul,

    Your answer has opened up a network.  Now I understand XML versus HTML, slightly.

    With XML, vs. JSON, I can get the read.

    Continuing …  


    RO

    Tuesday, April 30, 2019 9:54 PM
  • Continuing …

    more of the ongoing approach is apparent here: 

    https://social.msdn.microsoft.com/Forums/sqlserver/en-US/a7445768-4e81-4c90-99ad-8173005f2d0c/http-requests-in-vba-winhttp-or-msxml2-or-


    RO

    Tuesday, April 30, 2019 9:57 PM