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