Using MS Query against a named data range

[DE_Tx]

I have run into a dilemma that I have not be able to solve. I frequently use MS Query to retrieve data from ODBC tables and wanted to use it in similar fashion to extract data from various sheets in an Excel file with multiple sheets acting similar to tables.
I am building this in Excel 2002 per the client's requirements.

The dilemma - if using a fixed named range, MS Query will see the named range and be able to use it in typical fashion. If the named range is dynamic, MS Query doesn't see the named range and thus it cannot be used.

Any ideas as to why this is so?

The scenario - data is being stored in an Excel file (28 columns x up to 10,000 rows and growing). I want to be able to extract a subset of the data (approximately 35 days worth of data and only specific columns) to another file for reporting purposes.

I don't want to have to manually update the fixed name range in the data file and would prefer it to be dynamic

I know that I can extract the data using VBA, but that is not my strong suit (but can manage).

I can utilize MS Access and link the Excel data file as a table and then use MS Query in Excel to run against the MS Access database. This might be the way to go since there will be multiple tables (e.g. actuals, schedules, employee master data, etc). I can use MS Query against various MS Access queries.

At this point, I am not sure of the best approach. I am comfortable in using both MS Access and Excel and I believe the client is as well.

Any suggestions would be welcome.

Administrators - if this needs to be moved to a more appropriate forum, please do so.

Tim

 

[jeffreyweir]

Hi Tim. I don't know why this happens. If I was you, I'd use a bit of VBA to take those dynamic names and make a non-dynamic name out of them on file close, and point the query at those non-dynamic names. Pretty easy to do, and I'm sure we can collectively help you to do it if you need help.

 

[jeffreyweir]

Note that manipulating the data with VBA instead of SQL will likely be much faster. By way of example, check out http://dailydoseofexcel.com/archives/2013/11/21/unpivot-shootout/ where you'll see under the heading '...and the winner is' that doing some complicated SQL on a big dataset took two minutes, vs 20 seconds to do the same thing using VBA and array manipulation.