?Use Dynamic Named Range as Table / DataSource in MS Query?

[Default300]

QUESTION:
Is it possible to use an Excel Dynamic Named Range as a Table / DataSource in MS Query?

AIM:
I would like to use a Dynamic Named Range to trim superfluous (blank) rows from the Table before MS Query queries it.

I was able to achieve close to what I wanted with a Static Named Range Table, but I had to use a criterion in MS Query, ie "exampleField Is Not Null", to trim the blank rows. With this method, about 2x to 5x more records had be passed to MS Query to filter.

PROBLEM:
Unfortunately, when I use the Query Wizard, it cannot "see" the Dynamic Named Ranges, only the Static ones. That is, it is not one of the "available tables and columns".

I also tried rewriting the .dqy query file with Notepad, but with no luck.

BACKGROUND:
The query type is a "Database Query", created from the Excel user interface, and the MS Query Wizard.

Data is being brought from Excel to MS Query and back to Excel. Same workbook actually! So, if relevant, the Source workbook will be open when the Target refreshes data through MS Query.

One of my posts last week goes into the rationale for this [ http://www.mrexcel.com/forum/showthread.php?t=459189 ]. And details previous methodologies, including AdvancedFilter, Column Sorts and Row Sorts.

POSSIBLE LEADS:
I don't mind using VBA if that's the way to go. I hate the user interface of MS Query anyway.

When searching for a solution on the web, I came across this thread on Ozgrid. The person was using ADO, which Im fairly sure I'm not (my .dqy file, says ODBC). The SQL looks a little too scary (though VBA did too a year ago). But I wondered about what was suggested in the last post:

???
Could one base the query on a Static Named Range and immmediately prior to refresh, programatically Set the rngStaticNamedRange = (current state of) rngDynamicNamedRange?

NB:
I will always Refresh the Query Programmatically anyway.
And I will try to ensure that the Dynamic Named Range doesn't change during the Refresh procedure.

http://www.ozgrid.com/forum/showthread.php?t=72973

http://www.tek-tips.com/viewthread.cfm?qid=1478218&page=110


QUERY TEXT:
This is the .dqy file that works for the static range but not for the dynamic. The only thing I changed in it was the name of the table / range. I also changed the name of the file to the most recent version, which has the dynamic range in it, though as far as I know that is irrelevant anyway.

Sorry re the format. It's pasted from Notepad, with no wordwrapping. Looks fine composing it but the preview doesn't look promising!! I don't dare put in underscores. It may be easier to read if pasted into a text editor.

There's not much to it though. It's a very simple query. All columns, and records, of the original table / range are included. One minor Filter (mentioned above) is applied if blank records must be imported. Records are sorted on one to five Fields*. And Columns are reorganised for improved appearance. (More info in previous post mentioned.)

[FONT=Courier New]
XLODBC
1
DBQ=L:\MYPATH\Statistics Form (768).xls;DefaultDir=L:\MYPATH;Driver={Microsoft Excel Driver (*.xls)};DriverId=790;FIL=excel 8.0;MaxBufferSize=2048;MaxScanRows=8;PageTimeout=5;ReadOnly=1;SafeTransactions=0;Threads=3;UserCommitSync=Yes;
SELECT namClientInfoCloneFromDataEntryTableInclHeaderDynamic.RecordIndex, namClientInfoCloneFromDataEntryTableInclHeaderDynamic.A, namClientInfoCloneFromDataEntryTableInclHeaderDynamic.F, namClientInfoCloneFromDataEntryTableInclHeaderDynamic.Forename, namClientInfoCloneFromDataEntryTableInclHeaderDynamic.Familiar, namClientInfoCloneFromDataEntryTableInclHeaderDynamic.Surname, namClientInfoCloneFromDataEntryTableInclHeaderDynamic.L3, namClientInfoCloneFromDataEntryTableInclHeaderDynamic.L2, namClientInfoCloneFromDataEntryTableInclHeaderDynamic.L1, namClientInfoCloneFromDataEntryTableInclHeaderDynamic.MRN  FROM namClientInfoCloneFromDataEntryTableInclHeaderDynamic namClientInfoCloneFromDataEntryTableInclHeaderDynamic  WHERE (namClientInfoCloneFromDataEntryTableInclHeaderDynamic.RecordIndex Is Not Null)  ORDER BY namClientInfoCloneFromDataEntryTableInclHeaderDynamic.Familiar, namClientInfoCloneFromDataEntryTableInclHeaderDynamic.Forename, namClientInfoCloneFromDataEntryTableInclHeaderDynamic.Surname


RecordIndex    A    F    Forename    Familiar    Surname    L3    L2    L1    MRN
[/FONT]

Need to get some sleep now! Up again in three hours for my real job (as opposed to this very interesting and challenging hobby)!

By the way, I have resolved the question I requested help with in the previous post. [ http://www.mrexcel.com/forum/showthread.php?t=459189 ] I'll write it up when I get a chance, in case others are interested, or having the same problem.

 

[Default300]

Actually, I just had an idea...

I've never used the Resize VBA method.
Nor have I used the XLEndUp thing.

Would either of these work if the dynamic range is a no-go area?

All blank rows are already at the bottom of the Static Range, ie beneath the rows with useful data. The Static Range is a "drop zone" ie CopyToRange for an AdvancedFilter, and it allows for posssiblity of no filters being applied, and thus is large enough to hold the maximum amount of data.

Now definitely must sleep! Just 2.5 hours left before my alarm. Thanks in advance to anyone who been down this road before, hit this junction, and found the best way to go.