Refresh some web queries (but not others)

[ajsloss]

I'm using Excel2007 and have a workbook with a number of different web queries in it. Not all of the queries need to be refreshed everyday. Using the "Refresh All" button takes a long time, as does going through one by one. What I would like is a way for all of my queries to be listed in Column A. Then I will manually go through and put the day they need to be refreshed in Column B. In Column C, I will then run a formula that returns "yes" if the day in Column B is today and "no" if otherwise. Then, I would like another macro to refresh only the queries in Column A that also have a "yes" in Column C.

So to recap,
1. I'm looking for a macro that will list my web queries in Column A
2. A macro that will refresh only the web queries I need for that day

I would really appreciate the code, but I would really, really appreciate some commentary on what the code is doing and why it is doing that, so I can better understand how VBA works (I've seen some codes listed in the forums, but don't know how to modify them, because I'm not sure how they work).

Thanks in advance for your help.

 

[John_w]

See http://www.ozgrid.com/forum/showthread.php?t=89168&page=1 for looping through queries in Excel 2007.

I don't have Excel 2007. In Excel 2003 you refer to a query by its parent sheet name and the query name:

Dim qt As QueryTable

Set qt = Worksheets("Sheet_name").QueryTables("Query_name")
If Not qt Is Nothing Then
    qt.Refresh BackgroundQuery:=False
End If

This means in Excel 2003 each column A cell would need to store the sheet name and query name, however I'm not sure how the ListObject used by Excel 2007 affects the VBA code for referring to a query table and therefore what you'd need to store in each column A cell.