none
Loop to refresh 70+ web queries in Excel 2016 RRS feed

  • Question

  • I have a spread sheet that has some 70 odd queries to finance.yahoo.com to get the previous close of stock prices on the BSE (Bombay Stock Exchange). If I click on Refresh All, Excel hangs and my spreadsheet (sometimes) gets corrupted. I was told to try and use VBA to refresh the connections one by one.

    I have a worksheet called PrevClose. All my queries are in this sheet. Then I pull the values of the previous close onto another worksheet which then does some calculation on the data. The worksheet PrevClose looks like this:

    AKZOINDIA

    Column1 Column2

    Prev Close: 1524.35

    AMUBJACEM

    Column1 Column2

    Prev Close: 232.5

    The first row is the stock symbol. The second and third rows are the result of the query (and stripping of irrelevant data) from finance.yahoo . And the pattern repeats some 70 times.

    I have absolutely no knowledge of VBA, so can someone please tell me how to write a loop that will go through this worksheet and refresh the queries.

    Thanks for your help.

    -V

    Wednesday, June 22, 2016 5:58 AM

Answers

  • I have found a solution. After running the loop that refreshes the connections, I inserted the following code that saves all open workbooks and quits the application:

    For Each w In Application.Workbooks w.Save Next w Application.Quit

    Its not the best solution but it certainly meets my needs.


    • Marked as answer by Vineet Ahuja Monday, June 27, 2016 8:05 AM
    Monday, June 27, 2016 8:05 AM

All replies

  • Maybe something like this?

    Sub RefreshConnections()
        Dim iConnection As WorkbookConnection
        
        For Each iConnection In ThisWorkbook.Connections
            iConnection.Refresh
            DoEvents
        Next iConnection
    End Sub
    


    http://www.ambienteoffice.com.br - http://www.clarian.com.br

    Wednesday, June 22, 2016 10:11 AM
  • Hi Felipe,

    The problem is still occurring.

    Before you sent me your solution, I had tried this:

    Sub Refresh()
          ' Select cell A1, *first line of data*.
          Range("A1").Select
          ' Set Do loop to stop when an empty cell is reached.
          Do Until IsEmpty(ActiveCell)
             ' Insert your code here.
             ' Step down 3 rows from present location.
             ActiveWorkbook.Connections(ActiveCell.Value).Refresh
             DoEvents
             ActiveCell.Offset(3, 0).Select
          Loop
       End Sub

    What I find is (both in my method and yours), is that all the queries seem to get updated, but after that the memory and the cpu usage go up a lot. CPU usage goes to 50% and memory to 2.5 - 3 Gigs. Then the program is just stuck in limbo. It keeps at something with high cpu usage, but I'm not sure what it is.

    I also removed all the processing that occurs after the queries are updated to ensure that there is no problem in the steps that follow the queries. But it still did not work.(ie memory and cpu usage go through the roof)

    Any ideas?

    Thanks,

    V

    Wednesday, June 22, 2016 9:21 PM
  • Some toughts coming in my mind:

    -Excel 32 bits can handle a maximum of ~1,4GB of memory. Try installing Excel 64 bits.

    -Try setting the property ForceFullCalculation to True (Alt+F11, Ctrl+R, click on ThisWorkbook component, press F4 to show properties, find ForceFullCalculation property and set to True, save, close and reopen Excel)

    -Break your workbook in more files... maybe it is too heavy. Is it possible?


    http://www.ambienteoffice.com.br - http://www.clarian.com.br

    Wednesday, June 22, 2016 9:27 PM
  • >>>The first row is the stock symbol. The second and third rows are the result of the query (and stripping of irrelevant data) from finance.yahoo . And the pattern repeats some 70 times.

    According to your description, you could amend below code to suit your needs:
    Sub DemoWebQuery()
        Range("A1").Select
        Selection.QueryTable.Refresh BackgroundQuery:=False
        RunOnTime
    End Sub

    Thursday, June 23, 2016 1:42 AM
  • I have already set all the connection BackGroundQuery property to False.

    What is RunOnTime? (I couldn't find any information about it)

    Thanks,

    V

    Thursday, June 23, 2016 4:17 AM
  • 1. I have the feeling that going to Excel 64 bits is overkill for 70 odd connections. Wouldn't you agree?

    2. I tried ForceFullCalculation, but the same problem repeats

    3. My workbook has 3 sheets and it size on disk is 253kb, so I don't think that is the problem.

    Could it be that after all the queries are updated, Excel is trying to close all connections (or some kind of cleanup of all connections) and that is leading to this issue. Is there a way to cleanup the connections individually in the loop?

    Thanks,

    V

    Thursday, June 23, 2016 4:23 AM
  • Suggestion (3) is for you to break 70 connections into 7 files. So each file would have only 10 connections and would not crash.

    Is it possible for you to share the workbook here or does it contains confidential data? I don't have more ideas.


    http://www.ambienteoffice.com.br - http://www.clarian.com.br

    Thursday, June 23, 2016 12:45 PM
  • Your code seems to just keep opening connections. Try opening one connection, do stuff, then close that one before you open the next.
    Thursday, June 23, 2016 1:05 PM
  • Dogubob,

    This is my first program in VBA so I don't know how to do that. Would you mind sharing some sample code with me?

    Thanks,

    V


    Thursday, June 23, 2016 1:47 PM
  • Felipe,

    I think a better method is to have 2 subroutines. RefreshAll and RefreshSingle. RefreshAll can loop over all the active connections and pass them one at a time to RefreshSingle. RefreshSingle will just refresh the individual connection and exit. So, at any given time, there is only one connection that is open.

    The problem is I'm not familiar enough with VBA to do that. If you know, can you help?

    Thanks,

    V

    Thursday, June 23, 2016 1:51 PM
  • I just tried this but it did not work. Any ideas on why this is not working?

    Sub RefreshAll()
          ' Select cell A1, *first line of data*.
          Range("A1").Select
          ' Set Do loop to stop when an empty cell is reached.
            Do Until IsEmpty(ActiveCell)
             ' Insert your code here.
             ' Step down 1 row from present location.
             ' ActiveWorkbook.Connections(ActiveCell.Value).Refresh
             RefreshSingle (ActiveCell.Value)
             ActiveCell.Offset(3, 0).Select
          Loop
       End Sub

    Sub RefreshSingle(Conn As String)
            ActiveWorkbook.Connections(Conn).Refresh
            DoEvents
    End Sub

    Thursday, June 23, 2016 2:25 PM
  • I have found a solution. After running the loop that refreshes the connections, I inserted the following code that saves all open workbooks and quits the application:

    For Each w In Application.Workbooks w.Save Next w Application.Quit

    Its not the best solution but it certainly meets my needs.


    • Marked as answer by Vineet Ahuja Monday, June 27, 2016 8:05 AM
    Monday, June 27, 2016 8:05 AM
  • Hi Vineet Ahuja,

    Congratulation, we are glad to hear that you have resolved your issue. Thanks for your sharing this solution, it will help other community when they have same issue.
    Monday, June 27, 2016 9:15 AM