Web Query Loop Issue

[preludeofme]

Our company just recently upgraded to office 2010 and some of the scripts are not working anymore. one in particular that's driving me insane is a web query loop. I have been looking everywhere to figure out what the issue is but cannot find a fix.

It loops fine up until about the 70-100th query and then it locks up excel and i have to kill it in the task manager. I have tried dumping the cookies and history every few queries but it doesn't make a difference. Any ideas?

here's the main part of the code that pulls the information and where it is locking up:

Sub RunQuery()
 
Do Until ActiveCell.Value = ""

range("Activevalue").value = activecell.value
 
    Range("Activevalue").Value = Left(Replace(Range("A1").Value, "-", ""),9)
        With ActiveSheet.QueryTables.Add(Connection:= _
            "URL;webaddress & Range("Activevalue").Value _
            , Destination:=Range("H1"))
            .Name = _
            "=webaddress" & Range("ActiveValue").Value
            .FieldNames = True
            .RowNumbers = False
            .FillAdjacentFormulas = False
            .PreserveFormatting = True
            .RefreshOnFileOpen = False
            .BackgroundQuery = False
            .RefreshStyle = xlInsertDeleteCells
            .SavePassword = False
            .SaveData = True
            .AdjustColumnWidth = True
            .RefreshPeriod = 0
            .WebSelectionType = xlSpecifiedTables
            .WebFormatting = xlWebFormattingNone
            .WebTables = """InformationGrid"""
            .WebPreFormattedTextToColumns = True
            .WebConsecutiveDelimitersAsOne = True
            .WebSingleBlockTextImport = False
            .WebDisableDateRecognition = False
            .WebDisableRedirections = False
            .Refresh BackgroundQuery:=False
        End With
 
If x = 15 Then
            ' whack temp files - Process 8
            Shell "RunDll32.exe InetCpl.Cpl, ClearMyTracksByProcess 8"
            Shell "RunDll32.exe InetCpl.Cpl, ClearMyTracksByProcess 1"
        x = 0
    Else
        x = x + 1
    End If
 
activecell.offset(1,0).select
Loop
 
End Sub

 

[preludeofme]

still stuck, anyone else have the same problem and find a fix?

 

[John_w]

Your code is creating a new web query for each loop iteration, so for example that's 100 queries for 100 times round the loop. Do you need 100 separate queries? Try redesigning it to create just one web query outside the loop and for each loop iteration modify the Connection property with the required URL and call the Refresh method.

My code here uses this technique - http://www.mrexcel.com/forum/showthread.php?p=2494713#post2494713

 

[preludeofme]

thanks i'll give it a shot, i forgot to add that after i posted i tried adding to the end of the loop a delete query code so that it would clear the query after it ran it and pulled the information but still had the same problem. i'll try the refresh

Thanks

 

[preludeofme]

worked perfect, thanks!

still weird though because i thought deleting the query would have resolved any memory issues... oh well

 

[silicon_beaver]

Thanks John_w

Your tip is very useful for me. I have read the web years and learned lots from mrexcel.

I started VBA on Office 97 since it launched (meanwhile, my OS has changed from 97-->98-->ME-->XP-->Win7). Have been using the macro to query daily yahoo finance data.
Recently I run into issues of memory and temp files when I was testing my old VBA on new Win7.
(similar issue reported here: http://www.mrexcel.com/forum/excel-...el-visual-basic-applications-web-query-2.html)

I almost gave up my Office 97 and thought it's the main problem.
Your tip helps. Now my old Office 97 VBA runs on Win7/XP well.

Btw, my experience, querying yahoo data using Office 97 is much faster than any newer MS Office version (2003/2007/2010/2013). A primary reason I keep my Office97 alive.
(my home ISP, about one query/per second on Office97. i.e. about an hour for 4000's stock tickers. But took about four hours if using 2007/2010. impractical for daily data analysis)

Thanks again.

 

[silicon_beaver]

Here is my follow-up report.

Per my test, the method of refresh QueryTable by John is faster than creating a new web query each loop iteration, a common way by most users.

I played my new code using refresh last two days on two platforms: Office 2010 and Office 97.

On Office 2010, it runs smooth and fast. No any issues. Office 2010 looks attractive to me now. :D
(my previous macro was running slow on Office higher version)

On Office 97, it runs much faster, but still crashes, after about 500 ~ 1300 tickers. I had to save after every 500 tickers and to slice my test of 2000 tickers into four books/saves. then assembly them into one later when done.
The runtime of 500 tickers test, 34 min by my previous macro; 370 sec by the new macro. Amazing improvement on Office 97.

Thanks John

btw, does VBA Chart have a similar refresh feature? i.e. is there a Chart.Refresh or similar function?