Web Query Hangs

23 August 2010

Introduction
Users of Microsoft Excel and other web query software, like IMACROS, often query websites to get the web information for stocks or other purposes. These users will write scripts to automate the process. Sometimes, the process will hang causing lost of data. This article will describe methods to overcome this problem.

Why it Hangs?The process will hang either because the website is down or because the web traffic is high. A webpage usually takes 5 to 6 seconds to download may take up to 10 minutes or even hours.

How to Over come?
The following methods have been tried without much success.

a) Introduce timeout events in scripts or VBAThe script is waiting for a webpage to download and will not activate the timeout under such events.

b) Ping the website before proceeding to download
This will only help if the website is down but it will not help when there is a delay due to heavy traffic.

The better method is to modify the window registry’s internet setting with a timeout of appropriate delay. This is because most browsers use this delay in the internet setting.

http://support.microsoft.com/kb/181050

How to Modify the Registry?
The timeout is not native in the registry’s internet setting. It will have to be created under
[HKEY_CURRENT_USER\Software\Microsoft\Windows\CurrentVersion\Internet Settings]. The keyword is "ReceiveTimeout”. Its value will depend on normal delay in webpage download.

The Window default is

Internet Explorer 4.0 and Internet Explorer 4.01 --- 5 minutes
Internet Explorer 5.x and Internet Explorer 6.x---- 60 minutes
Internet Explorer 7 and Internet Explorer 8 ------- 60 minutes

In VBA, add the following code before downloading the data

Set myWS = CreateObject("WScript.Shell")'write registry keymyWS.RegWrite
"HKEY_CURRENT_USER\SOFTWARE\Microsoft\Windows\CurrentVersion\Internet " &
_"Settings\ReceiveTimeout", "15000", "REG_DWORD"

The value of “15000” means a delay of 15 seconds. Insert the same code just after the download script and change the value to suit normal surfing.