troubleshooting Question

using CreateTabledef to link to Excel Named Range

Avatar of Dale Fye
Dale FyeFlag for United States of America asked on
Microsoft AccessMicrosoft ExcelMicrosoft Office
27 Comments1 Solution380 ViewsLast Modified:
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
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:
'1234'!MyRange
'2345'!MyRange
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:
MyTabName!MyRange
and simply changing this to:
tdf.SourceTableName = "MyTabName$MyRange"
allows the code to link the range properly.  However, when the workbooks tab name is numeric, this does not work:
tdf.SourceTableName = "'1234'$MyRange"
and neither does
tdf.SourceTableName = "1234$MyRange"

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,
Dale
ASKER CERTIFIED SOLUTION
Join our community to see this answer!
Unlock 1 Answer and 27 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 27 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros