[Solved] changing multiple hyperlinks @ once??

I found this doing a search, it should help you out;

Sub ChangeHyperLinks()
Dim WS As Worksheet
Dim H As Hyperlink
Dim stOldPath As String
Dim stNewPath As String
stOldName = "Sheet2!A1" ' change as required
stNewName = "Sheet3!A1" ' change as required
For Each WS In ActiveWorkbook.Worksheets
For Each H In WS.Hyperlinks
If H.Range = stOldName Then
H.Range = stNewName
End If
Next
Next
End Sub

This is what I came up with after toying with it a bit

Sub ChangeAllHyperLinks()
Dim strOldName As String 'old month name
Dim strNewName As String 'new month name
strOldName = "Nov"
strNewName = "Dec"

Dim H As Hyperlink
For Each H In ActiveSheet.Hyperlinks 'only looks in active sheet
'this assumes you are using three letter naming convention for your months
'if your text does not display the month and range you can change the TextToDisplay part to H.Range
'I used the H.TextToDisplay to make sure it was changing as it should
If Left(H.TextToDisplay, 3) = strOldName Then
'set the new link to new month and keep current range value i.e., !A1
H.Range = strNewName & Right(H.Range, Len(Left(H.Range, 3)))
'also changes the sub address of link to new month and keep current range value
H.SubAddress = "" & strNewName & Right(H.Range, Len(Left(H.TextToDisplay, 3))) & ""
End If
Next
End Sub

Hi,

If you want to make the routine 'reusable' so that you don't have to recode it the next time you want to do the same sort of thing, try this approach.

Sub SwapText()
    Dim ws As Worksheet
    Set ws = ThisWorkbook.Worksheets("Data")
    HyperlinkChanger ws, "Sheet2", "Sheet3"
End Sub


Sub HyperlinkChanger(ws As Worksheet, strOld As String, strNew As String)
    Dim hLink As Hyperlink, wsF As WorksheetFunction


    Set wsF = Application.WorksheetFunction
    For Each hLink In ws.Hyperlinks
        With hLink
            .Range = wsF.Substitute(.Range, strOld, strNew)
        End With
    Next hLink
    
End Sub

HTH

I'm trying to do kinda the same thing. There are some cells in my worksheet that link to last years workbook. I need to change all the hyperlinks so that they point to this year. For example:
..BioMedBm-03rg.xls#'VIII-ASP03'!$S$12

The 03s need to be changed to 04s. I've tried to adapt the code posted by bnix to do this, but my VB is kinda rusty. Right now it looks like this:

Sub ChangeAllHyperLinks()
Dim strOldYear As String 'old year
Dim strNewYear As String 'new year
strOldYear = "03"
strNewYear = "04"

Dim H As Hyperlink
For Each H In ActiveSheet.Hyperlinks 'only looks in active sheet
'this assumes you are using three letter naming convention for your months
'if your text does not display the month and range you can change the TextToDisplay part to H.Range
'I used the H.TextToDisplay to make sure it was changing as it should
If Left(H.Address, 2) = strOldYear Then
'set the new link to new month and keep current range value i.e., !A1
H.Range = strNewYear & Right(H.Range, Len(Left(H.Range, 2)))
'also changes the sub address of link to new month and keep current range value
H.SubAddress = "" & strNewYear & Right(H.Range, Len(Left(H.Address, 2))) & ""
End If
Next
End Sub

Can someone help me with this?

Thanks.

Hi D,

Welcome to the forum

Did you try the routine that I suggested above? Just change the 'old' and 'new' parameters that are used.

When I try to run your script, I get an "Subscript Out of Range" error and debug highlights the third line. I'm guessing I'm not changing something I should.

Hi D,

I take it you mean the line Set ws = ThisWorkbook.Worksheets("Data")? This assumes that the sheet you are working with is called "Data", just change it to the name of the sheet for your particular workbook.

HTH

Okay, almost there. Now is saying "Type Mismatch" for this line:

.Range = wsF.Substitute(.Range, strOld, strNew)

Try .Range.Formula rather than just .Range

Well, I tried that. I ran it and it wanted me to point to a bunch of files that the worksheet is pointing to, but even after doing that it still gives me the mismatch error. :puzzled:

originally posted by RichieUK

Quote

This assumes that the sheet you are working with is called "Data", just change it to the name of the sheet

originally posted by jjst34

Quote

even though I change "Data", to the name of the workbook, as suggested

Did you rename the workbook or the worksheet name?

I reread my post. I sounded a bit testy didn't I? Sorry, that was not my intention. I was just trying to make sure that the problem wasn't a "simple" fix.

VBA, as all programming languages are, can be a wee bit unforgiving when things like workbook and worksheet are interchanged (or an extra comma, parantheses, quotation mark, exclamation point . . . is typed in - the voice of experience speaking here).

Again, I apologize for the tenor of my prior post.

:biggrin::biggrin:
:usaflag:

Hi,

OK, we're talking about hyperlinks within a workbook aren't we?!

Right, test with a 3-sheet workbook. Hyperlinks on "Data" sheet, other sheets called "Nov 03" and "Dec 03". The following will change the link from Nov 03 to Dec 03.

Sub SwapText()
    Dim ws As Worksheet
    Set ws = ThisWorkbook.Worksheets("Data")
    HyperlinkChanger ws, "Nov", "Dec"
End Sub
 
Sub HyperlinkChanger(ws As Worksheet, strOld As String, strNew As String)
    Dim hLink As Hyperlink, wsF As WorksheetFunction
 
    Set wsF = Application.WorksheetFunction
    For Each hLink In ws.Hyperlinks
        With hLink
            .Range = wsF.Substitute(.Range, strOld, strNew)
            .Address = ""
            .SubAddress = wsF.Substitute(.SubAddress, strOld, strNew)
        End With
    Next hLink
     
End Sub