Retrieving Web Query Data without Interruption

Written by Allen Wyatt (last updated December 22, 2022)
This tip applies to Excel 2007 and 2010


7

Nikolas has developed a Web query to retrieve external data on a regular basis. The problem is that he frequently receives an "Unable to open the web page..." error message when running the Web query. This message appears when there is some interruption of the Internet connection between Nikolas and the Web server, and he needs to click OK on the error message so that Excel will continue.

This presents a problem when Nikolas is away from his computer because it may mean that the Web query doesn't collect all the data it should be cause it is patiently waiting for the OK button to be clicked when it runs into a problem. Nikolas wants a way to tell the Web query to not display the message and just go back to waiting if it can't connection on the current attempt.

Unfortunately, there is no way to tell Excel to do what you want. When the "Unable to open the web page..." message appears, it is virtually impossible to suppress the message. The only solution is to try to create a macro that works around the problem. For instance, you could develop a macro that creates an instance of Internet Explorer (which doesn't have the problem) to test for an error reaching the Web page. The following macro implements this approach.

Option Explicit
'Declare Sleep API
Private Declare Sub Sleep Lib "kernel32" (ByVal nMilliseconds As Long)

Function GetData(strStartURL As String) As String
    Dim Attempt As Long
    Dim Connected As Boolean
    Dim ieDocNew As MSHTML.HTMLDocument

    GetData = "N/A"
    Attempt = 0

retry:
    Attempt = Attempt + 1

    'Create browser object references and open an IE window
    Dim ieNew As New InternetExplorer
    'Load page
    With ieNew
        .Visible = True       'show window
        .navigate strStartURL 'open page
        While Not .readyState = READYSTATE_COMPLETE
            Sleep 500         'wait 1/2 sec before trying again
        Wend
    End With

    'The page should be open in IE, time for parsing
    'Create document object model references
    Set ieDocNew = ieNew.Document

    If ieDocNew.Scripts.Length = 13 _
      And ieNew.LocationName = "Microsoft Excel Tips" _
      Then
        Connected = True
        GetData = "Data successfully captured"

        'This is where you do something with the data
    End If

    'Clean up IE objects
    Set ieDocNew = Nothing
    ieNew.Quit
    Set ieNew = Nothing
    DoEvents
    If Attempt < 10 And Not Connected Then GoTo retry
End Function

Note that this macro requires some configuration within the VBA interface to properly operate. Specifically, you need to choose References from the Tools menu and make sure that the project includes references to the Microsoft HTML Object Library and the Microsoft Internet Controls.

What the macro does is to use IE to connect to the URL passed to the function (in strStartURL) and then grab the content that is found there. If the connection is successful, then Connected is set to True and you can parse and use the data at the site. The function, as written, passes back "Data successfully captured" to the calling routine, but you could just as easily pass back some value grabbed from the remote site. That value could then be stuffed, but the calling routine, into a worksheet.

Note, as well, that the function does some rudimentary parsing on the page it captures, and only considers the connection successful if it finds some expected wording in the page title found at the URL.

To get a feel for how the macro works, use some macros like the following:

Sub TEST_GetData1()
    MsgBox GetData("http://excel.tips.net")
End Sub
Sub TEST_GetData2()
    MsgBox GetData("http://excel.tipsxx.net")
End Sub
Sub TEST_GetData3()
    MsgBox GetData("http://excel.tips.net/junk")
End Sub

The first one should work; the second two should display a message that says "N/A."

Note:

If you would like to know how to use the macros described on this page (or on any other page on the ExcelTips sites), I've prepared a special page that includes helpful information. Click here to open that special page in a new browser tab.

ExcelTips is your source for cost-effective Microsoft Excel training. This tip (10525) applies to Microsoft Excel 2007 and 2010.

Author Bio

Allen Wyatt

With more than 50 non-fiction books and numerous magazine articles to his credit, Allen Wyatt is an internationally recognized author. He is president of Sharon Parq Associates, a computer and publishing services company. ...

MORE FROM ALLEN

Referencing a Page Number In Another Document

Page references are a common element of many documents. If you need to have a page reference to a page in a different ...

Discover More

Creating Venn Diagrams with Excel Data

A common way of representing data is to use a Venn diagram. Unfortunately, Excel doesn't have a precise way of creating ...

Discover More

Adding a ScreenTip

Need to add a ScreenTip to your document? It's easy to do, provided you are adding a hyperlink.

Discover More

Solve Real Business Problems Master business modeling and analysis techniques with Excel and transform data into bottom-line results. This hands-on, scenario-focused guide shows you how to use the latest Excel tools to integrate data from multiple tables. Check out Microsoft Excel 2013 Data Analysis and Business Modeling today!

More ExcelTips (ribbon)

Hyperlinks in Comments

Need to add a hyperlink to a comment or note? It's easy to do by following the steps outlined in this tip.

Discover More

Specifying Default Hyperlink Text

When you insert into a cell a hyperlink that references a file on your system, the text displayed by default matches the ...

Discover More

Converting a Range of URLs to Hyperlinks

Converting a single URL into a hyperlink is easy. Converting hundreds or thousands can be much harder if you have to rely ...

Discover More
Subscribe

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

View most recent newsletter.

Comments

If you would like to add an image to your comment (not an avatar, but an image to help in making the point of your comment), include the characters [{fig}] (all 7 characters, in the sequence shown) in your comment text. You’ll be prompted to upload your image when you submit the comment. Maximum image size is 6Mpixels. Images larger than 600px wide or 1000px tall will be reduced. Up to three images may be included in a comment. All images are subject to review. Commenting privileges may be curtailed if inappropriate images are posted.

What is five more than 8?

2022-12-27 04:32:11

sandeep kothari

Thanks a ton Woolley.


2022-12-26 15:37:12

J. Woolley

@sandeep kothari
Also, replace this statement
Private Declare Sub Sleep Lib "kernel32" (ByVal nMilliseconds As Long)
with this statement
Private Declare PtrSafe Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)


2022-12-26 15:14:22

J. Woolley

@sandeep kothari
Replace this statement
  If ieDocNew.Scripts.Length = 13 _
    And ieNew.LocationName = "Microsoft Excel Tips" _
    Then
with this statement
  If Left(ieNew.LocationName, Len("Microsoft Excel Tips")) _
    = "Microsoft Excel Tips" Then
and review the paragraph that begins with: "Note that this macro requires some configuration...."


2022-12-25 07:46:35

sandeep kothari

Allen, The code does not work on 64 bit system.


2016-03-18 15:46:20

Don Faison

since I don't and can't use IE, is there another way around this? I used to get daily historical exchange rates from http://fxtop.com table using a macro with a web query, but fxtop added an ad above the table and the query now returns no data.


2014-04-17 08:17:17

Doug

Another option is use the utility PTFB (Push The Frickin' Button), available at http://www.tlhouse.co.uk. It will automatically click on a button for you. I have no affiliation with this company except as a satisfied customer.


2013-09-27 06:22:06

Raj

I want to showing result of school in private website on webs.com.....

Please suggess me , how it will do ?


This Site

Got a version of Excel that uses the ribbon interface (Excel 2007 or later)? This site is for you! If you use an earlier version of Excel, visit our ExcelTips site focusing on the menu interface.

Newest Tips
Subscribe

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

(Your e-mail address is not shared with anyone, ever.)

View the most recent newsletter.