Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Dear community,
Loading Excel is so much fun. Though since 2003 we began to see a new type of object called ListObject. This object is quite similar to a named range but is in fact publicly named a Table. Since then many users are using this object. I can't find another parameter in the statement for file definition.
While loading Named range is totally fine, loading a Table (or ListObject in VBA) seems impossible. Have you encounter this type of issue. How did you deal with that?
Much thanks in advance.
If you could elaborate it in more detail and provide a small example it will be helpful.
- Marcus
Hi Marcus,
Here is an Excel File.
You will find:
The Named Table (Table1) is not available as an option to be loaded in QlikView whereas the Named Range (Range1) is.
Thanks,
I think that these Table (ListObject) is no own data-area it's a feature (on a higher level) which will be applied to a data-area and therefore you couldn't access these virtual object directly.
But if you give these Table an additional name you could access it and a normal odbc-load on the sheet or a normale table-load will be work also:
ODBC CONNECT32 TO [DriverForExcel;DBQ=I:\MyPath\TestLoadListObjects.xlsx];
SQL SELECT Country,
Sales
FROM `I:\MyPath\TestLoadListObjects.xlsx`.`Sheet1$`;
SQL SELECT *
FROM `I:\MyPath\TestLoadListObjects2.xlsx`.NameForTable; // with a name for this ListObject
Directory;
LOAD A,
B,
C,
D,
E,
F
FROM
(ooxml, no labels, table is Sheet1);
- Marcus
Hi Markus,
Thank you for your reply.
Unfortunately, here is a very quick example and the users will have to manage multiple Named Range to apply on top of the Named Table which is not helpful as the table can extend automatically without the range be extended.
I guess you confirmed I have to find another option to deal with that.
Thank you!
The excel name-ranges could be dynamically with an expression like this:
offset(Sheet1!$A$1;;;CountA(Sheet1!$A:$A);CountA(Sheet1!$1:$1))
See also: Offset Function in Excel - Easy Excel Tutorial
- Marcus
Hi Markus,
I didn't know this function thanks. After exploring it, I can say it won't work. Thank you thou on that matter.
Best,
For me these methods worked very well since years - I use it for all my dynamic data-areas, mostly for pivot-tables. Therefore have a further look to the idea - maybe I have made an error by choosing/translating the right function/syntax - in german in function will be called as Bereich.Verschieben().
- Marcus