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?
Giải pháp dùng vbscript :
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
Blogger Comment
Facebook Comment