Import from Web - Code Interruption

[gtg430i]

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,

[Jan Karel Pieterse]

Perhaps you can show us the code?

[gtg430i]

Hello - My apologies for the delayed response, but I still need to resolve this.




The code basically loops from page 100 to 2000 and downloads the data onto Excel. It's working perfectly except with the "Contact server for Information" message that I get sometimes with which I need to press the Esc button to overcome.


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 take 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

[SamT]

"Contacting Server for Information" should have an associated Error #

On Error MsgBox Err #

Then you can trap that Err# and WAIT (some seconds) then Resume at the Error point.

It is even possible that the very simple

On Error Resume Next

could work in your circumstances.

I don't have even a clue if you can combine them

On Error Wait(5): Resume [Next]

[gtg430i]

What is the error # for this message?

[gtg430i]

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

[fredlo2010]

Hi guys,

This is a cross post thread

http://www.excelforum.com/excel-prog...ml#post3524054

[gtg430i]

Hi - Yes I did post the same issue on the Excel forum. Would that be a problem?

[sassora]

http://www.vbaexpress.com/forum/show...-Cross-Posting

[gtg430i]

Well Please accept my apology in this case. I did not know this is a serious violation.

can I still get help please? I dont think my solution above with the Errhandler line is helping. In fact it might be the solution but I am not placing the line of code above the line that s causing the error. I thought the code where I try to connect to the website is the cause of the erro so i place the errhandler before.

How can I know which line of code is causing the "contacting server for information" error?

thanks

[ashleyuk1984]

Just an idea... Maybe try to get the property of the status bar in excel??

Such as:

Errhandler:

If Application.Statusbar = "Contacting Server for Information" Then
Application.SendKeys "{esc}", True
Else
Application.SendKeys "{enter}", True '<----- or "~" >
End if
Resume

Note: I don't know if that works, it's just off the top of my head, you might need to look into that property more in order to get it working.
However, if the status bar still says this when you need to press enter, then this probably wouldn't work.
Is the "continue" clickable with the mouse? Maybe send a mouse click to it??
It's hard to diagnose without the code & screenshots. We're just guessing at best.

[SamT]

Cross posting itself is not a problem. It is merely considered rude when you don't make note of the fact by giving a link to the other site(s). See the FAQ link in my signature for more info.

[Jan Karel Pieterse]

I doubt whether sendkeys is the best solution. But without seeing your code I cannot possibly tell (I am not registered at excelforum.com so I cannot see what's posted there)...