none
Excel VBA Web Query / Internet Explorer Version Issue RRS feed

  • Question

  • Greetings,

    System Info: Windows 7, Excel 2013, Internet Explorer 11

    I have been using the same VBA web queries in Excel for years. 

    Now the site I query requires IE9 or greater and will not work in compatibility mode.

    The site is government so I am unable to provide the link.

    The code is...

        With DataSheet.QueryTables.Add(Connection:="URL;" & MyURL, Destination:=DataSheet.Range("a1"))
            For z = 1 To .Parameters.Count
                .Parameters(z).SetParam xlConstant, "1"
            Next
            .BackgroundQuery = True
            .TablesOnlyFromHTML = False
            .Refresh BackgroundQuery:=False
            .SaveData = True
        End With
    

    The error I receive is:

    The browser you are using is not supported in this version. Please use Internet Explorer 9, Internet Explorer 10, or Internet Explorer 11.
    If you are using one of the browsers listed above, please ensure that this site is not listed in your Compatibility View Settings.

    Question:

    Is it possible to force Excel to query using >=IE9?

    Is there a known workaround for web queries that require >=IE9?

    Thank you,

    Kevin

    Thursday, April 20, 2017 3:25 PM

Answers

  • The QueryTables sends a user agent string to the webserver so the server knows how to treat the client.  You could create a webpage and using Javascript detect the string and see what it is.

     var ua = window.navigator.userAgent;

    I don't see an option to change it.  You could use MS XML 6.0 to get the data.  It has an option to set the header. 

    Sub GetYahoo()
    
        Dim url As String, YahooHtml As String
        Dim HttpObj As MSXML2.XMLHTTP60
        
        url = "https://finance.yahoo.com/q/op?s=ABX&date=1415318400"
        
        Set HttpObj = New MSXML2.XMLHTTP60
        HttpObj.Open "GET", url, False
        HttpObj.setRequestHeader "User-Agent", "XMLHTTP/1.0"
        HttpObj.Send 
        
        While HttpObj.readyState <> 4
                DoEvents
        Wend
        YahooHtml = HttpObj.responseText
      
    End Sub

    • Marked as answer by Kevin Waddle Monday, April 24, 2017 11:47 PM
    Sunday, April 23, 2017 2:43 PM

All replies

  • In the System Info line you say IE11.  Are you using IE11?  If not, what version are you  using?
    Thursday, April 20, 2017 11:56 PM
  • Thanks for your reply.

    I am using IE11 as my browser.

    However, from what I can find on the web Excel uses an internal version of IE for web queries and not your default browser.

    It appears that the internal, web query browser version is IE7.

    When I query the web site (IE11 required) using VBA, or just manually creating a new web query, I get the unsupported browser error.

    Thank you,

    Kevin

    Friday, April 21, 2017 12:33 AM
  • You could use SeleniumBasic and IE11 to extract table.  The release is a little dated but IE11 works fine.  You can get Chrome and Edge to work by replacing the drivers.

    Private Sub Handle_Table2()
      Dim driver As New Selenium.IEDriver
      driver.Get "http://the-internet.herokuapp.com/tables"
      
      Dim tbl As TableElement
      Set tbl = driver.FindElementByXPath("//table[@id='table1']").AsTable
      
      'Print all cells
      Dim data(): data = tbl.data
      For c = 1 To UBound(data, 1)
        For r = 1 To UBound(data, 2)
          Debug.Print data(c, r)
        Next
        Debug.Print Empty
      Next
      
      driver.Quit
    End Sub

    Friday, April 21, 2017 7:34 PM
  • Unfortunatley, due to network policy restrictions, I cannot use any third party software.

    Any solution must involve native MS Office products.

    Thank you,

    Kevin

    Saturday, April 22, 2017 4:01 PM
  • The QueryTables sends a user agent string to the webserver so the server knows how to treat the client.  You could create a webpage and using Javascript detect the string and see what it is.

     var ua = window.navigator.userAgent;

    I don't see an option to change it.  You could use MS XML 6.0 to get the data.  It has an option to set the header. 

    Sub GetYahoo()
    
        Dim url As String, YahooHtml As String
        Dim HttpObj As MSXML2.XMLHTTP60
        
        url = "https://finance.yahoo.com/q/op?s=ABX&date=1415318400"
        
        Set HttpObj = New MSXML2.XMLHTTP60
        HttpObj.Open "GET", url, False
        HttpObj.setRequestHeader "User-Agent", "XMLHTTP/1.0"
        HttpObj.Send 
        
        While HttpObj.readyState <> 4
                DoEvents
        Wend
        YahooHtml = HttpObj.responseText
      
    End Sub

    • Marked as answer by Kevin Waddle Monday, April 24, 2017 11:47 PM
    Sunday, April 23, 2017 2:43 PM
  • Thank you.

    Looks promising.

    I will try this Monday when I get into the office and let you know.

    Thank you again,

    Kevin

    Sunday, April 23, 2017 3:26 PM
  • That did it!!!

    Thank you.

    I ended up converting your code to a function. The function takes the URL, and an output file name and path and returns the html for my web query saved to an HTML file...

    Function OpenGDSS(strURL As String, strOutputFile As String, strOutputFilePath As String) As String
    
        Dim url                             As String
        Dim strHTML                         As String
        Dim HttpObj                         As MSXML2.XMLHTTP60
        Dim strPath                         As String
        Dim fso                             As Object
        Dim oFile                           As Object
        Dim Fileout                         As Object
        'Requires reference to Microsoft XML, v6.0
    
        Set HttpObj = New MSXML2.XMLHTTP60
        HttpObj.Open "GET", strURL, False
        HttpObj.setRequestHeader "User-Agent", "Mozilla/5.0 (Windows NT 6.3; Trident/7.0; rv:11.0) like Gecko"
        HttpObj.Send
    
        While HttpObj.ReadyState <> 4
            DoEvents
        Wend
        strHTML = HttpObj.responseText
        OpenGDSS = HttpObj.responseText
        Set fso = CreateObject("Scripting.FileSystemObject")
        Set Fileout = fso.CreateTextFile(strOutputFilePath & "\" & strOutputFile, True, True)
        Fileout.Write strHTML
        Fileout.Close
        Set fso = Nothing
        Set oFile = Nothing
        
    End Function

    The I used the existing web query code, but changed the source to this temp HTML file...

    Sub TestURL()
    Dim myURL As String
    Dim z As Variant
    myURL = "https://www.somewebsite.com"
    
    z = OpenGDSS(myURL, "TempFile.html", ActiveWorkbook.Path)
    myURL = ActiveWorkbook.Path & "\TempFile.html"
           
            With DataSheet.QueryTables.Add(Connection:="URL;" & myURL, Destination:=DataSheet.Range("a1"))
                .BackgroundQuery = True
                .TablesOnlyFromHTML = False
                .Refresh BackgroundQuery:=False
                .SaveData = True
            End With
            Kill ActiveWorkbook.Path & "\IraqLoad.html"
    End Sub

    Thank you so much for your help,

    Kevin

    • Proposed as answer by A.Hofmann Wednesday, August 9, 2017 8:35 AM
    Monday, April 24, 2017 11:47 PM
  • Wow, well done.

    Thank you mogulman and Kevin for sharing the solution!

    Best wishes,

    Andreas

    Wednesday, August 9, 2017 8:34 AM