Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Named ranges
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Named ranges
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Named ranges
Hi OJ
Yes, I tried that too. No dice. Habib "OJ" wrote in message oups.com... 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Named ranges
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. -- When sending mail, use address arvil<attarkon.ee Arvi Laanemets "HSalim[MVP]" wrote in message ... 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Named ranges
Arvi Laanemets wrote:
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. -- |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Named ranges
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" wrote in message ... 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. -- When sending mail, use address arvil<attarkon.ee Arvi Laanemets "HSalim[MVP]" wrote in message ... 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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Named ranges
Hi
"HSalim[MVP]" wrote in message ... 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. -- When sending mail, use address arvil<attarkon.ee Arvi Laanemets |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Named ranges
Arvi Laanemets wrote: 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. -- |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Named ranges
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 "Jamie Collins" wrote in message oups.com... Arvi Laanemets wrote: 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. -- |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Named Ranges in VBA | Excel Discussion (Misc queries) | |||
Like 123, allow named ranges, and print named ranges | Excel Discussion (Misc queries) | |||
Named Ranges | Excel Programming | |||
named ranges - changing ranges with month selected | Excel Programming | |||
Named ranges | Excel Programming |