I am trying to figure out how to import ListObjects in PP.

As far as I can tell PP does not seem to "see" range names that refer to entire ListObjects i.e. =T_DTL[#All], while it "sees" ranges that refer to direct cells i.e. =Dtl!$O$3:$V$46.

Is that correct and is there a way to work around it?

The reason I am asking is that the tables I want to work with do not necessarily start on line 1 and PP references entire worksheets i.e. Dtl$, as per my example above.I tried different approaches with dynamic ranges but PP refuses to present them as choices while Selecting Tables and Views. The only way to achieve what I need is by creating named ranges with hardcoded start and end, which is a bit odd since Tables (ListObjects) are a powerful tool since Excel 2007 and I would expect a stronger integration with other powerful tools such as PowerPivot.Apart from the overhead of having to go through each workbook and define a range for every Table I wish to use.

Am I missing something here?

• Edited by George Thalassinos Thursday, January 22, 2015 9:31 AM

I don't follow all of what you are trying to do but to refer to ListObjects (table) try something like this

Dim lo As Excel.ListObject
Dim ws As Excel.Worksheet

Set lo = ws.ListObjects("myTable")
'or
Set lo = ws.ListObjects(1)
lo.DataBodyRange.Activate ' the sheet must be active

• Edited by Peter Thornton (Excel MVP 2008-13) Thursday, January 22, 2015 10:35 AM

Hi Peter, it is evident I was not clear.

I am trying to import an Excel ListObject into PowerPivot.

But when I link to the .xlsm file in the PowerPivot window (of a blank file where I will build my model) and I am presented with the Tables and Views to choose from, the ListObjects of the .xlsm file are missing, just the named ranges I have defined with a classic notation (i.e. $C$3:$AB$500) are there. And any attempt to define a named range using the name of a ListObject results in a range that PowerPivot refuses to present as available to choose.

I hope I am making more sense now :-)

Peter's approach to identify "MyTable" is eminently reasonable.
Did you try it and what were the results?
You might also want to share your ??.xlsm file.

My PowerPivot attempt was abruptly broken about a month ago, hence my very late reply.

Peter is quoting VBA statements on how to refer to a Table (ListObject), which is familiar to me as I am also writing VBA code. Unfortunately my problem is in interactive mode, not in code execution.

When we open a file's PowerPivot window from within Excel we are prompted to select the tables we want to work with. That is where I cannot see my ListObjects / Tables. I am presented with all ranges that PowerPivot sees as declared in the file that I am opening (as in Formulas / Names Manager) EXCEPT for the ListObjects. Those are missing.

Try it if you will and let me know if this behavior is relevant only to my installation or to yours as well.