Web Data Query - hangs and need to resart Excel

matty1973

Board Regular
Joined
May 27, 2004
Messages
68
I am doing a data query but sometimes when the code asks it to refresh it just hangs and never updates the data and never returns to the code so I have to use task manager to crash Excel and restart. Is there a way to handle this or a better way to do it?

The website I am querying is:

http://hk.racing.nextmedia.com/allodds.php

and the code that gets stuck (only sometimes) is:

Range("AppleOddsCorner").Select
Selection.QueryTable.Refresh BackgroundQuery:=False

Many thanks if you can advise.
 

Some videos you may like

Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.

matty1973

Board Regular
Joined
May 27, 2004
Messages
68
bump - anybody got an idea how to handle this?

I don't know why it sometimes hangs. Really I just want it to be able to continue with the code if it does hang.
 

John_w

MrExcel MVP
Joined
Oct 15, 2007
Messages
6,614
You could try clearing the IE cache periodically or every 30-40 queries. This might prevent the hanging. Search for ClearMyTracksByProcess to do it programmatically.
 

matty1973

Board Regular
Joined
May 27, 2004
Messages
68
Thanks for the reply.

On the computer that it it has been hanging IE hasn't been opened in a long while - will this still help?
 

John_w

MrExcel MVP
Joined
Oct 15, 2007
Messages
6,614

ADVERTISEMENT

It might help because I think a web query uses IE functions internally. It is a known problem that a web query can hang when lots of queries are being made and one solution is to clear the IE cache. And I don't really mean open IE manually and clear its cache (temporary internet files), but do it using code based on the hint I provided. Try it.
 

matty1973

Board Regular
Joined
May 27, 2004
Messages
68
Thank you John - I believe you are correct in your diagnosis and solution.

The code I am using is:

Sub Clear_History()
Shell "RunDll32.exe InetCpl.cpl,ClearMyTracksByProcess 1"
End Sub

I have run a loop of repeated queries and with the ClearMyTracksByProcess it is successful for 100 loops - without it often fails in the first 10 loops!

It does raise a couple of further issues if I am trouble you further:

1) A dialog box gets opened Titled Delete Browsing History - is there a way of hiding this? I tried
Application.ScreenUpdating = False
Application.DisplayAlerts = False

But I think because the box is external to Excel they don't have an effect.

2) Sometimes I now get an Invalid Web Query error - this is not the end of the world though since it does not hang so using

On Error Resume Next

allows it to continue.

3) I believe changing the 1 at the end controls exactly what gets cleared. Do you know if any of the other numbers below are relevant to my situation?

Clear_Temp_Files()
Shell "RunDll32.exe InetCpl.cpl,ClearMyTracksByProcess 8 "

Clear_Cookies()
Shell "RunDll32.exe InetCpl.cpl,ClearMyTracksByProcess 2"

Clear_History()
Shell "RunDll32.exe InetCpl.cpl,ClearMyTracksByProcess 1"

Clear_Form_Data()
Shell "RunDll32.exe InetCpl.cpl,ClearMyTracksByProcess 16"

Clear_Saved_Passwords()
Shell "RunDll32.exe InetCpl.cpl,ClearMyTracksByProcess 32"

Clear_All()
Shell "RunDll32.exe InetCpl.cpl,ClearMyTracksByProcess 255"

Clear_Clear_Add_ons_Settings()
Shell "RunDll32.exe InetCpl.cpl,ClearMyTracksByProcess 4351"

Thanks again - it is a great help
 

John_w

MrExcel MVP
Joined
Oct 15, 2007
Messages
6,614
The code I am using is:

Sub Clear_History()
Shell "RunDll32.exe InetCpl.cpl,ClearMyTracksByProcess 1"
End Sub

I have run a loop of repeated queries and with the ClearMyTracksByProcess it is successful for 100 loops - without it often fails in the first 10 loops!
I'm surprised that deleting the IE history fixed the problem. Clearing the IE temporary files, as I said, is the command which is more likely to fix the problem (Shell "RunDll32.exe InetCpl.cpl,ClearMyTracksByProcess 8").
It does raise a couple of further issues if I am trouble you further:

1) A dialog box gets opened Titled Delete Browsing History - is there a way of hiding this? I tried
Application.ScreenUpdating = False
Application.DisplayAlerts = False

But I think because the box is external to Excel they don't have an effect.
Yes, that window is external to Excel. You could try:

Code:
Shell "RunDll32.exe InetCpl.cpl,ClearMyTracksByProcess 8", vbHide

or:
Code:
Shell "RunDll32.exe InetCpl.cpl,ClearMyTracksByProcess 8", vbHide
AppActivate "Microsoft Excel"
2) Sometimes I now get an Invalid Web Query error - this is not the end of the world though since it does not hang so using

On Error Resume Next

allows it to continue.

3) I believe changing the 1 at the end controls exactly what gets cleared. Do you know if any of the other numbers below are relevant to my situation?

Clear_Temp_Files()
Shell "RunDll32.exe InetCpl.cpl,ClearMyTracksByProcess 8 "

Clear_Cookies()
Shell "RunDll32.exe InetCpl.cpl,ClearMyTracksByProcess 2"

Clear_History()
Shell "RunDll32.exe InetCpl.cpl,ClearMyTracksByProcess 1"

Clear_Form_Data()
Shell "RunDll32.exe InetCpl.cpl,ClearMyTracksByProcess 16"

Clear_Saved_Passwords()
Shell "RunDll32.exe InetCpl.cpl,ClearMyTracksByProcess 32"

Clear_All()
Shell "RunDll32.exe InetCpl.cpl,ClearMyTracksByProcess 255"

Clear_Clear_Add_ons_Settings()
Shell "RunDll32.exe InetCpl.cpl,ClearMyTracksByProcess 4351"
The number argument at the end is a bit mask which determines which IE functions are performed. You can add the numbers together to run multiple functions in one call. For example to clear history and temporary files (1+8):
Code:
Shell "RunDll32.exe InetCpl.cpl,ClearMyTracksByProcess 9"
See http://www.petri.co.il/delete-ie7-history-from-command-line.htm.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,569
Messages
5,596,915
Members
414,110
Latest member
docops

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top