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.
 

Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.
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.
 
Upvote 0
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.
 
Upvote 0
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?
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,535
Messages
6,114,194
Members
448,554
Latest member
Gleisner2

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
Back
Top