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.DatabaseDim tdf as DAO.TabledefSet db = CurrentDbSet tdf = db.CreateTableDef("xl_NamedRange")tdf.Connect = "Excel 12.0;HDR=YES;IMEX=2;ACCDB=YES;DATABASE=" & strExcelFileNametdf.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:
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?