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.”