excel vba - web download stalled in VBA -

i've written simple vba program download stock quotes portfolio in google finance. works fine couple of hours , hangs up. in applications status bar says "connecting to" (internet?). once stuck won't respond esc key , have force end windows task manager.

the portfolio accessed once every 5 minutes , data placed @ a1 copied separate page storage. code access portfolio is:

with activesheet.querytables.add(connection:= _     "url;https://www.google.com/finance#", destination:=range("$a$1"))     .name = "finance#"     .fieldnames = true     .rownumbers = false     .filladjacentformulas = false     .preserveformatting = true     .refreshonfileopen = false     .backgroundquery = true     .refreshstyle = xloverwritecells     .savepassword = false     .savedata = true     .adjustcolumnwidth = true     .refreshperiod = 0     .webselectiontype = xlspecifiedtables     .webformatting = xlwebformattingnone     .webtables = """portfolio1"""     .webpreformattedtexttocolumns = true     .webconsecutivedelimitersasone = true     .websingleblocktextimport = false     .webdisabledaterecognition = false     .webdisableredirections = false     .refresh backgroundquery:=false end 

the error occurs randomly, after long period of time (hours) , doesn't appear time of day dependent.

i tried setting refresh backgroundquery:=true result being message box pops when program hangs. acknowledging message box appears clear problem need program run autonomously , handle these hiccups without babysitter.

i found problem results using application.wait create timer can't mechanics of stalling are, execution stops @ query command. original program had timer counted down 5 minutes , queried google current stock prices ticker symbols in google portfolio. solution has been use application.ontime instead. side benefit of excel's attention consumed application.wait such nothing can done in excel while running. application.ontime on other hand seems offload timer function hardware, or ??, such excel available other things while waiting timer time out.

the whole program looks this:

dim clock date               'countdown time dim click date               'default time of 12:00:00 if no other input given. here functions '0' in date format dim wait string              'wait format = "00:10:00"  = 10 minutes dim text string              'capture user input delay between quotes  dim schtime date  sub initialize()  worksheets("daily").select text = cells(2, 1).value        'user supplied time between quotes: 1-59 minutes wait = "00:" + text + ":00" clock = timevalue(wait)  end sub  sub timer()  schtime = + timevalue("00:00:01") application.ontime schtime, "tictoc"  end sub   sub end_timer()  application.ontime earliesttime:=schtime, _ procedure:="tictoc", schedule:=false  end sub  sub quote() dim querytables worksheet dim rownum integer dim string dim shift string  application.screenupdating = false  sheets("5 min update").select = range("l2")                     'get user supplied time offset adjust local time zone ny time sheets("daily").select  'find next empty row data  rownum = 8 while cells(rownum, 7) <> ""     rownum = rownum + 1              'where start putting data on page wend  sheets("5 min update").select  activesheet.querytables.add(connection:= _     "url;https://www.google.com/finance#", destination:=range("$a$1"))     .name = "finance#"     .fieldnames = true     .rownumbers = false     .filladjacentformulas = false     .preserveformatting = true     .refreshonfileopen = false     .backgroundquery = true     .refreshstyle = xloverwritecells     .savepassword = false     .savedata = true     .adjustcolumnwidth = true     .refreshperiod = 0     .webselectiontype = xlspecifiedtables     .webformatting = xlwebformattingnone     .webtables = """portfolio1"""     .webpreformattedtexttocolumns = true     .webconsecutivedelimitersasone = true     .websingleblocktextimport = false     .webdisabledaterecognition = false     .webdisableredirections = false     .refresh backgroundquery:=false end  sheets("5 min update").select  'move tickers rolling table  sheets("daily").select     range("g8", "t8").select     selection.clearcontents sheets("5 min update").select range("a1", range("a1").end(xldown)).select selection.copy sheets("daily").select cells(8, 7).select selection.pastespecial paste:=xlpastevalues, operation:=xlnone, skipblanks _     :=false, transpose:=true   'move $$ quote rolling table  sheets("5 min update").select range("b1", range("b1").end(xldown)).select selection.copy sheets("daily").select cells(rownum, 7).select selection.pastespecial paste:=xlpastevalues, operation:=xlnone, skipblanks _     :=false, transpose:=true  'time stamp  shift = "0" + + ":00:00"  cells(rownum, 4).value = date + timevalue(shift)  '("03:00:00") cells(rownum, 4).numberformat = "ddd" cells(rownum, 5).value = date + timevalue(shift) cells(rownum, 5).numberformat = "dd-mmm-yy" cells(rownum, 6).value = + timevalue(shift) cells(rownum, 6).numberformat = "h:mm am/pm"  'clean mess: close connections , querytables  dim integer dim ws worksheet dim qt querytable each ws in thisworkbook.worksheets each qt in ws.querytables qt.delete next qt next ws  if activeworkbook.connections.count > 0     = 1 activeworkbook.connections.count     activeworkbook.connections.item(1).delete     next end if  range("a5").select thisworkbook.save  application.screenupdating = true  end sub   sub tictoc()  'display countdown till next quote comes in  if clock > click                          'click = '0' in date format     range("a4").value = clock     clock = clock - timevalue("00:00:01") else     range("a4").value = "00:00"     call quote     call initialize end if  call timer  end sub  sub reset_clock()  worksheets("daily").select clock = "00:00" range("a4").value = "00:00"  end sub 

the sub tictoc creates countdown timer display indicating how long until next quote. 'run' button points macro begin program. when program first opened variables 0 macro set timer display "00:00" , call quote macro, re-initializes count down timer , starts timer macro. stop macro included. after stopping if run pressed again timer pick left off unless clock has been manually reset (reset_clock macro , user button).

its form delete connections , query tables when you're done them. in debugging first program accumulated on 800 connections added couple of loops clean these up. occurs @ end of quote macro.