Parametrized value does not always refresh query

[DanL]

I am using MS Query to return a table of data within Excel 2007. The source
data for the query is a named range within the same Excel file, and the named
range is actually a table (which is set to refresh when the workbook is
opened).

The query has a parametrized value that is getting its value from a cell in
the same worksheet. I have set the properties of the parameter value to
"Refresh automatically when cell value changes".

This works correctly most of the time--the query updates when I change the
cell value.

However, when I save the workbook, open it, save it again without changing
the cell, and reopen it, the query does not refresh automatically. If I
close the file without saving and reopen it, the query still does not refresh
automatically. However, if I change the value of the cell, manually refresh
the table that is used as the named range in the query, save the file, and
then reopen it, the query updates automatically again.

Basically, when I open the workbook if I see the table used in the named
range refreshing, I know the query will refresh when I change the
parametrized cell. But if I don't see the table refreshing, the query won't
refresh either.

Is this a bug? Am I doing something wrong?

 

[BIAKathy]

I believe that this is a Bug. I am having the same problem. It works
everytime in 2003 but not in 2007. I have a parameter using the validation
which is a list which is populated by it's own query. If I create the report
in 2003 and save it it will work every time in 2007. If I make a
modification to the worksheet and save it in 2007 it will no longer work. I
have found that if I make the change in 2003, run the query with all my
parameters, delete the data saving the query, reset all my parameters and
then save it will then again run in 2007. I have tried to create it directly
in 2007 but my validation list looking at it's own query will never do an
automatic refresh. I am fortunate to have use of both 2003 and 2007, not
sure what I would do if I didn't. I am planning on submitting this to
Microsoft as a bug.