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.
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?
Thanks for your help,
Dale
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 tdfThis 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"tdf.SourceTableName = "'1234'$MyRange"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
sheet names should start with a letter (as opposed to number or special character) for best success
ASKER
@Crystal,
I know, and should not contain spaces either, but at some point in the recent past MS changed that naming convention and started allowing numbers and names with spaces.
@Norie,
I'll give it a try and get back to you.
I know, and should not contain spaces either, but at some point in the recent past MS changed that naming convention and started allowing numbers and names with spaces.
@Norie,
I'll give it a try and get back to you.
ASKER
@Norie,
Nope, that didn't do it. Thanks for the suggestion, though.
Anybody else care to join in?
Nope, that didn't do it. Thanks for the suggestion, though.
Anybody else care to join in?
Dale
It worked for me.
What exactly did you try?
It worked for me.
What exactly did you try?
Didn't notice you were the one asking, Dale; I know you know better ; ) Like in Access, even tho it is allowed to make a tablename starting with a number, it does hurt!
>> 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 <<
I have seen range names get duplicated when a sheet is copied.
what about using square brackets around ... [sheetname$rangename]
>> 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 <<
I have seen range names get duplicated when a sheet is copied.
what about using square brackets around ... [sheetname$rangename]
ASKER
Norie,
Did you use TransferSpreadsheet, or the tabledef method described above? Appreciate your comment about the Name Manager, I don't spend a lot of time in Excel, and have never actually noticed it. Love to learn something new. Can you package up your Access code into a blank database along with the spreadsheet you are importing from and post it here?
I tried it like:
tdf.SourceTableName = "'1234$'MyRange"
Crystal, Have not tried it bracketed like that, but did try [1234]$RangeName. At this point, I'll try anything.
Thanks,
Dale
Did you use TransferSpreadsheet, or the tabledef method described above? Appreciate your comment about the Name Manager, I don't spend a lot of time in Excel, and have never actually noticed it. Love to learn something new. Can you package up your Access code into a blank database along with the spreadsheet you are importing from and post it here?
I tried it like:
tdf.SourceTableName = "'1234$'MyRange"
Crystal, Have not tried it bracketed like that, but did try [1234]$RangeName. At this point, I'll try anything.
Thanks,
Dale
ASKER CERTIFIED SOLUTION
membership
Create a free account to see this answer
Signing up is free and takes 30 seconds. No credit card required.
ASKER
Crystal,
Thanks for the tutorial on Named Range, it's what you do!
Still no joy with [sheetname$RangeName], [SheetName]$[RangeName]
This is obviously about the reference to the sheet, when it is numeric, and now I've confirmed that it causes problems when the sheet name contains spaces as well.
Thanks for the tutorial on Named Range, it's what you do!
Still no joy with [sheetname$RangeName], [SheetName]$[RangeName]
This is obviously about the reference to the sheet, when it is numeric, and now I've confirmed that it causes problems when the sheet name contains spaces as well.
you're welcome, Dale, and thanks. Sadly, you are allowed to do things that bite ~
(btw, the Name Manager window is resizeable, as are the columns -- you can also create a name that refers to a value, not a range -- then it is more like a global variable you can also use in formulas)
fwiw, I created a workbook with a worksheet with a range name and copied the sheet to get a duplicate. I then linked, manually. Here is the SourceTableName: '1234$'MyRangeName
I also tried manually linking to a (duplicate) range name on a sheet with a period in the sheetname. Access converted . to # for display but reported an error ...of course! The sheet name was wrong -- and wouldn't link to it.
Linking to a (duplicate) range name on a sheet with a space in the middle of the sheetname was ok:
'temp 2$'MyRangeName
(btw, the Name Manager window is resizeable, as are the columns -- you can also create a name that refers to a value, not a range -- then it is more like a global variable you can also use in formulas)
fwiw, I created a workbook with a worksheet with a range name and copied the sheet to get a duplicate. I then linked, manually. Here is the SourceTableName: '1234$'MyRangeName
I also tried manually linking to a (duplicate) range name on a sheet with a period in the sheetname. Access converted . to # for display but reported an error ...of course! The sheet name was wrong -- and wouldn't link to it.
Linking to a (duplicate) range name on a sheet with a space in the middle of the sheetname was ok:
'temp 2$'MyRangeName
Dale
I used the code you posted, I just adjusted line 7.
I used the code you posted, I just adjusted line 7.
ASKER
Norie,
What version of Access?
What version of Access?
ASKER
Crystal, What version of Access was that last test in?
I'm currently working in 2007 because a client with their 50+ employees is still using 2007. I keep telling them that it is time to upgrade, but it's a cost thing, and they honestly cannot see what the later versions of Access give them that they need.
Dale
I'm currently working in 2007 because a client with their 50+ employees is still using 2007. I keep telling them that it is time to upgrade, but it's a cost thing, and they honestly cannot see what the later versions of Access give them that they need.
Dale
Dale
I'm currently using Access365/2016 but I think I was able to deal with a similar issue in earlier versions.
Have you tried manually setting up a link to one of the problem ranges and then examining it's TableDef?
I'm currently using Access365/2016 but I think I was able to deal with a similar issue in earlier versions.
Have you tried manually setting up a link to one of the problem ranges and then examining it's TableDef?
ASKER
Norie,
Yes, but 2007 does not recognize duplicates, so I'm having to deal with that as well. Will do some more testing thus evening.
Yes, but 2007 does not recognize duplicates, so I'm having to deal with that as well. Will do some more testing thus evening.
By duplicates do you mean duplicate named ranges?
Dale, it is 2013
ASKER
@Norie,
Yes, Access 2007 does not seem to recognize duplicate named ranges (with worksheet scope).
NamedRanges.jpg
ExternalDataNamedRanges1.jpg
ExternalDataNamedRanges2.jpg
Yes, Access 2007 does not seem to recognize duplicate named ranges (with worksheet scope).
NamedRanges.jpg
ExternalDataNamedRanges1.jpg
ExternalDataNamedRanges2.jpg
Dale, since the duplicates show, just not the scope .. try picking each and examine SourceTableName and data - could be the wizard got an uplift but not capabilities
Which version of Excel is being used?
ASKER
2007 Excel and Access.
Crystal,
I did that, in 2007 they both import the first instance of the named range.
I'm inclined to deduce that this is a bug in 2007, but I'm going to move my code to 2010 and test it there.
If it works there, I'll just add a msgbox to the error handler to advise the user to change the name of the named range if there are duplicates, and change the name of the worksheet if selecting from a range, but the worksheet name is numeric or contains a space.
Crystal,
I did that, in 2007 they both import the first instance of the named range.
I'm inclined to deduce that this is a bug in 2007, but I'm going to move my code to 2010 and test it there.
If it works there, I'll just add a msgbox to the error handler to advise the user to change the name of the named range if there are duplicates, and change the name of the worksheet if selecting from a range, but the worksheet name is numeric or contains a space.
yes, let the user fix it ... get them into better naming habits :) show ranges that are valid to pick from ... and ranges that need to be renamed if the data is wanted. Space and number are not the only problems ... period is a problem and so are some of the other special characters such as square brackets. I can post code to fix bad characters if you ask.
Luckily when a range is named through the user interface, Excel does not allow it to be a bad name.
>> both import the first instance <<
thanks for sharing that
>> bug in 2007 <<
or rather, I would assume 2007 didn't yet have ability to import from a range with worksheet scope using the user interface (and maybe also programmatically?).
Luckily when a range is named through the user interface, Excel does not allow it to be a bad name.
>> both import the first instance <<
thanks for sharing that
>> bug in 2007 <<
or rather, I would assume 2007 didn't yet have ability to import from a range with worksheet scope using the user interface (and maybe also programmatically?).
ASKER
Crystal,
"or rather, I would assume 2007..."
maybe, but Excel 2007 had the ability to name worksheet scoped ranges, so if Access didn't have that feature, "I" would call it a bug.
Dale
"or rather, I would assume 2007..."
maybe, but Excel 2007 had the ability to name worksheet scoped ranges, so if Access didn't have that feature, "I" would call it a bug.
Dale
but perhaps it is just the wizard (almost all of them are outdated), not the ability ... can you do it with code using names with normal characters?
SourceTableName can be a range name (but, as you saw, it needs to be unique so it is/WAS a name with workbook scope). If SourceTableName is a sheetname, it ends with $ ... and further, if a specific range on a sheet, that can be specified after $
i get the bug idea since you shouldn't see the dups unless you also have indication of scope ... but since this is fixed in later versions (well except for bad characters -- that is training), it is hardly worth reporting, imo more important things need to be done
SourceTableName can be a range name (but, as you saw, it needs to be unique so it is/WAS a name with workbook scope). If SourceTableName is a sheetname, it ends with $ ... and further, if a specific range on a sheet, that can be specified after $
i get the bug idea since you shouldn't see the dups unless you also have indication of scope ... but since this is fixed in later versions (well except for bad characters -- that is training), it is hardly worth reporting, imo more important things need to be done
ASKER
Crystal,
I tried the tabledef and transferspreadsheet methods in 2007, and neither method would handle the mal-formed sheetname when importing or linking to a named range. Both methods will work if the sheet is named properly. But the wizard will not link or import named range associated the 2nd or subsequent worksheet, if the range is scoped as a worksheet.
Dale
I tried the tabledef and transferspreadsheet methods in 2007, and neither method would handle the mal-formed sheetname when importing or linking to a named range. Both methods will work if the sheet is named properly. But the wizard will not link or import named range associated the 2nd or subsequent worksheet, if the range is scoped as a worksheet.
Dale
ASKER
Thanks for your help with this Crystal.
I've modified my code so that it handles the error by displaying a message to the users about changing the name of the range to start with an alpha character, and to remove all spaces from the named range name.
sorry for the delay on closing this.
I've modified my code so that it handles the error by displaying a message to the users about changing the name of the range to start with an alpha character, and to remove all spaces from the named range name.
sorry for the delay on closing this.
glad to hear, Dale ~ you're welcome, happy to help
Sounds like these named ranges have worksheet rather than workbook scope.
The scope of a named range can be set when you create it with the Name Manager.
If you have numeric sheet names try this.
Open in new window
[/code]