Hyperlink with VBA

Radio_123 1

Hi everyone,

We have a large folder with a bunch of files/folders on a shared drive. Our excel sheet(in that same large folder) keeps track of all of these files/folders and there is also a hyperlink for each file/folder. For some odd reason, all the hyperlinks recently got corrupted and none of them work, however the text is still there. Instead of manually linking over 1000 hyperlinks, does anyone know or have a chunk of code that I can plug into VBA and get all the hyperlinks automatically set correctly. I should mentioned that all the file names, and hyper-link names are the exact same string of text. I just need the actual hyperlink to be linked, the text is already there. I did some research and I'm not sure if there is an automatic solution, considering this is a shared drive, and not a local computer folder.

Thanks,

1 answer

Michael Taylor 56,346 Reputation points

2023-01-10T16:15:56.403+00:00

I don't think a shared drive vs local computer folder matters here. Can you provide us a sample of the data you're talking about? Based upon your description it sounds like you have something like this:

'\server\share\file1.txt' '\server\share\file2.txt`

Each of those values in your Excel spreadsheet is a link that when clicked goes to the actual UNC in File Explorer. Is that correct?

Do you really need the programmatic link? By default when Excel sees a URL/UNC text value it automatically treats it as a link. You don't have to do anything. Of course it does this when you add the text so you couldn't just remove the hyperlink programmatically as that would break it.
Please sign in to rate this answer.
Radio_123 1 Reputation point

2023-01-13T18:00:56.3166667+00:00

Thanks for the infomation. I should have mentioned that this folder I have is filled with folders and Microsoft Word documents. So, some file locations would have to end with a .doc, and some would have to end with nothing (because its a folder). Do you know how I can automate this so I don't have to hand pick each one for what file type it is.

Michael Taylor 56,346 Reputation points

2023-01-13T18:13:27.0766667+00:00

Can you clarify what piece is corrupted? Specifically is the text fine but the hyperlink was removed or is the hyperlink corrupted somehow? Are all the text/links mangled in the same way (e.g. they all now start with https or something)?

Michael Taylor 56,346 Reputation points

2023-01-13T18:17:13.08+00:00

As for a script, take a look at this one. The script finds all the hyperlinks in the spreadsheet. In the script they are resetting that it has been visited. In your case though you would simply change the hyperlink URL to match the text in the cell it is contained in. This should reset all links to the corresponding text. If you need a sample of how that might look then I'll have to put something together to demo it. But it is basically the above script with the URL being set instead. The hard part is finding the links.

Radio_123 1 Reputation point

2023-01-18T15:03:26.65+00:00

The text is fine. For some reason all the hyperlinks used to point to the shared drive where all of these files "CJT-XXXX" were posted, as you can see in the picture.

The actual hyperlink location somehow changed.

I like your method of just defining the location in the spreadsheet, and it will automatically fetch it and link it, however some boxes will require a ".txt" at the end if it is a word doc, and some other boxes will require nothing at the end since it is a folder.

Also, the script you linked doesn't seem to work, my pc takes no action upon me clicking it.

Michael Taylor 56,346 Reputation points

2023-01-18T15:31:03.4533333+00:00

Can you post a very small example file so we have something to test against?

As for Office scripts, because of security macros no longer work in Office files unless you save them with macros enabled. But if you give us a sample file we can more easily fix this I believe.

Radio_123 1 Reputation point

2023-01-19T14:26:40.6733333+00:00

Hi Michael,

I have an excel file, along with some folders and word documents. I would like some automated way of referencing the folders/word docs in the excel file.

I can't post the files in here since they don't allow these file types to be posted, can I have your email ?

Michael Taylor 56,346 Reputation points

2023-01-19T15:06:34.8233333+00:00

All we really need is an Excel file with the data that you want fixed up. We don't need the associated files. You should be able to zip up your Excel file and attach it to the post. Otherwise you'll need to use a file sharing service like DropBox to share the file for security reasons.

Radio_123 1 Reputation point

2023-01-20T16:54:14.5366667+00:00

Here are the files.

[https://drive.google.com/drive/folders/1Hq3PDUhfHyO2ebW-iaqp6jyjWWbgSjXg?usp=sharing

Michael Taylor 56,346 Reputation points

2023-01-20T20:38:11.57+00:00

Ok, given your example the process would be to enumerate all the hyperlinks in the spreadsheet. For each spreadsheet update the link to have a new prefix (\\server\share). Something like this.

Sub FixLinks() Const LinkPrefix = "\\server\share\" Dim link As Hyperlink Dim newLink As String For Each link In ActiveSheet.Hyperlinks newLink = LinkPrefix & link.Address link.Address = newLink Next End Sub

There are caveats here. Firstly the assumption is that each link in the spreadsheet is just the relative URL and not a mix of absolute and relative URLs. For example if one link is \\server1\share1\folder\file1 and another is folder\file1 then the script would not be able to easily tell the difference. If you need that logic then your script will have to get more complex to try to figure out what the existing link actually is.

Secondly, macros are disabled in Office docs for security reasons. Therefore you'll need to temporarily switch the app type. Open the spreadsheet then go to the Developers tab and click Macros. Then create a new macro (e.g. FixLinks). Put the contents of the script into the macro. Then save it. When you do Office will tell you to click Yes to ignore the macro or No to enable it. When you click No then it'll prompt for you to save the spreadsheet as a new file. Change the type to Excel Macro-Enabled Workbook. Once the save is complete then go back to the Macros list, select the macro and click Run to run it. Once it completes your spreadsheet should be updated. At that point you shouldn't need the macro anymore so you can save again and change the type back to just Excel Workbook to remove the macro.
Sign in to comment

Use comments to ask for clarification, additional information, or improvements to the question.

Share via

Hyperlink with VBA

1 answer

Your answer