SQL "INSERT INTO" Does not Modify Definition of Target Range
 

I am using MS Excel 2002 to manage financial data.

To add new data to a named range in a spreadsheet, I have
written a query that uses the SQL "INSERT INTO" capability.

Works well, except for two problems. For this Post, the
problem I'm trying to solve is that the update query does
add records to the spreadsheet as expected, but it leaves
the definition of the named range unchanged. E.g.,
suppose that the named range originally covers A1:D35, and
the update query adds 7 records to the spreadsheet. I
would have expected the query to also modify the
definition of the named range to cover A1:D42.

If I perform the same function using the "Execute SQL"
function from inside Microsoft Query, it does modify the
definition of the named range to include the added
records. But I can't use this capability in a macro, or
at least I can't see how to do that.

Any thoughts on how to get my query to automatically
update the definition of the target range to include added
records?

Thanks,
David