Update all links at once

Question

I have a Workbook with many sheets almost all of them have a hyperlink to cell A1 in one specific sheet named 'HK 2017'. I want to change the name of the hyperlinked sheet from 'HK 2017' to 'HK'. And also update all links at once, so that they could work with the new name of the sheet.

Thanks for help.

Vityata · Answer 1 · 2017-10-06T08:52:51.683

1

Loop through the sheets in the worksheet. Excel VBA looping through multiple worksheets
In every sheet, loop through the cells in the used range. Excel VBA iterate over all used cells and substitute their values
Change their hyperlink values. changing a wildcard in an excel hyperlink with vba or Excel VBA Get hyperlink address of specific cell
Party like it's your birthday - https://www.youtube.com/watch?v=5qm8PH4xAss

edited Oct 06 '17 at 08:52

answered Oct 06 '17 at 08:30

Vityata

42,633
8
55
100

Well that's nice but I dont have pdf files as in the point 3. also there is no # sign in my hyperlink, so no .Adress or .SubAdress wont work. Also I need to delete characters not replace characters like in that code. I was more curious about some way to do this without VBA guess it is not possible. – blau Oct 06 '17 at 08:50

score 0 · Answer 2 · answered May 22 '19 at 11:25

Here is my solution. In all worksheets of the current workbook I simply replace a given string in the formula for a different one

Public Sub edit_links()
    Dim iSheet As Worksheet
    Dim old_text as string
    Dim new_text as string

    old_text = "='\\C\client\XYZ\[old_excel_file.xlsm]Sheet1'"
    new_text = "=Sheet1"

    For Each iSheet In ThisWorkbook.Sheets
        'Debug.Print isheet.Name
        Call update_all_cell_formulas_in_sheet( _
            isheet, _
            old_text, _
            new_text)
    Next iSheet
End Sub

private Sub update_all_cell_formulas_in_sheet(in_sheet As Worksheet, in_search As String, in_replace As String)
    Dim counter As Integer
    Dim iCell As Range

    counter = 0
    For Each iCell In in_sheet.UsedRange
        If InStr(iCell.Formula, in_search) > 0 Then
            counter = counter + 1
            'Debug.Print iCell.Parent.Name, iCell.Address, iCell.Formula
            iCell.Formula = Replace(iCell.Formula, in_search, in_replace)
            'Debug.Assert counter Mod 100 <> 0 ' stop the code every 100 changes
        End If
    Next iCell

    update_all_cell_formulas_in_sheet = counter

End Sub

Update all links at once

2 Answers2