Importing names & ranges

Ibm / 8124ntp
April 2, 2009 at 22:55:01
Specs: Microsoft Windows XP Professional, 2.992 GHz / 1278 MB
I have a master excel file which is linked to multiple files. In my master file I may increase the names and ranges for data sets added. I can carry my name list through for my drop down menus but what I want to do is update the name and range values in my multiple files to reflect the new ones.

At present I am opening the files linked to the master and manually adding the names and ranges and if I have to do many files it is extremely frustrating.

When I open any file linked to my master file I want it to import the name and range values automatically from the master file.

Can anyone help.

See More: Importing names & ranges

Report •

April 3, 2009 at 13:54:53
Maybe somebody can pick up were I got stuck...

I put this in Book2 and it added the same Names that were found in Book1, along with the range that the Name RefersTo, except that the range was in Book2, not Book1.

In other words, it duplicates the Names from Book1, but doesn't ReferTo the Book1 ranges, it RefersTo the Book2 ranges.

Sub ImportNamedRanges()
 Set nms = Workbooks("Book1.xls").Names
   For n = 1 To nms.Count
     ActiveWorkbook.Names.Add Name:=nms(n).Name, RefersTo:=nms(n).RefersTo
End Sub

I then tried building the RefersTo argument by using:

rft = "[Book1.xls]" & Right(nms(n).RefersTo, Len(nms(n).RefersTo) - 1)

(I needed to strip the = sign off the RefersTo result)

and then used RefersTo:=rft in my code, but it returned a text string like "[Book1.xls]Sheet1!$B$1:$B$10" including the quotes, which obviously won't work.

Maybe this will give somebody else an idea.

Report •

April 3, 2009 at 15:39:12
You know, now that I've thought about this, I'm a little confused.

Why do you need to add the Names to the other workbooks?

If you have a Named range in the Master book e.g. Fred, why can't you just use it in the other books, e.g.


Report •

April 3, 2009 at 18:52:56
Wonderful response as usual but let me remove the confusion which is quite logical. We often send the file to the client and because the master contains data for other users in a different format we only want in the child file the data required hence the linked sheet.

Also when the file goes off site it loses the connection with the parent file and therefore does not update. If the link refers to a file not available the drop down list in the cell will return an error which means the client cannot change the data.

I WANT the names to refer to the hidden link sheet in my child file so that it will always be capable of being worked on if the server goes down or if I do my work at home after office hours and do not want to risk duplicate master files and changing link source on remote editing.

My aim is that if the parent file is available all changes made in the master table will make the child file update and if the parent file is unavailable the child file will still function correctly.

Therefore the macro needs to check if the parent file is accessible on opening of the child file and if not just close until the next time the child file is opened.

I hope this is a clear enough explanation.

Report •

Related Solutions

April 3, 2009 at 20:09:10
re: I hope this is a clear enough explanation.

Unfortunately, no. I understand the issue with the master file not being available, but I'm not sure what names/ranges need to be updated when the child files are opened.

You have the advantage of knowing the construction of your workbooks and how they are interconnected, while we're sitting out here on the web without a clue.

Is it possible to dumb it down for me and give me some examples of where the various Names are - which book, which sheet, what you need them to look like, etc?

As far as checking to see if the Master file is available, some minor modification of the following code should take care of that. If we can figure how how to update the Names, we would simply wrap this code around that solution.

Sub DoesWorkBookExist()
'Written by
'Test to see if a Workbook exists
    With Application.FileSearch
        .LookIn = "C:\Documents and Settings\User\My Documents"
        '* represents wildcard characters
        .Filename = "Master*.xls"
            If .Execute > 0 Then 'Workbook exists
                MsgBox "There is a Workbook."
            Else 'There is Not a Workbook
                MsgBox "The Workbook does not exist"
            End If
    End With
End Sub

Report •

April 5, 2009 at 23:12:32
OK, I will try and explain how it all works together.

My master (parent) file consists of one sheet which has all the data I need in the columns.

The columns are in sets of three the first being material size then next being surface area and finally the last column in each set is the weight per meter.

The first column in the sheet contains the section names which are also the names of the ranges in the first column of each data set.

As per your excellent advice previously I named the three columns of the data set “XXXXXDATA” which worked perfectly on the formula constructing the LOOKUP function from the single range name.

All my data is in three columns with two names and corresponding ranges. i.e ANGLE & ANGLEDATA.

At present there are ten sets of data (30 columns) that I use but will increase to potentially 50+ sets in the future.

As the data for these extra columns will be added on an ad-hoc basis the sheet is continually updated as required.

My child file has one sheet that is linked to the master and recalculates each time it is opened to ensure it is up to date with the master content at any time. My projects can each have up to 1000 child files linked to the master.

Each child file uses names and ranges which refer back to the link sheet to construct the drop down lists and calculate the value. These names and ranges are identical to the master file names and ranges.

Excel links and updates DATA automatically which is great.
Excel does not link or update names and ranges which is my problem.

I may increase the column ranges in my master file because I have added extra data making the columns longer, the child file automatically captures these changes in the data. At the moment the drop down lists show lots of white space because the ranges are set at greater values than the data existing. It is sometimes a nuisance but still easy to live with.

I will increase the data columns for new groups and this is automatically updated by the links because I have linked the master full width and a larger number of rows than I know will ever be used. I do not link the full sheet because of the huge amount of recalculation on updating.

The problem is that I want to copy the new names and ranges in the master and create the same values in the child files and also update the values for existing ranges if they have changed.

Two scenarios exist here. One is to compare the child name values with the parent name values and update the changes. Two is to just pick up ALL the names and ranges from the parent file and overwrite the values in the child file each time it opens.

If you have any more questions or would like a copy of the relevant files then I will be very happy to comply.

Report •

April 6, 2009 at 09:40:59
I guess the first question is this:

How comfortable are you with working with VBA code?

I'm not going to be able to write something that will "just work" right out of the box, so it is going to need some modifications to fit your exact requirements. Only you will know the network path names, file names, sheet names, etc.

Even if I knew all of the paths and other names, I couldn't test the code since the paths don't exist in my world.

I can provide a shell, but not a turn-key product.

I'm also not going to be under contract to maintain the code should issues arise or circumstances change.

If you want, I'll try to write something that should get you started, and maybe hang around to tweak it a little, but after that you'll be on your own.

Let me know how you would like to proceed.

Report •

April 6, 2009 at 20:29:28
I understand all you stated in your reply and agree that it is for you to offer assistance ad-hoc at your choice with no obligations and it is VERY appreciated.

I am thinking that as I am NOT comfortable with VBA code because it is like Chinese to me maybe we should "think out of the box" here.

I am comfortable with formula construction in Excel and get great pleasure from a successful conclusion.

If I was to create a range of cells that contained the values needed to create the names and ranges could you construct with VBA a macro that constructs the names and ranges from values in linked cells.

I see it like this:

1. Cell A1 has the range name
2. Cell B1 has the range values
3. Cell A2 has next range name etc.

Cell A1 value is ANGLE
Cell B1 value is $D$2:$D$200
Cell A2 value is ANGLEDATA
Cell B2 value is $D2:$F$200

I nearly forgot that the sheet name that the link data is on is called simply LINKS

As I update my master table I add in the extra values required and the macro sees the values and rebuilds the names and ranges in the child sheet from the cells linked. That way the paths are irrelevant because the source data is the local sheet.

Not the perfect solution but it "probably" does away with a lot of in-depth work in importing the names etc.

Thankyou for your patience & kindness with this problem.

Report •

April 9, 2009 at 01:37:56
Hi there.

After sitting and looking at the VB code above I decided to try messing with it to see how things happened.

An error left me hanging a little but then I suddenly realised my master sheet and my link sheet had different names so I corrected that.

I then ran the macro again and my names and ranges from my master loaded perfectly.

There was absolutely nothing wrong with your first code. I apologise if my requests were a little jumbled and but your interpretation was perfect.

Is it possible for the macro to get the values without opening the master file? I am assuming the "Set nms....." requires the master to be open so something is required here.

I know from the run of this dialogue that I MUST start to learn VB. I am missing out on too much here and even at 60 yo I can still re-educate myself albeit a little more slowly than my younger days.

Thanks for the help to date.

Report •

Ask Question