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]

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?

Create a button for the user to "refresh hyperlinks". Then use the following code for the button to ask the user to select the folder. Then update the hyperlinks accordingly:

With Application.FileDialog(msoFileDialogFolderPicker)        .Title = "Select a root folder"
        .ButtonName = "Select"
        .AllowMultiSelect = False
        
        Worksheets("worksheet with hyperlinks").Hyperlinks.Add Anchor:=.Range("A5"), _
        Address:=Application.FileDialog(msoFileDialogFolderPicker).SelectedItems(1), _
        ScreenTip:="Microsoft Web Site", _
        TextToDisplay:="Microsoft"

 

[MiceElfandEye]

This sounds delightful, copied/pasted code to module, gave it a title (Sub UpdateHyperlinks()) This line shows up in red: With Application.FileDialog(msoFileDialogFolderPicker) .Title = "Select a root folder"

What am I doing wrong?

 

[AKR22]

" .Title = "Select a root folder" " - should be on a new line

 

[MiceElfandEye]

Compile error:
Method or datamember not found
I assume I need to type in the worksheet name here ("worksheet with hyperlinks") and I assume when it says .Range("A5") I need to ensure something is in A5?

 

[AKR22]

Yes, you need to specify all of the variables because I dont know what they are. BTW, the path that is being returned is only to a root folder. It will not grab file names so you will need to do that. But, there is a way to select file names if need be.

 

[MiceElfandEye]

I'm sorry, you've lost me. How do I get them to grab file names? The people this is going to are not going to be able to do this, is this something I do before they get it?

 

[AKR22]

I am running under the assumption that the folder name is different for every machine, and the file names inside the folder that the hyperlinks are linking to remain the same. If this is the case, once you have the folder path, then you can overwrite all of the hyperlinks with the file names while including the new folder path.

If this is not the case, you will have to be more specific in what is happening to these files.

 

[MiceElfandEye]

Yes, folder name is the only one that will change. the files within the folder will remain the same name. What information is supposed to be in A5? Do I need to replace the verbiage "worksheet with hyperlinks" with the actual name of the workbook where all the hyperlinks live?

 

[AKR22]

Yes, you have the right idea. The range should point to the cell with the hyperlink you want to overwrite; one function for one hyperlink. Also yes, you need to specify the sheet where the hyperlink is.

 

[MiceElfandEye]

There are going to be around 150, do I need to use one of these for each? By the way, in case you get bored with this, thank you so much for taking the time to walk me through this, I appreciate it.