Excel: Mass hyperlink change.

[thingy]

Just moved which server one of our companies use. This has brought up a problem where by one of the users excel documents (at least, more will probably crop up over time) has lots of hyperlinks in it to powerpoint files and whatnot. The hyperlinks are using the full UNC to the old netware server, and there's 3000+ of these fuckers.

Is anyone aware of any tools or adept enough at VB scripting to help me do a mass change of "\\server\vol1\group\company\filepath\filename.ext" to "H:\filepath\filename.ext" in the hyperlinks? Search/replace only does the text, it's not smart enough to go into the links themselves.

[CMYK]

This will do it.

Run the code in every workbook you have



Sub ReplaceHyperlinks()
Dim h As Hyperlink
Dim intWrksheets As Integer
Dim strFilename As String

Const NEW_PATH = "H:\the_path\" 'Change as required:: MUST END IN BACKSLASH

For intWrksheets = 1 To ActiveWorkbook.Worksheets.Count 'for every sheet in the workbook
For Each h In Worksheets(intWrksheets).Hyperlinks 'for every hyperlink in the sheet
strFilename = Right(h.Address, (Len(h.Address) - InStrRev(h.Address, "\", -1, 1))) 'get the filename + extension
h.Address = NEW_PATH & strFilename 'change the actual link
h.TextToDisplay = NEW_PATH & strFilename 'change the display name
Next
Next

End Sub

[thingy]

Not every linked file is in the same directory. Is it possible just to change part of the path and not the entire thing?

[CMYK]

How many directories?

If it's just a handful we can add a Select Case to do it

[thingy]

God knows, there's over 3000 files linked apparently.

[CMYK]

but

"\\server\vol1\group\company\"

is common before the distinct directory kicks in and you want it replaced with

"h:\"

??

[CMYK]

Sub ReplaceHyperlinks()
Dim h As Hyperlink
Dim intWrksheets As Integer

Const OLD_PATH = "\\server\vol1\group\company\" ':: MUST END IN BACKSLASH

Const NEW_PATH = "H:\" 'Change as required:: MUST END IN BACKSLASH

For intWrksheets = 1 To ActiveWorkbook.Worksheets.Count 'for every sheet in the workbook
For Each h In Worksheets(intWrksheets).Hyperlinks 'for every hyperlink in the sheet
h.Address = Replace(h.Address, OLD_PATH, NEW_PATH, 1, 1)
h.TextToDisplay = Replace(h.Address, OLD_PATH, NEW_PATH, 1, 1)
Next
Next

End Sub

[thingy]

[gives CMYK a gold star] worked a treat.

[CMYK]

Nice one.

Glad to help.

[wolfpac181]

Put a magnet on it..... solution to everything!