using CreateTabledef to link to Excel Named Range

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

Select all 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:
'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"

Select all 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"

Select all Open in new window

and neither does

tdf.SourceTableName = "1234$MyRange"

Select all 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,
Dale