CODEWITHSUNDEEP
excel
IMHO
IMHO

Reputation: 799

Excel Hyperlink mass update

I have a spreadsheet with thousands of rows. Each row contains a hyperlink with a path.

The path is not valid, however easily fixable by replacing first part of it with correct value.

Example: current hyperlink: F:\Help\index.html

Needed: P:\SystemHelp\index.html

The problem is that standard Find/Replace does not "see" content of hyperlinks.

Is the only way to write a macro or is there another way to do it?

Upvotes: 25

Views: 84189

Answers (5)

dcp
dcp

Reputation: 55449

I don't know of another way besides a macro. But looks like somebody already wrote one to do it.

Public Sub ReplaceHyperlinkURL(FindString As String, ReplaceString As String) Dim LinkURL As String Dim PreStr As String Dim PostStr As String Dim NewURL As String Dim FindPos As Integer Dim ReplaceLen As Integer Dim URLLen As Integer Dim MyDoc As Worksheet Dim MyCell As Range On Error GoTo ErrHandler Set MyDoc = ActiveSheet For Each MyCell In MyDoc.UsedRange If MyCell.Hyperlinks.Count > 0 Then LinkURL = MyCell(1).Hyperlinks(1).Address FindPos = InStr(1, LinkURL, FindString) If FindPos > 0 Then 'If FindString is found ReplaceLen = Len(FindString) URLLen = Len(LinkURL) PreStr = Mid(LinkURL, 1, FindPos - 1) PostStr = Mid(LinkURL, FindPos + ReplaceLen, URLLen) NewURL = PreStr & ReplaceString & PostStr MyCell(1).Hyperlinks(1).Address = NewURL 'Change the URL End If End If Next MyCell Exit Sub ErrHandler: MsgBox ("ReplaceHyperlinkURL error") End Sub Public Sub WBReplaceHyperlinkURL(FindString As String, ReplaceString As String) 'For all sheets in the workbook Dim LinkURL As String Dim PreStr As String Dim PostStr As String Dim NewURL As String Dim FindPos As Integer Dim ReplaceLen As Integer Dim URLLen As Integer Dim MyDoc As Worksheet Dim MyCell As Range On Error GoTo ErrHandler For Each WS In Worksheets WS.Activate Set MyDoc = ActiveSheet For Each MyCell In MyDoc.UsedRange If MyCell.Hyperlinks.Count > 0 Then LinkURL = MyCell(1).Hyperlinks(1).Address FindPos = InStr(1, LinkURL, FindString) If FindPos > 0 Then 'If FindString is found ReplaceLen = Len(FindString) URLLen = Len(LinkURL) PreStr = Mid(LinkURL, 1, FindPos - 1) PostStr = Mid(LinkURL, FindPos + ReplaceLen, URLLen) NewURL = PreStr & ReplaceString & PostStr MyCell(1).Hyperlinks(1).Address = NewURL 'Change the URL End If End If Next MyCell Next WS MsgBox ("Hyperlink Replacement Complete") Exit Sub ErrHandler: MsgBox ("ReplaceHyperlinkURL error") End Sub

The code must be placed in a VBA code module. From a spreadsheet, open the VBA Editor in the developer ribbon. The developer ribbon can be turned on in the popular tab of Excel Options. Then select Insert - Module from the menu. Copy the code and paste it into the module. Then save the module.

In order to run the procedure, create a macro that contains following line and run the macro in Excel. Be sure to replace the FindText with the portion of the address you want to find and ReplaceText with the text you want to replace it with.

Call ReplaceHyperlinkURL("FindText", "ReplaceText")

Please be sure to make a backup copy of your spreadsheet before running the macro just in case an error is made in the FindText or ReplaceText. If you want to perform the search and replace on all sheets in the workbook, use the WBReplaceHyperlinkURL routine rather than ReplaceHyperlinkURL.

Upvotes: 9

Pranav Rai
Pranav Rai

Reputation: 359

Here is what I typically use for this purpose (since I was never comfortable with macros or VB scripting). This will not affect any formatting - all your graphs and charts will stay as they are.

  1. Rename the .xlsx or .xls file to .zip

  2. Unzip and edit the appropriate .xml or xml.rels file inside with any text editor of your choice

  3. Rename the .zip file back to .xlsx (or .xls)

Thats it.

For step 2 you can choose one of the following ways:

  1. Edit directly inside the .zip file OR

  2. Extract the .zip, then edit the file(s) outside the .zip and then replace it back into the .zip - drag and drop OR

  3. Unzip the .zip, make the edits and zip it back again using only windows explorer (Right click > Send to > Compressed (zipped) folder).

The worksheets are typically located inside the .zip in the following path xl > worksheets. Sometimes the links are stored in the .rels files stored in xl > worksheets > rels

Upvotes: 2

TheChemist
TheChemist

Reputation: 171

No need for a macro

Warning: Some stuff like graphs etc may be lost, however formulas and formats seem to be preserved.

  • Save the document as XML Spread Sheet

  • Open the file with Notepad

  • "Replace all" from "wrong text string" to "correct text string"

  • Save

  • Open the file with Excel

  • Save document in original format

Upvotes: 17

Larry Hirsch
Larry Hirsch

Reputation: 11

Another way is to insert a ' character so it is all text, to the search and replace and then go through and remove the ' characters.

Upvotes: 1

Dick Kusleika
Dick Kusleika

Reputation: 33165

Hey cnx.org, way to reinvent the Replace function.

Sub FindReplaceHLinks(sFind As String, sReplace As String, _
    Optional lStart As Long = 1, Optional lCount As Long = -1)

    Dim rCell As Range
    Dim hl As Hyperlink

    For Each rCell In ActiveSheet.UsedRange.Cells
        If rCell.Hyperlinks.Count > 0 Then
            For Each hl In rCell.Hyperlinks
                hl.Address = Replace(hl.Address, sFind, sReplace, lStart, lCount, vbTextCompare)
            Next hl
        End If
    Next rCell
End Sub

Sub Doit()

    FindReplaceHLinks "F:\help\", "F:\SystemHelp\"

End Sub

Upvotes: 19

Related Questions