Is it possible to connect to Excel tables? I'm referring to the dynamic type supported properly since Excel 2007, not to named ranges defined using offset function or similar.
I have not had any luck so far. Tableau does not recognise the table when connecting, unless it happens to be the first data row it encounters which is no different to connecting to a sheet. I know that Tableau can connect to static named ranges, but when I create a dynamic named range defined as being equal to the table, it does not pick this up either.
In a separate thread (here) Joe Mako states that dynamic named ranges created using the offset function cannot be referenced as the JET engine does not support these so they cannot be referenced outside Excel. I do not use dynamic ranges much any more which are largely redundant since the dynamic table support in Excel 2007, but I suppose that the tables could be treated in the same way outside Excel?
However, Microsoft does appear to support tables at a higher level, since in Excel 2010 I have used MS Query to union multiple tables from the same excel file, using standard SQL code with the tables as the data sources. This does not appear to be supported in Excel 2007, or perhaps the machine I'm working on doesn't have the necessary add-ons.
Has anybody found any solutions?
Edit: Have attached Excel file showing examples of what works and what doesn't.