VB(A) and InternetExplorer and XMLHttp

Over the past few weeks, I’ve had reason to explore the use of VBA to access information on web pages and through web services using both InternetExplorer and XMLHttp. While my study of the two is far from exhaustive, I decided to document the research for general consumption expecting it to take a few hours. As I wrote more issues cropped up and the “few hours” project took several days. But, it’s finally uploaded at
VBA & web services

Posted in Uncategorized

11 thoughts on “VB(A) and InternetExplorer and XMLHttp

  1. Thank you very much for your greatly useful tips, Tushar. I have been enjoying reading your articles here and in your web site.

    Would you mind giving me some help with my case as the following?

    I’d like to open two different web sites and and then combine the information from these two sites. I can grab the information that I want from the first web site, but I cannot get any information from the second web site. It looks like the oIE.document object always point to the first web site and does not switch to the second after it is loaded. Hope you can point out the things that I am doing wrong here and offer some solutions.


    Here is my procedure and I am using Internet Explorer 7

    Sub NavigateToTwoWebSites()

    txtURL1 = “http://www.google.com/”
    txtURL2 = “http://www.yahoo.com/”

    Set oIE = CreateObject(“InternetExplorer.Application”)
    oIE.Visible = True
    oIE.Navigate txtURL1

    While oIE.Busy Or oIE.ReadyState  READYSTATE_COMPLETE

    Debug.Print oIE.document.all.tags(“A”).Item(0).outerHTML

    oIE.Navigate2 txtURL2, 1

    While oIE.Busy Or oIE.ReadyState  READYSTATE_COMPLETE

    Debug.Print oIE.document.all.tags(“A”).Item(0).outerHTML

    Set oIE = Nothing
    End Sub

  2. Huaming — Why did you switch over to the Navigate2 method and ask for it to open in another window? On my machine, the browser settings block the opening of the second window, which is why nothing changes for me.

    It works fine for me if I just use:

    oIE.Navigate txtURL2

    …for the second web page retrieval. In any case, even if the IE object were to open another window for the second web page, you’d need to refer to THAT object to get your outerHTML data (not sure how to do that).

  3. Hi, Randy,
    I use the “navigate2? method to keep these two web sites in two separate tabs underneath same Internet Explorer window. This is to make it little easier to compare and to follow links in them. I also tried to create another oIE object by using the following procedure, but it does not work either.

    Set oIE2 = CreateObject(“InternetExplorer.Application”)
    oIE2.Visible = True
    oIE2.Navigate2 txtURL2, 1
  4. Try creating your second EXCEL object as:

    Set oIE2 = CreateObject(“InternetExplorer.Application”)
    oIE2.Visible = True
    oIE2.Navigate txtURL2

    Using the “1? flag would have the same problem your original code did — it’s trying to open the URL in a new window. Your browser security settings may be preventing that.

  5. Thanks, Randy!!! It does what I wanted. But can you merge these two windows into same one window but in two separate tabs?

  6. Hi Tushar,

    Excellent article.

    A suggestion though. There is no need to go through the hoops of creating a class with a default property, the msxml.DOMDocument has two events you can use. In the Class, you can:

    Public WithEvents DOMEvent As DOMDocument

    Private Sub DOMEvent_onreadystatechange()
        If DOMEvent.readyState = 4 Then
            MsgBox “Loaded.” & DOMEvent.XML
        End If
    End Sub

    Then in a normal module (or wherever you put the code that calls up some XML):

    Dim moXMLdoc As MSXML2.DOMDocument
    Dim mcXMLClass As clsMSXML

    Sub demo()
        Set mcXMLClass = New clsMSXML
        Set moXMLdoc = New MSXML2.DOMDocument
        Set mcXMLClass.DOMEvent = moXMLdoc
        moXMLdoc.async = True
        moXMLdoc.Load “http://www.jkp-ads.com/feed.xml”
    End Sub

  7. I have been looking for a way to grab information from a website and paste it into Excel. The idea is to use Excel to surf to a particular website, select an option from an option box, move forward a page, copy that information into a spreadsheet, then move back a page and select the next option in the option box. So far I cannot find any resources as to how to identify and get the elements from the option box, count the elements in the option box, whatever. Anyone have any suggestions?

  8. Hi ,
    im actually trying to log into my yahoo inbox and search a particular mail from QTP .For this i was tryin to use thw Wsh to send keys , turns out thats not an option .Then i used the Internet explorer object , however i do not know how to browse the application with that kinda scriptin.

  9. I have list of urls in Column A. Is it possible to create a query that extracts all the other urls present in the webpage of the url in ColumnA?

Posting code? Use <pre> tags for VBA and <code> tags for inline.

Leave a Reply

Your email address will not be published.