Recently at work, I needed to bring together several Excel worksheets into one place for analysis.  I'm sure there are ways to do this without leaving Excel, but my thought was to link them all into an Access database which would allow me to set up static queries against the data that wouldn't need to be changed.  With all my analysis setup, my thought is to not need someone to understand much about it except they need to double-click here, or open a file there.  That way, when I move on, a monkey can do my job. 

Regardless, these worksheets had data starting on line 3, not line 1.  So I could not simply link to the worksheets.  I needed to make a named range to link to.  However, the number of lines that we need to link to changes for each worksheet daily.  I could put in a dynamic named range, but you cannot link to a dynamic named range, only a static named range.

I searched the web for a solution, but didn't find anything.  This is probably a testament to my search ability.  So, I thought of a possible solution.  Perhaps if I put in some VBA code which would change the range each time the file was saved, the 'dynamic' range I needed would look like a 'static' range to Access!

I put in the code, which involved creating a range object and naming it.  I think it went something like this, and I'll update when I get the chance to check it (it's all at work, can't blog at work!) in the before save sub.

Range("A3", Range("A3").End(xlDown).offset(0,ColumnsNeeded).address).name = RangeName

Basically, every time the file is saved, a static named range is created which you can set up a link to through Access.  The only downside is that in Excel 2007, you need to make it a macro-enabled workbook.  I got around that by using the old .xls format, which keeps from confusing the people who don't need to know about it.