Hyperlinks & Change Location

Question

Please help, I have a workbook with hundreds of hyperlinks that link to numerous files within a folder let's call it current. 

I then archive the files within folder current into archive folder, let's call it 1011.  These hyperlinks were created using CTRL K so were linked when they were in current. 

When I move the files into the 1011 folder the links no longer work as the path is incorrect. 

Is there an efficient way of remapping these given that within my workbook there are over 30 worksheets with all individual links? 

I have seen the light and we now use the =hyperlink function so for current and futures links this won't be an issue using Ctrl H.

Folder structure

Going From

Folder (projects)

      Folder (current)

          File A, B, C & so on

Going to

Folder (projects)

    Folder (archive)

        Folder (1011)

            Files A, B, C & so on

TIA

Bec

Answer 1

replaceHyperlinks "\projects\current", "\projects\archive\1011"

Do I put the entire path into old and the new?  Also do I have to put that script into every worksheet? or at the end of already existing script.

 

 I think I need to be spoon fed here, sorry.

TIA

Bec

Put the code into a standard module.

It affects every worksheet in the active workbook.

Provide only the portion of the path that you want changed.

You can always use the code and see what it does.  Just make sure you have a backup copy of the workbook. :)

Answer 2

replaceHyperlinks "\projects\current", "\projects\archive\1011"

Do I put the entire path into old and the new?  Also do I have to put that script into every worksheet? or at the end of already existing script.

 I think I need to be spoon fed here, sorry.

TIA

Bec

Answer 3

Please help, I have a workbook with hundreds of hyperlinks that link to numerous files within a folder let's call it current. 

I then archive the files within folder current into archive folder, let's call it 1011.  These hyperlinks were created using CTRL K so were linked when they were in current. 

When I move the files into the 1011 folder the links no longer work as the path is incorrect. 

Is there an efficient way of remapping these given that within my workbook there are over 30 worksheets with all individual links? 

I have seen the light and we now use the =hyperlink function so for current and futures links this won't be an issue using Ctrl H.

 

Folder structure

Going From

Folder (projects)

      Folder (current)

          File A, B, C & so on

 

Going to

Folder (projects)

    Folder (archive)

        Folder (1011)

            Files A, B, C & so on

 

TIA

Bec

 

Compiles OK but otherwise untested.  Modify useReplaceHLs as required and run it to modify all the hyperlinks in all the worksheets in the active workbook.

Option Explicit

Sub replaceHyperlinks(ByVal OldAddr As String, ByVal NewAddr As String)

    Dim WS As Worksheet

    For Each WS In ActiveWorkbook.Worksheets

        Dim HL As Hyperlink

        For Each HL In WS.Hyperlinks

            HL.Address = Replace(HL.Address, OldAddr, NewAddr)

            Next HL

        Next WS

    End Sub

Sub useReplaceHLs()

    replaceHyperlinks "\projects\current", "\projects\archive\1011"

    End Sub

Answer 4

Updates are set to automatic and is still refer to the old path

Answer 5

Have you set the update files to automatic or manual, what happens if you click on the update links?