Introduction
We often use the Web Query function of Microsoft Excel to extract data such as stock quotes from various websites. Many a time, Excel just hang without reporting any error message. The only way to resolve the problem is to terminate the Microsoft Excel application program. This article presents a simple and effective way of resolving this problem.
What’s Required?
Some experiences in macro or VBA programming.
What Has Been Tried?
The Web offered many solutions to resolve this problem but none could work better; either because the solutions, such as introduction of scheduled (OnTime) procedure, do not work or they have complicated macros or VBAs that no one can understand. Error trappings using Error handling loops do not help either because the Website being queried was only down and not out.
What is the Solution?
Write a short macro to “ping” the website before initiating the web query command.
How?
a) Using the API function of VBA and putting a Public declaration in the top or heading of the macro or VBA.
Private Declare Function InternetCheckConnection Lib "wininet.dll" _
Alias "InternetCheckConnectionA" _
(ByVal lpszUrl As String, _
ByVal dwFlags As Long, _
ByVal dwReserved As Long) As Long
Private Const FLAG_ICC_FORCE_CONNECTION = &H1
b) Writing or using the recording function of Excel to create a macro for the Web Query and insert the following macro to the Web Query VBA
Sub downloading()
Dim sUrl As String
sUrl = “http://yahoo.com”
but = InternetCheckConnection(sUrl, FLAG_ICC_FORCE_CONNECTION, 0&)
If but Then
…initiate web queries
Else
… announce website is down
End If
End Sub
Doesn't work
ReplyDeleteKindMike, many websites nowadays have barred downloading of data using Excel Web Query. One would have to use program such as imacros instead
ReplyDelete