Hyperlinks When moving File

[MiceElfandEye]

Good Morning,

I have several different files (powerpoint, excel etc). They are all in the same folder. There is one excel file that has a hyperlink to each of these (one file to rule them all), that is also in the same folder. My question is, when the folder is copy and pasted, or downloaded to a different location, will the hyperlinks remain intact? Is there a way to keep them intact?

 

[AKR22]

No, hyperlinks keep a folder path that can only be changed manually. You can create a vba process to search for the files in question, then grab the path and create a new hyperlink.

 

[MiceElfandEye]

No, hyperlinks keep a folder path that can only be changed manually. You can create a vba process to search for the files in question, then grab the path and create a new hyperlink.

That sounds delightful. While I'm not a complete rookie at VBA I would have no idea how to do this from scratch, ideas?

 

[FormR]

Is there a way to keep them intact?

Hi, if you have the name of the file including the file extension in A1 - this formula will return a hyperlink to that file in same folder as the workbook that contains it.

=HYPERLINK(TRIM(LEFT(SUBSTITUTE(CELL("filename"),"[",REPT(" ",99)),99))&A1,A1)

Note: for this to work the workbook needs to have been saved at least once.

 

[MiceElfandEye]

Not exactly what I was looking for, but thank you for the idea. Users are going to have to be able to download a folder with all files in it, save it to their network and then with minimal (read click a button) manipulation update all the hyperlinks to where they currently are. End users are not adept at excel and will not understand much beyond, click this, then this, then fill it in.

 

[FormR]

Hi, not sure which part doesn't work for you, but this method would allow the hyperlinks to work without any further manipulation. If its having to have a list of the file names in separate cells, you could always hide that column.

Anyway, I'm sure someone will offer up a VBA alternative

 

[MiceElfandEye]

Perhaps I'm not understanding what it is you're saying. All the hyperlinks are either attached to text or pics,does that make a difference. I haven't dealt with whatever wizardry you are proposing and am therefore apparently lost.

 

[AKR22]

That sounds delightful. While I'm not a complete rookie at VBA I would have no idea how to do this from scratch, ideas?

Yes, a few actually! I will assume that the folder name that the user will be downloading will not change. You can search for the folder and then set the new path to a hyperlink. I found some material that should help: Search for a file on all drives of a computer - Microsoft Access / VBA

Below is the code for adding a hyperlink once you have found the folder path:

[COLOR=#0000FF][FONT=Menlo]With[/FONT][/COLOR][COLOR=#000000][FONT=Menlo] Worksheets([/FONT][/COLOR][COLOR=#000000][FONT=Menlo]1[/FONT][/COLOR][COLOR=#000000][FONT=Menlo])  .Hyperlinks.Add Anchor:=.Range([/FONT][/COLOR][COLOR=#A31515][FONT=Menlo]"a5"[/FONT][/COLOR][COLOR=#000000][FONT=Menlo]), _  Address:=[/FONT][/COLOR][COLOR=#A31515][FONT=Menlo]"http://example.microsoft.com"[/FONT][/COLOR][COLOR=#000000][FONT=Menlo], _  ScreenTip:=[/FONT][/COLOR][COLOR=#A31515][FONT=Menlo]"Microsoft Web Site"[/FONT][/COLOR][COLOR=#000000][FONT=Menlo], _  TextToDisplay:=[/FONT][/COLOR][COLOR=#A31515][FONT=Menlo]"Microsoft"[/FONT][/COLOR][COLOR=#000000][FONT=Menlo] [/FONT][/COLOR][COLOR=#0000FF][FONT=Menlo]End[/FONT][/COLOR][COLOR=#000000][FONT=Menlo] [/FONT][/COLOR][COLOR=#0000FF][FONT=Menlo]With[/FONT][/COLOR]

 

[MiceElfandEye]

When they save it to their computer/network drive, the folder itself will have a different name. For each piece of machinery, it will have a corresponding folder named after it. Example, Machine A, Machine B, etc. I will have no knowledge ahead of time what they will name the folder. Can I have them enter the file name in a specified cell and have it search for that?

 

[FormR]

All the hyperlinks are either attached to text or pics,does that make a difference.

Hi, yes, my suggestion assumed the hyperlinks were in cells and as such is not appropriate for your set-up.