with excel 2003, I used to import tables, and then use the imported query
'range name' to do further analysis of the data using (dsum, dcount, ...)

eg.

result =
Application.WorksheetFunction.DCount(Range("sheet1 !query_range_name"), "age",
Range("sheet2!criteria_range_name"))

with excel 2007, the data can now only be imported as a 'table', and the
table 'range name' does not include it's headers, so the above code fails

Have also tried using the 'External Data Properties name' (which is the
range name that used to work in 2003) but this is not recognised either.

I know a possible work around is to create a new range name which includes
the headers and change it each time the table refreshes - but surely there
must be a method without this annoying workaround??

I solved it myself... when refering to the table as a named range use:

"sheet1!table_name[#All]"

this will include headers in the range.

can also refer to just columns of the table - refer this link:
http://www.jkp-ads.com/Articles/Excel2007TablesVBA.asp


"greg" wrote:

with excel 2003, I used to import tables, and then use the imported query
'range name' to do further analysis of the data using (dsum, dcount, ...)

eg.

result =
Application.WorksheetFunction.DCount(Range("sheet1 !query_range_name"), "age",
Range("sheet2!criteria_range_name"))

with excel 2007, the data can now only be imported as a 'table', and the
table 'range name' does not include it's headers, so the above code fails

Have also tried using the 'External Data Properties name' (which is the
range name that used to work in 2003) but this is not recognised either.

I know a possible work around is to create a new range name which includes
the headers and change it each time the table refreshes - but surely there
must be a method without this annoying workaround??