Web Data Query - hangs and need to resart Excel

[matty1973]

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.

 

[matty1973]

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]

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]

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]

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]

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]

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:

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

or:

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):

Shell "RunDll32.exe InetCpl.cpl,ClearMyTracksByProcess 9"

See http://www.petri.co.il/delete-ie7-history-from-command-line.htm.