Update existing hyperlinks automatically when changing the folder name linked documents are saved under

[ftischer]

Dear Forum members,<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o></o>
I am desperately looking for some advice from you Excel experts!<o></o>
At work we are using an excel spreadsheet as a project checklist during the project execution phase. Roughly 100 template documents (doc, xls, mpp etc.) which are saved on a shared drive are linked in this file. Those are filled with project specific information over time, so that after the information is entered it is no longer a template, but a file with data. Therefore the files need to be saved somewhere else and the hyperlinks need to be updated, which is currently being done manually, so that the template files don’t get overwritten.<o></o>
<o> </o>
My current idea is to set up a template file and template folder structure on a shared drive that can be used for every upcoming project. In other words one overall folder (Projects) contains all projects (Project_1, _2…) and within each project the same file structure would be used, for example:<o></o>
<o> </o>
C:\Projects<o></o>
C:\Projects\Project_1<o></o>
C:\Projects\Project_2<o></o>
C:\Projects\Template_Project<o></o>
<o> </o>
C:\Projects\Template_Project<o></o>
C:\Projects\Template_Project\Budget\budget01.xls<o></o>
C:\Projects\Template_Project\Budget\changes.xls<o></o>
<o> </o>
C:\Projects\Template_Project\Schedule\Deliverables.mpp<o></o>
C:\Projects\Template_Project\Schedule\Milestones.mpp<o></o>
<o> </o>
C:\Projects\Template_Project\Org\Org_chart.doc<o></o>
C:\Projects\Template_Project\Org\Contacts.doc<o></o>
<o> </o>
All of the hyperlinks in that Template_Project would be set up accordingly and link to all the files in the Template_Project folder. Well, as you can imagine only one project could be called Template_Project (not very likely!) and all following ones would need a different name. So I would copy the Template_Project folder and rename it to for example Project_3.<o></o>
<o> </o>
How can I update all saved hyperlinks in the xls file from the old folder name to the new one without touching every single hyperlink manually? In other words replace “Template_Project” in all hyperlinks with “Project_3”:<o></o>
<o> </o>
C:\Projects\Project_3<o></o>
C:\Projects\Project_3\Budget\budget01.xls<o></o>
C:\Projects\Project_3\Budget\changes.xls<o></o>
<o> </o>
Did you, dear Mr.Excel fan, by any chance run into this problem before? And if you did, could you please share your findings/advice? Or do you maybe have an idea how the hyperlinks which are not actually part of the cell content, could be manipulated other than the tedious manually way?<o></o>
<o> </o>
I’d appreciate any comments and suggestions! Thank you very much and have a great, happy and healthy new year 2011!<o></o>
<o> </o>
Cheers,<o></o>
Felix<o></o>

 

[Jerry Sullivan]

Welcome Felix,



You could search the Board and find examples of how to use VBA to edit all your Hyperlinks, but for the situation you describe, I think you would be better off to setup your templates using relative pathing for the Hyperlinks. Here are the steps:

1. Pick a cell in your workbook in which you will store the path of the Project Folder (A1 in the screen shot example below).
2. Define a Name for that Cell ie: FilePath (when defining the name select "Workbook" from the Scope dropdown).
3. You can manually enter your Project Folder in this Cell or use the formula shown in Cell A1 below to automatically list the folder your workbook is saved.
4. When you create your Hyperlinks, instead of using the full file spec like: =HYPERLINK ("C:\Projects\Template_Project\Schedule\Milestones.mpp" ,"Milestones")

use your FilePath variable like: =HYPERLINK(FilePath & "Schedule\Milestones.mpp","Milestones")
​

Excel Workbook
	A	B
1	C:\Projects\Projects_3\
2
3	Project Links:	Budget
4		Milestones
Sheet1

Excel 2007

Cell Formulas
Range	Formula
A1	=LEFT(CELL("filename"),SEARCH("[",CELL("filename"))-1)
B3	=HYPERLINK(FilePath & "Budget\budget01.xls ","Budget")
B4	=HYPERLINK(FilePath & "Schedule\Milestones.mpp","Milestones")

Excel Workbook
Name	Refers To
FilePath	=Sheet1!$A$1
Workbook Defined Names

The best part is that you won't need to modify your Hyperlinks each time you start a new project.

Cheers!

 

[ftischer]

Thank you very much, Jerry.
Today I finally found the time to test your recommended solution and it works perfectly. This will help us a lot and will save a big chunk of time.

Thanks again! Cheers,
Felix

 

[AshleyEH]

Hello All,
I know that this post is a few years old but I am looking for a solution to a similar issue. I am using excel for budget summary. One of the worksheets compares the prior years budget (broken down by weeks), this is compared to the current year-current week budget. Every year the folder paths have to be manually updated (cell by cell) to the prior years folders. They also include formulas to calculate the budget, folder path etc. Is there an easier way to do this? Or does it have to be done manually at the start of a new year?
Thank you so much for any insight you can give me on this issue.
AEH

 

[exceldunong]

This helped me too! Than you Jerry and Mr Excel!