We help IT Professionals succeed at work.
Get Started

using CreateTabledef to link to Excel Named Range

Last Modified: 2017-09-10
I have the following code which creates a linked table in Access with a source that is the named range in an Excel workbook.  
Dim db as DAO.Database
Dim tdf as DAO.Tabledef

Set db = CurrentDb
Set tdf = db.CreateTableDef("xl_NamedRange")
tdf.Connect = "Excel 12.0;HDR=YES;IMEX=2;ACCDB=YES;DATABASE=" & strExcelFileName
tdf.SourceTableName = "MyRange"
db.TableDefs.Append tdf

Open in new window

Some of you will ask, why not simply use the TransferSpreadsheet method?  The answer is that any use of TransferSpreadsheet method will display the Access NavPane, even if it is disabled in the Startup options.  I know I could simply hide it again, but not without some screen flicker, and I want to avoid that.

This code works fine, for the most part.  However, I've encountered a couple of workbooks that have named range names which look like:
I honestly don't know how the creator of the workbook I'm importing from was able to create two ranges in the same workbook with the same name, but they did.  In my application, I loop through the Names collection of the selected workbook and capture these values in a table and this process returns the above values, where the numbers inside the single quotes are actually the tab names.  When the tab name is alphanumeric, and not just numeric, the Name.Name property might look like:
and simply changing this to:
tdf.SourceTableName = "MyTabName$MyRange"

Open in new window

allows the code to link the range properly.  However, when the workbooks tab name is numeric, this does not work:
tdf.SourceTableName = "'1234'$MyRange"

Open in new window

and neither does
tdf.SourceTableName = "1234$MyRange"

Open in new window

Does anyone know:
1.  How to create two named ranges in a workbook with the same Name, although the sheet they belong to is different?
2.  How to properly reference the range Name when the tab holding the range has a name which is numeric?

Thanks for your help,
Watch Question
Remote Training and Programming
Top Expert 2015
This problem has been solved!
Unlock 1 Answer and 27 Comments.
See Answer
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE