Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA Hangs
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA Hangs
I have the exact same problem... It's driving me nuts!
Have you found the answer? "Bill Martin" wrote: 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA Hangs
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?cod...teurlcache.htm The timer question has met with resounding silence in several places. Bill --------------------------------- Albert wrote: I have the exact same problem... It's driving me nuts! Have you found the answer? "Bill Martin" wrote: 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA Hangs
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" wrote: 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?cod...teurlcache.htm The timer question has met with resounding silence in several places. Bill --------------------------------- Albert wrote: I have the exact same problem... It's driving me nuts! Have you found the answer? "Bill Martin" wrote: 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA Hangs
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 wrote: 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" wrote: 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?cod...teurlcache.htm The timer question has met with resounding silence in several places. Bill --------------------------------- Albert wrote: I have the exact same problem... It's driving me nuts! Have you found the answer? "Bill Martin" wrote: 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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA Hangs
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. "Bill Martin" wrote: 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 wrote: 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" wrote: 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?cod...teurlcache.htm The timer question has met with resounding silence in several places. Bill --------------------------------- Albert wrote: I have the exact same problem... It's driving me nuts! Have you found the answer? "Bill Martin" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
sql hangs | Excel Programming | |||
sql hangs | Excel Programming | |||
Excel hangs... | Excel Programming | |||
Macro Hangs | Excel Programming | |||
VBA Hangs | Excel Programming |