Excel Query

[nb4me]

When I save a range as a table or when I create a dynamic range it works perfectly.......but the range isn't available when I try to import that range(table) using microsoft query from another workbook. When I change it to a regular named range, It is on the query list and I can import it. Am I doing something wrong?

 

[Fazza]

You are doing nothing wrong. That is how it is. Dynamic (volatile) defined names don't work for querying.

VBA may be an option to create non-dynamic named ranges.
Or if the data has headers in row 1 & data directly underneath you can use a worksheet name. LIke this, FROM [worksheetname$]

cheers

 

[nb4me]

You are doing nothing wrong. That is how it is. Dynamic (volatile) defined names don't work for querying.

VBA may be an option to create non-dynamic named ranges.
Or if the data has headers in row 1 & data directly underneath you can use a worksheet name. LIke this, FROM [worksheetname$]

cheers

Hi Fazza,

Thanks so much for your reply. I was just wondering how to implement your suggestion (about worksheetname$) if I can't find the range on the list of available ranges to import.

Thanks again.

 

[Fazza]

I should have explained: near the beginning of the wizard there is a choice. Something like 'Options'. Select that & then 'system tables'. After OK'ing that, you will see worksheet names as well as non-dynamic defined names in the available data sources. (Or you can enter it directly in the SQL. Either in MS Query or via VBA.)