Avatar of Andreas Hermle
Andreas HermleFlag for Germany

asked on 

Change hyperlink addresses that contain a certain substring using VBA

Dear Experts:

I got hundreds of hyperlinks on the active worksheet. All of the hyperlink addresses point to network paths.
Lots of them now have to be changed the following way:

Examples and Requirements:

Before

C:\test\MyProduct_90-234-56-17_new_bw.pdf  
C:\test\MyProduct_90-433-22-19_old_bw.pdf
C:\test\MyProduct_90-373-44-18_old_updated_bw.pdf
C:\test\MyProduct_90-244-19-22_bw.pdf
C:\test\MyProduct_90-243-44-11_old_bw.pdf
C:\test\MyProduct_90-433-22-99_bw.pdf
etc.

Only hyperlinks which contain the following substrings are to be worked on: 90-433-22-19, 90-244-19-22, 90-433-22-99
In the above three cases the substring 'bw' at the very end has to be changed to '4c'

In the above example this results in:

After:

C:\test\MyProduct_90-234-56-17_new_bw.pdf  
C:\test\MyProduct_90-433-22-19_old_4c.pdf
C:\test\MyProduct_90-373-44-18_old_updated_bw.pdf
C:\test\MyProduct_90-244-19-22_4c.pdf
C:\test\MyProduct_90-243-44-11_old_bw.pdf
C:\test\MyProduct_90-433-22-99_4c.pdf

All the others have to be left untouched since they do not contain these substrings.

Would be great if somebody could come up with a VBA-solution since I have to change hundreds of these hyperlink addresses. This would save me hours of time.

Help is much appreciated. Thank you very much in advance.

Regards, Andreas
VBAMicrosoft ExcelMicrosoft Office

Avatar of undefined
Last Comment
Andreas Hermle
Avatar of Rgonzo1971
Rgonzo1971

Hi,

Could you send a dummy?

Regards
Avatar of Rgonzo1971
Rgonzo1971

Hi,

pls try
Sub Macro()
Dim regex As Object

Set regex = CreateObject("VBScript.RegExp")
With regex
    .Global = True
    .MultiLine = True
    .IgnoreCase = True
    .Pattern = "(90-(433-22-19|244-19-22|433-22-99)(_.*?)?_)(bw)\."
End With


For Each c In Range(Range("A1"), Range("A" & Rows.Count).End(xlUp))
    strInput = c.Text
    c.Value = regex.Replace(strInput, "$14c.")
    Next
Next
Set regex = Nothing


End Sub

Open in new window

Regards
Avatar of GrahamSkan
GrahamSkan
Flag of United Kingdom of Great Britain and Northern Ireland image

Or this:
Option Explicit

Sub EditHyperlinks()
    Dim hyp As Hyperlink
    Dim strNumbers() As String
    Dim i As Integer
    
    strNumbers = Split("90-433-22-19,90-244-19-22,90-433-22-99", ",")
    
    For Each hyp In ActiveSheet.Cells.Hyperlinks
        With hyp
            For i = 0 To UBound(strNumbers)
                If InStr(.Address, strNumbers(i)) > 0 Then
                    .Address = Replace(.Address, "bw", "4c")
                End If
            Next i
        End With
    Next hyp
End Sub

Open in new window

ASKER CERTIFIED SOLUTION
Avatar of GrahamSkan
GrahamSkan
Flag of United Kingdom of Great Britain and Northern Ireland image

Blurred text
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
Avatar of Andreas Hermle

ASKER

Hi Rgonzo,

thank you very much for your swift and professional help.

I tested it and it worked sort of but not quite:

The hyperlink 'text to display' gets changed, but it is the hyperlink address that should be changed not the 'text to display'

Moreover my hyperlinks are scattered all over the worksheet not only in Column A. If you could consider this in your code :-)

Hope this is feasible and again thank you very much for your great / professional help.

I have attached a sample file for your convenience.

Regards, Andreas

Change_Hyperlink_Address_Rgonzo_Sam.xlsm
Avatar of Andreas Hermle

ASKER

HI Graham,

upps, did not expect you to roam around in the Excel section ;-)

And you know what, it works just fine your code  :-) , great job of yours as always. Thank you very much for it.

As a matter of fact I will do some more testing on other worksheets just to be sure ...

I am off to do some chores, will get back to you this afternoon

Till then, Regards, Andreas
Avatar of Andreas Hermle

ASKER

Just a quick one before I go, how many numbers can I enter in that split function on line 6?
Avatar of GrahamSkan
GrahamSkan
Flag of United Kingdom of Great Britain and Northern Ireland image

There isn't a specific limit. The code stops when the last entry in the resultant array is reached - UBound(strNumbers)
Avatar of Andreas Hermle

ASKER

Dear Graham, thank you very much for your superbjob, works just great. I really appreciate your work.

Rgonzo, you were very close to the solution and have helped me a lot in the past.

Regards, Andreas
Microsoft Excel
Microsoft Excel

Microsoft Excel topics include formulas, formatting, VBA macros and user-defined functions, and everything else related to the spreadsheet user interface, including error messages.

144K
Questions
--
Followers
--
Top Experts
Get a personalized solution from industry experts
Ask the experts
Read over 600 more reviews

TRUSTED BY

IBM logoIntel logoMicrosoft logoUbisoft logoSAP logo
Qualcomm logoCitrix Systems logoWorkday logoErnst & Young logo
High performer badgeUsers love us badge
LinkedIn logoFacebook logoX logoInstagram logoTikTok logoYouTube logo