Results 1 to 7 of 7

Web Data Query - hangs and need to resart Excel

This is a discussion on Web Data Query - hangs and need to resart Excel within the Excel Questions forums, part of the Question Forums category; I am doing a data query but sometimes when the code asks it to refresh it just hangs and never ...

  1. #1
    Board Regular
    Join Date
    May 2004
    Posts
    68

    Default Web Data Query - hangs and need to resart Excel

    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.
    A long time Excel user - with a lot to learn.

  2. #2
    Board Regular
    Join Date
    May 2004
    Posts
    68

    Default Re: Web Data Query - hangs and need to resart Excel

    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.
    A long time Excel user - with a lot to learn.

  3. #3
    Board Regular
    Join Date
    Oct 2007
    Posts
    2,932

    Default Re: Web Data Query - hangs and need to resart Excel

    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.

  4. #4
    Board Regular
    Join Date
    May 2004
    Posts
    68

    Default Re: Web Data Query - hangs and need to resart Excel

    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?
    A long time Excel user - with a lot to learn.

  5. #5
    Board Regular
    Join Date
    Oct 2007
    Posts
    2,932

    Default Re: Web Data Query - hangs and need to resart Excel

    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.

  6. #6
    Board Regular
    Join Date
    May 2004
    Posts
    68

    Default Re: Web Data Query - hangs and need to resart Excel

    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
    A long time Excel user - with a lot to learn.

  7. #7
    Board Regular
    Join Date
    Oct 2007
    Posts
    2,932

    Default Re: Web Data Query - hangs and need to resart Excel

    Quote Originally Posted by matty1973 View Post
    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-hi...mmand-line.htm.

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


DMCA.com