Named ranges

[HSalim[MVP]]

Hi All,
I am using the offset function to define a named range. It is
=OFFSET('ledger'!$A$2,0,0,COUNTA('ledger'!$A:$A),2)
While it is not listed in the Name box as a choice for dropdown items, it is
accessible if you type it in.

When I access this range using ODBC, this range is not visible. However,
"normal" ranges, i.e. those visible in the Name Box are seen through ODBC.
Any ideas on how to make that special range visible via ODBC?

Regards
Habib

Microsoft MVP - Great Plains

 

[OJ]

Hi,
I dunno why it isn't visible but have you tried using another name to
reference the dynamic name? Is that name visible?

ie
Name1 = "=OFFSET('ledger'!$A$2,0,0,COUN­TA('ledger'!$A:$A),2)"
Name2 = Name1

Hth,
O

 

[Arvi Laanemets]

Hi

IMHO you can use only non-dynamic named ranges, defined as direct cell
references, as source tables for ODBC query. I always define ranges, I
assume to use as query source tables, with a lot of spare rows - you can
always determine in filtering criterias some key column(s) to be Not Null.

There are some other rifes with queries from Excel files too. P.e. I had the
situation, where I had a lot of dynamic ranges defined, and when I defined a
fixed named range, with name beginnng with "T" (i.e. it was at bottom of
named ranges list), it wasn't accessible for ODBC. It looks like the number
of named ranges, from where applicable for ODBC are searched for, is
limited - when I started the range name with other character, so it moved up
in ranges list, then from certain position (it was somewhere between12 and
20 - don't remember exactly) it was accessible for ODBC again.

Another quirk - when you create an ODBC query from range in same workbook,
then as I found out lately, you must have Analysis Toolpack Add-in installed
(I suspected MS Query Add-In at start, but it really looks like you need
Analysis Toolpack!!!???). Otherwise you can create the query, but you'll
have problems after you close the workbook and reopen it later, When
refreshing the query, the query wizard opens, and you are asked for source
again and again - unless you cancel.

 

[HSalim[MVP]]

Hi OJ
Yes, I tried that too. No dice.
Habib

Hi,
I dunno why it isn't visible but have you tried using another name to
reference the dynamic name? Is that name visible?

ie
Name1 = "=OFFSET('ledger'!$A$2,0,0,COUN­TA('ledger'!$A:$A),2)"
Name2 = Name1

Hth,
O

 

[Jamie Collins]

IMHO you can use only non-dynamic named ranges, defined as direct cell
references, as source tables for ODBC query.

Either a TABLE or a SYSTEM_TABLE may be used in a query using the ODBC
driver. The same applies to the OLE DB providers, accept they cannot
differentiate between TABLE or SYSTEM_TABLE.

There is only one definition for TABLE, being a workbook-level defined
Name ('named range'), defined using a simple formula to return the
range e.g. =Sheet1!A1:B4. I assume the driver/provider reads the
address used in the formula (but at a low level). Cells are not
calculated when data is accessed using Jet e.g. you can change a
precedent but the formula cell's value will not reflect the change
until it is opened and calculated in the Excel UI (also, a formula cell
cannot be changed using Jet). Therefore, a dynamic range which relies
on the result of a formula will not be seen as a TABLE.

There are three definitions for SYSTEM TABLE

1) a worksheet-level defined Name, again defined using a simple formula
to return the range e.g.

SELECT * FROM [MySheet$MyDefinedName];

2) a worksheet e.g.

SELECT * FROM [MySheet$];

The UsedRange (at a lower level than VBA) determines the table bounds.

3) an absolute range address, either with an explicit sheet name e.g.

SELECT * FROM [MySheet$A1:B4];

or with the sheet name omitted e.g.

SELECT * FROM [A:B];

in which case the worksheet at position Worksheets(1) will be used
regardless of visibility.

Jamie.

--

 

[HSalim[MVP]]

Arvi,
Thanks for the info.
The problem is that I want to set up a foolproof method that offers a
consisntent result.
Naming large ranges does not work because users can delete rows, resetting
the range.
However,defining the range as 'ledger'!$A:$R may do the trick.

Regards
Habib

 

[Arvi Laanemets]

Hi

Arvi,
Thanks for the info.
The problem is that I want to set up a foolproof method that offers a
consisntent result.
Naming large ranges does not work because users can delete rows, resetting
the range.
However,defining the range as 'ledger'!$A:$R may do the trick.

Better write a workbooks BeforeSave event, which rewrites the named range
definition and sets it to (used range + N ) rows, whenever the user wants to
save changes (what he does during the session without saving, doesn't
matter, as query takes data from file on disk, and the user works with
temporary file). I'm afraid references to whole columns don't work.

Or use Jamie's suggestion and refer to absolute range address. You can
combine it with a bit of code, where the range address in query definition
is adjusted with VBA, before the query is processed. But I can't help here -
haven't never used this technique myself.

 

[Jamie Collins]

use Jamie's suggestion and refer to absolute range address.

Using just the worksheet e.g.

SELECT * FROM [Sheet1$];

usually does the same trick as a dynamic range because it uses the
UsedRange.

Specifying a range address larger than the UsedRange will result in
only the intersection being returned e.g. if UsedRange = A1:E4 then
querying A1:B65535 will only return 2 columns and 4 rows (assuming no
header row).

I'm afraid references to whole columns don't work.

You can specify whole column(s) as the table e.g.

SELECT * FROM [Sheet1$A:C];

Again, you only get the intersection with the UsedRange.

HTH,
Jamie.

--

 

[HSalim[MVP]]

Jamie and Arvi,
Thank you bot for your suggestions and input.
I want to avoid the select * from [sheet1$] approach if I can, so I'll try
out your suggestions and post an update later.

Thanks and regards
HS

use Jamie's suggestion and refer to absolute range address.

Using just the worksheet e.g.

SELECT * FROM [Sheet1$];

usually does the same trick as a dynamic range because it uses the
UsedRange.

Specifying a range address larger than the UsedRange will result in
only the intersection being returned e.g. if UsedRange = A1:E4 then
querying A1:B65535 will only return 2 columns and 4 rows (assuming no
header row).

I'm afraid references to whole columns don't work.

You can specify whole column(s) as the table e.g.

SELECT * FROM [Sheet1$A:C];

Again, you only get the intersection with the UsedRange.

HTH,
Jamie.