Deleting all named ranges that have a workbook scope
 

Hopefully this is really simple... I've set up a macro-enabled file that
imports excel files from a survey as worksheets - each file becomes a new
sheet. In order to get an overview of the survey answers, each survey file
has a number of named ranges. each with the scope "sheet," so that I can
import fifty files that all have the named range AnswerOne. Unfortunately,
after a recent revision to the survey, it now includes a number of ranges
with the scope "Workbook". If I import one of these files into my collection,
that range becomes global across the entire workbook. When I import the next
file into the collection, its named ranges collide with the existing named
ranges and the entire process breaks down. Therefore I have two questions:

1) Does anybody know of a way to import the files and force all the ranges
in the imported files to be local to the worksheet? That would be ideal,
since I could then have multiple ranges with the same names next to each
other that would only affect their own sheets.

2) How do I change the survey file so that the named ranges are
worksheet-scoped, rather than workbook-scoped? If I try to manage names, the
option to change it from Workbook to Worksheet is grayed out, and I have no
idea why...

Thanks a lot for the help

Deleting all named ranges that have a workbook scope
 

Try using my Name Manager addin (developed by myself and Jan Karel Pieterse)
download from
http://www.decisionmodels.com/downloads.htm

It has the ability to filter and change names from Global to Local etc.


Charles
___________________________________
The Excel Calculation Site
http://www.decisionmodels.com

"Babymech" wrote in message
...
Hopefully this is really simple... I've set up a macro-enabled file that
imports excel files from a survey as worksheets - each file becomes a new
sheet. In order to get an overview of the survey answers, each survey file
has a number of named ranges. each with the scope "sheet," so that I can
import fifty files that all have the named range AnswerOne. Unfortunately,
after a recent revision to the survey, it now includes a number of ranges
with the scope "Workbook". If I import one of these files into my
collection,
that range becomes global across the entire workbook. When I import the
next
file into the collection, its named ranges collide with the existing named
ranges and the entire process breaks down. Therefore I have two questions:

1) Does anybody know of a way to import the files and force all the ranges
in the imported files to be local to the worksheet? That would be ideal,
since I could then have multiple ranges with the same names next to each
other that would only affect their own sheets.

2) How do I change the survey file so that the named ranges are
worksheet-scoped, rather than workbook-scoped? If I try to manage names,
the
option to change it from Workbook to Worksheet is grayed out, and I have
no
idea why...

Thanks a lot for the help

Deleting all named ranges that have a workbook scope
 

Excellent - thanks. Your Name Manager worked wonders for converting the scope
of the names in the survey, so now I have a version with only local ranges.
However, I still have a number of survey files floating around with the old
setup, ie a bunch of Workbook-ranges... do you know a way to either convert
them by macro, or to use Name Manager to batch change the files I have?

Again - thanks for the tip and the excellent utility.

"Charles Williams" wrote:

Try using my Name Manager addin (developed by myself and Jan Karel Pieterse)
download from
http://www.decisionmodels.com/downloads.htm

It has the ability to filter and change names from Global to Local etc.


Charles
___________________________________
The Excel Calculation Site
http://www.decisionmodels.com

"Babymech" wrote in message
...
Hopefully this is really simple... I've set up a macro-enabled file that
imports excel files from a survey as worksheets - each file becomes a new
sheet. In order to get an overview of the survey answers, each survey file
has a number of named ranges. each with the scope "sheet," so that I can
import fifty files that all have the named range AnswerOne. Unfortunately,
after a recent revision to the survey, it now includes a number of ranges
with the scope "Workbook". If I import one of these files into my
collection,
that range becomes global across the entire workbook. When I import the
next
file into the collection, its named ranges collide with the existing named
ranges and the entire process breaks down. Therefore I have two questions:

1) Does anybody know of a way to import the files and force all the ranges
in the imported files to be local to the worksheet? That would be ideal,
since I could then have multiple ranges with the same names next to each
other that would only affect their own sheets.

2) How do I change the survey file so that the named ranges are
worksheet-scoped, rather than workbook-scoped? If I try to manage names,
the
option to change it from Workbook to Worksheet is grayed out, and I have
no
idea why...

Thanks a lot for the help

Deleting all named ranges that have a workbook scope
 

Don't know of a batch method, what I would do is:
- start Excel
- open all the survey files
- start Name Manager
- set the right Name Manager Filter
- convert the active workbook, save it and close it
- repeat for the next active workbook

Since Name Manager automatically refreshes for the active workbook it should
be quite fast.

Charles
___________________________________
The Excel Calculation Site
http://www.decisionmodels.com

"Babymech" wrote in message
...
Excellent - thanks. Your Name Manager worked wonders for converting the
scope
of the names in the survey, so now I have a version with only local
ranges.
However, I still have a number of survey files floating around with the
old
setup, ie a bunch of Workbook-ranges... do you know a way to either
convert
them by macro, or to use Name Manager to batch change the files I have?

Again - thanks for the tip and the excellent utility.

"Charles Williams" wrote:

Try using my Name Manager addin (developed by myself and Jan Karel
Pieterse)
download from
http://www.decisionmodels.com/downloads.htm

It has the ability to filter and change names from Global to Local etc.


Charles
___________________________________
The Excel Calculation Site
http://www.decisionmodels.com

"Babymech" wrote in message
...
Hopefully this is really simple... I've set up a macro-enabled file
that
imports excel files from a survey as worksheets - each file becomes a
new
sheet. In order to get an overview of the survey answers, each survey
file
has a number of named ranges. each with the scope "sheet," so that I
can
import fifty files that all have the named range AnswerOne.
Unfortunately,
after a recent revision to the survey, it now includes a number of
ranges
with the scope "Workbook". If I import one of these files into my
collection,
that range becomes global across the entire workbook. When I import the
next
file into the collection, its named ranges collide with the existing
named
ranges and the entire process breaks down. Therefore I have two
questions:

1) Does anybody know of a way to import the files and force all the
ranges
in the imported files to be local to the worksheet? That would be
ideal,
since I could then have multiple ranges with the same names next to
each
other that would only affect their own sheets.

2) How do I change the survey file so that the named ranges are
worksheet-scoped, rather than workbook-scoped? If I try to manage
names,
the
option to change it from Workbook to Worksheet is grayed out, and I
have
no
idea why...

Thanks a lot for the help