Named Range Issues

[Quehay]

I'm creating some Excel named ranges in VBA from a VB Script file. The names appear in the Insert>Define menus afterward but not in the names box. When I try to import the names from Access VBA they "can't be found" (and I'm saving the workbooks before attempting). Interactive, menu-driven Access import attempts don't show the named ranges either. There's no Refresh method for the names collection...

Any help would be much appreciated.

Thanks!


Jeff Roberts
Insight Data Consulting
Access and SQL Server Development

http://www.InsightDataConsulting.com

 

[SkipVought]

Hi,

So post a typical formula for one of your "Named Ranges" that is not working.

Skip,

Want to get great answers to your Tek-Tips questions? Have a look at faq222-2244

 

[Quehay]

DemographicsRegion = mWkSheet.Range"A1").CurrentRegion.address
mWkBook.Names.Add DEMOGRAPHICS,DemographicsRegion,TRUE


Jeff Roberts
Insight Data Consulting
Access and SQL Server Development

http://www.InsightDataConsulting.com

 

[PHV]

And what about this ?
mWkBook.Names.Add "DEMOGRAPHICS",DemographicsRegion,TRUE

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[SkipVought]

DITTO!

Skip,

Want to get great answers to your Tek-Tips questions? Have a look at faq222-2244

 

[Quehay]

The all caps should give-away that it's a CONSTANT! ;-)

I tested the import code with an interactively created named range, and it worked. The problem seems to be that Excel is not quite "cementing" the VBA-created ranges in place.

Again, I see them in the Define names box after code creation but not as items in the names box at top left in worksheet.

Thanks for the responses!



Jeff Roberts
Insight Data Consulting
Access and SQL Server Development

http://www.InsightDataConsulting.com

 

[PHV]

And what about this ?
mWkBook.Names.Add DEMOGRAPHICS, "=" & mWkSheet.Name & "!" & DemographicsRegion, TRUE

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[Quehay]

Found it...

I'm working in VB Script so the named arguments (e.g. Name:= format) that you see with VBA macro code doesn't apply--you just enter the required and used arguments in the proper order with commas for optional, neglected arguments.

There IS, however a literal '=' that's needed for the range address, so putting this in fixes it:

dim EQUALS
EQUALS = "="
mWkBook.Names.Add "DEMOGRAPHICS",EQUALS & DemographicsRegion,TRUE

Thanks again!


Jeff Roberts
Insight Data Consulting
Access and SQL Server Development

http://www.InsightDataConsulting.com

 

[SkipVought]

    mWkBook.Names.Add "DEMOGRAPHICS", "=" & mWkSheet.Name & "!" & mWkSheet.[d1].CurrentRegion.Address, True

Skip,

Want to get great answers to your Tek-Tips questions? Have a look at faq222-2244

 

[Quehay]

Guys your perfect posts came after I'd posted my own solution (making a public confession of bewilderment is the surest way to see the mistake in your own code--never fails).

Adding the wksheet name and BANG is probably a good idea!

Thanks!

Jeff


Jeff Roberts
Insight Data Consulting
Access and SQL Server Development

http://www.InsightDataConsulting.com