MSQuery and dynamic names (All?)

Topic

New Reply

I don’t know how useful this will be, but thought I’d post it anyway as I haven’t seen it mentioned anywhere before.

If you are using MSQuery to retrieve data from an Excel workbook (or using the Import Data function rather than the New Database Query function), then any named ranges that have been defined dynamically will not appear as tables. For example, you have defined the name Export
as:

=OFFSET(Sheet1!$A$1,0,0,COUNTA(Sheet1!$A:$A),COUNTA(Sheet1!$1:$1))

Export will not appear as a table in the dialog. (The same seems to be true trying to use VBA/ADO to retrieve this based on my quick tests).

However, if you define an additional name, let’s say Export2
, as:

=EVALUATE(Export)

then this does appear in the MSQuery dialog and in the Import Data dialog as a valid table.

Any thoughts/comments/problems appreciated. Note: I have only tested this in Excel 2002, but have seen it mentioned as an issue in prior versions – I do not know if it was fixed in subsequent versions.

Reply | Quote

Replies

This looks like a great tip!

Shouldn’t it be =EVALUATE("Export")?

Note: it doesn’t work in the Dutch language version of Excel, probably because the EVALUATE macro function has a localized Dutch name while MS Query expects the English name,

Reply | Quote

It seems I spoke too soon – although it was working perfectly yesterday, it will not work at all today, whether I use EVALUATE(Export) or EVALUATE("Export")! I am trying to determine why…

Reply | Quote

No, I cannot get it to work now, which makes me think I must have messed something up while testing it yesterday and probably overwrote the definition of Export2 with a range reference.
So everyone please ignore my ramblings!!

Reply | Quote

It did surprise me, because Evaluate is in fact an Excel4 macro function, which is even more unlikely to be evaluated when you’re extraction data from an Excel book without Excel itself.
Would’ve been nice though!

Reply | Quote

I think I’ve gotten a bit carried away with EVALUATE since I realised that if you were doing dependent validation lists, you could use EVALUATE instead of INDIRECT and then your dependent lists could be defined dynamically. I guess I’ll just have to accept the fact that it’s not a cure for everything!

Reply | Quote