VBA Hangs

[Bill Martin]

I have VBA code that reaches out to Yahoo to retrieve some information.
Occasionally it will hang indefinitely. I get the status message "Connecting to
the web" so my assumption is that the web has not responded with some hand shake
that Excel was expecting to get. When it hangs there's apparently no way to
shake it alive from the keyboard. I can only exit Excel entirely and restart
everything.

I've tried trapping the hang with an "On Error Goto" statement, but apparently
Excel does not think there's any actual error - it's just waiting for a response.

The actual web fetch is of the form:

With ActiveSheet.QueryTables.Add(Connection:="URL;" & qurl,
Destination:=QuerySht.Range("A1"))
.BackgroundQuery = True
.TablesOnlyFromHTML = False
.Refresh BackgroundQuery:=False
.SaveData = True
End With

Is there some way I could set a timer for 10 seconds, or whatever, before
executing the web fetch and use it to somehow 'Break' execution of the VBA
statement that's hung?

Thanks.

Bill

 

[Albert]

I have the exact same problem... It's driving me nuts!
Have you found the answer?

 

[Bill Martin]

None... Someone suggested that perhaps the IE cache was over filling and that
emptying it first would help. He provided a link to this VB patch software to
do it, but I haven't tried it yet. Or even know if it will work in VBA.

http://vbnet.mvps.org/index.html?code/internet/deleteurlcache.htm

The timer question has met with resounding silence in several places.

Bill
---------------------------------

 

[Albert]

Hmmm...
Lets try troubleshooting it ourselves...

Here are some of the possible culprits in my list. Do you use any of these?

Office 2010 Tech Preview (but the problem occurrs in all installed versions
of office)
Firefox (latest)
IE8
Digsby
Dropbox
Evernote
Windows XP
AVG 8.5
CLCL (Clipboard manager)

Everything up to date...

Regards,
Albert

 

[Bill Martin]

My system:

1) Office 2003
2) Firefox at the latest level.
3) IE8 installed, but very rarely used.
4) No Digsby, dropbox, evernote or CLCL.
5) AVG 8.5
6) Two machines, one with XP and one with Vista. It will fail on both on rare
and seemingly random occasions.

All software at current levels.

I also have a friend who is using my spreadsheet and he claims to have never
seen it lock up. He's running the latest Excel version under Vista, but I don't
know what Virus program. Perhaps I should ask.

Bill
----------------------------

 

[Albert]

Hi Bill.

I just uninstalled IE8.0 and my queries now seem to be running consistently
now, at least since yesterday.
I'll probably leave IE8.0 uninstalled for now and see how things evolve
before trying to reinstall IE8.0. (I seldom use it anyway)

Perhaps you could do the same so we can continue to troubleshoot and verify
the culprit to be IE8.0?

If you wish please contact me directly at
(e-mail address removed)
(Remove "spamgibberish" from the above email).

Albert C.

 

[Albert]

SOLVED!
The Culprit: AVG (parts of it)
All you have to do to is reinstall AVG choosing which components to install.
Unselect LinkScanner and Microsoft Office Addin.
After doing this I started getting BSOD's galore, and this was solved by
re-installing Firefox.