Import from Web - Code Interruption

Hello - I have a VBA code that extracts data from a website. At certain times, the code stops and the following message on the lower left corner of Excel appears "Contacting Server for Information". Now if I press the Esc button, the code resumes and runs fine.

The problem is that the code takes a very long time to execute and will have to leave overnight to run. I basically need a line of code that automatically presses the Esc in case of a freeze.

I appreciate all your help.

Thank You,

Can you post a sample of your code?

Thanks

Any suggestion?

Thanks

is this a problem you can repeat, in other words is it random ? I have been looking online for a solution but I have found none. I cannot replicate the issue because I don't have the whole code or the source. You could set a break point and see where the issue is and then try to determine its cause or how to fix it.

what are you doing after importing the query? Are you copying it to a different location? You can delete all previous queries maybe it will fix it.

I am sorry I cannot help more. Maybe if you share a little bit more of your code I might be able to take a look.

Thanks

Check this article I think will help with the problem

http://social.technet.microsoft.com/...deployprevious

Hi - What it I believe is causing this freeze is the internet connection. This is a very random freeze problem that may or may not occur. What's happening after the import has nothing to do with it. The freeze occur ONLY when trying to import. As I mentioned when this message appears in the lower left corner, hitting the escape button simply solves it. I tried to delete all previous queries, but this didnt' solve it.

I was thinking maybe I can add the following line Application.SendKeys "{esc}", True that executes every 5 minutes while the main import code is also running. I chose 5 minutes because the import does not that more than 3 minutes. When this limit is exceeded I know that the code is frozen.

I don't know if I mentioned, the freeze usually resolves by itself and the code resumes. Now this take 10 minutes or sometimes 40 minutes. I just need to bypass this to save on running time.

Thanks

Yes, Send keys can be a solution. Now I am not sure if this will also cause the updating of the current query to be cancelled as well.
But did you check my question in post #5? Are you saving the values of the query and then deleting the query before a new query is added?

Is there a way you can share more of the code? I really would like to see what else is going on. Maybe there is something else that can be done to optimize it.

Thanks

Hi - Thank you for your support. I really wish not to share the code as I believe the code itself has no cause to the error.

The imported data is dumped in excel and the previous query is deleted with the following code:

'With ActiveSheet
' For i = .QueryTables.Count To 1 Step -1
' .QueryTables(i).Delete
' Next
'End With

and then it loops to the next page of the website..

Is it possible to run two codes concurrently? I need while the main code is running, another code runs Application.SendKeys "{esc}", True every five minutes.

I understand if you are no longer able to help.

Thanks

Ok - Hi again. I believe I solved, well It's a work around. I placed the following:

On Error GoTo Errhandler:
'''Code goes here

Errhandler:
' MsgBox Err & ": " & Error(Err)
' Application.Wait Now + TimeValue("00:00:5")
Application.SendKeys "{esc}", True
Resume

Now the above automatically presses the Esc button when needed, however I am facing another problem now. Sometimes the code is interrupted and I will need to press the Continue button. Can I do this automatically WITHOUT disabling the Esc button(this will be needed to correct the original problem). I other words automatically press continue.

Thanks