Using a TableName instead of Named Range in Excel datasource SQL Query

Question

In Excel VBA, I am using Excel as datasource for some queries. I am using the query as

SQL = "Select * from   [NameRange1]"   - This works

But, I want to use a table name instead of Namerange(Excel 2007 Tables). How do I use that? I tried "select * from [Table1]" and I also tried creating a Namerange for this table1 and it didn't work either..

any ideas please?

Doug Glancy · Accepted Answer · 2010-07-31 05:23:25Z

1

You don't say whether you're using DAO or ADO to connect, but I don't think it matters. I don't think it's possible to refer to a Table. All the documentation I've seen lists three types of ranges: an entire sheet [Sheet1$], a named range[NameRange1], or an unnamed range [Sheet1$A1:B10].

I am able to give an Excel 2010 table a named range that includes the header and that works just like any other named range, and it changes size with the addition of rows. Make sure that if it's a sheet-level named range you do it like [Sheet1$NameRange1].

The best information I found on doing it with ADO is here: http://www.xtremevbtalk.com/showthread.php?t=217783

answered Jul 31, 2010 at 5:23

Doug Glancy

27.5k6 gold badges72 silver badges118 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

CrashOverride Over a year ago

Link no longer active

Collectives™ on Stack Overflow

Using a TableName instead of Named Range in Excel datasource SQL Query

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related