Excel Filename Links

I have a workbook, call it "Year 8" that has hundreds of references to data from a named range on a separate workbook, call it "New Year 8 Master".

I want to use "Year 8" as a template for four other similar workbooks, called "Year 9", "Year 10", etc.  Each of these would reference separate workbooks, ie "New Year 9 Master", "New Year 10 Master", etc.

I've opened "New Year 9 Master" and saved "Year 8" as "Year 9". I've then tried a find and replace process so that every formula that refers to "New Year 8 Master" now refers to "New Year 9 Master" but as each change occurs, a dialogue box opens asking to identify the linked workbook.  With so many links, the process will take hours.

I don't suppose anyone can identify a workaround?  I was thinking of using some VBA to find and replace each Year 8 reference and change it to Year 9... will that work?

An example of a formula (forgive any crassness) is shown below.

 =IFERROR(INDEX('New Year 8 Master.xlsx'!MainDataTable,MATCH(H5,'New Year 8 Master.xlsx'!Forename_Surname,0)-1,MATCH($AF$5,'New Year 8 Master.xlsx'!Titles,0)),"")  

Many thanks in advance for any suggestions.
Ben
Ben CheethamAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Saurabh Singh TeotiaCommented:
A quick solution for this will be...

Press Ctrl+H and then use this..

what to replace-->'New Year 8 Master.xlsx'!

With this-->'New Year 9 Master.xlsx'!

Now you can decide whether you want to do this replace at worksheet level or at workbook level to do what ever you are looking for and this will do the necessary changes...

Alternatively..You can go to data-->Edit Links and update your links from their as well..

Saurabh...
0
Ben CheethamAuthor Commented:
Many thanks, Saurabh.

Unfortunately, I've tried this already and, as each formula is changed, I get a dialogue box (Headed "Update values") which asks me to select the file containing the data, ie. "New Year 9 Master".  I'll have to do this hundreds of times unless I find another workaround.

Any other ideas?

Thanks
0
Saurabh Singh TeotiaCommented:
You can use the following code to do what you are looking for..

Sub changevalues()

    Dim rng As Range, cell As Range

    Set rng = ActiveSheet.UsedRange.SpecialCells(xlCellTypeFormulas)

If Not rng Is Nothing Then
    For Each cell In rng
        If InStr(1, cell.Formula, "'New Year 8 Master.xlsx'!", vbTextCompare) > 0 Then
            cell.Formula = Replace(cell.Formula, "'New Year 8 Master.xlsx'!", "'New Year 9 Master.xlsx'!", 1, , vbTextCompare)
        End If

    Next cell
End If

End Sub

Open in new window


Saurabh...
0
Introducing Cloud Class® training courses

Tech changes fast. You can learn faster. That’s why we’re bringing professional training courses to Experts Exchange. With a subscription, you can access all the Cloud Class® courses to expand your education, prep for certifications, and get top-notch instructions.

Ben CheethamAuthor Commented:
That's great too, but I am still getting the same problem.  As each change is made, a dialogue box opens asking me to identify the new referenced workbook.

I wish I knew a way to switch off the 'linking' until all the changes were made.
0
Saurabh Singh TeotiaCommented:
Use this one...

Sub changevalues()
Application.ScreenUpdating = False
Application.DisplayAlerts = False
Application.AskToUpdateLinks = False


    Dim rng As Range, cell As Range

    Set rng = ActiveSheet.UsedRange.SpecialCells(xlCellTypeFormulas)

If Not rng Is Nothing Then
    For Each cell In rng
        If InStr(1, cell.Formula, "'New Year 8 Master.xlsx'!", vbTextCompare) > 0 Then
            cell.Formula = Replace(cell.Formula, "'New Year 8 Master.xlsx'!", "'New Year 9 Master.xlsx'!", 1, , vbTextCompare)
        End If

    Next cell
End If


Application.ScreenUpdating = True
Application.DisplayAlerts = True
Application.AskToUpdateLinks = True
End Sub

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Ben CheethamAuthor Commented:
Superbly prompt and the solution seems perfect!

Thank you!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.