• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Marco for editing link in workbook

AnkitHanda

New Member
Hi,


I need help in editing links of a worksheet with more than 100

links like this -


Now excel file is in March'11 folder with below link-


D:F&FFeb 11 - CircleAAGA - AA_F&F_FEB_2010-11.xlsx

D:F&FFeb 11 - CircleABGA - AB_F&F_FEB_2010-11.xlsx

D:F&FFeb 11 - CircleAAOther Cost - AA_F&F_FEB_2010-11.xlsx

D:F&FFeb 11 - CircleABOther Cost - AB_F&F_FEB_2010-11.xlsx


Now I want to change link by this-


D:F&FMar 11 - CircleAAGA - AA_F&F_MAR_2010-11.xlsx

D:F&FMar 11 - CircleABGA - AB_F&F_MAR_2010-11.xlsx

D:F&FMar 11 - CircleAAOther Cost - AA_F&F_MAR_2010-11.xlsx

D:F&FMar 11 - CircleABOther Cost - AB_F&F_MAR_2010-11.xlsx


I know Ctrl + H can be done... but there are so many links like this

in it the worksheet that everytime excel stop responding to it.


Please create a marco for it, because its very tedious and time

consuming.


Thanks in advance!!!
 

Hui

Excel Ninja
Staff member
AnkitHanda


Give the following a go


To use copy the text and paste it into a code module in VBA

Change the 2 lines:

fromtxt = "Feb" 'To to change from

totxt = "Mar" 'Text to change to

to suit


Execute

[pre]
Code:
Sub Mod_Hyperlinks()
Dim h As Hyperlink
Dim fromtxt As String
Dim totxt As String
Dim wsheet as string

fromtxt = "Feb" 'To to change from
totxt = "Mar" 'Text to change to
wsheet ="Sheet1" 'Change to your worksheet name

For Each h In Worksheets(wsheet).Hyperlinks
If h.TextToDisplay <> "" Then
h.Address = Replace(h.Address, fromtxt, totxt)
h.TextToDisplay = Replace(h.TextToDisplay, fromtxt, totxt)
End If
Next
End Sub
[/pre]
 

AnkitHanda

New Member
Hi Hui,


Thanks alot for reply. I am new to macro thing, so don't know why this debug is coming.


Debug- For Each h In Worksheets(wsheet).Hyperlinks


In case you want any further clarification. Please let me know.
 

Hui

Excel Ninja
Staff member
Did you change the line

[pre]
Code:
wsheet ="Sheet1" 'Change to your worksheet name
[/pre]
To reflect the Worksheet name where your links are ?


Did you copy the code to the worksheet Module for the worksheet where the links are or somewhere else ?


I have tested this in Excel 2003/XP, 2007 and 2010 and it works fine.


What version of Excel are you using ?
 

AnkitHanda

New Member
Yes I have copied it to VBA module. I am using 2007, can it be done on Activesheet. Because if we do Alt+E+K in a file, we could find all links in a worksheet.


Please make it on all active worksheet.
 

Hui

Excel Ninja
Staff member
[pre]
Code:
Sub Mod_Hyperlinks()
Dim h As Hyperlink
Dim fromtxt As String
Dim totxt As String
Dim wsheet As String

fromtxt = "Feb" 'To to change from
totxt = "Mar" 'Text to change to
wsheet = ActiveSheet.Name 

For Each h In Worksheets(wsheet).Hyperlinks
If h.TextToDisplay <> "" Then
h.Address = Replace(h.Address, fromtxt, totxt)
h.TextToDisplay = Replace(h.TextToDisplay, fromtxt, totxt)
End If
Next
End Sub
[/pre]
 

AnkitHanda

New Member
I appreciate your effort. Guys but I don't know why its not working.


I just wanted to reconfirm that the file/worksheet contains links from different files from different location, as mentioned in my earlier post also.
 

Luke M

Excel Ninja
This doesn't answer the immediate question about debugging, but I just noticed that in the hyperlink address, the month name changes from proper capitalization to all upper capitalization. Please correct if I'm wrong Hui, but since Replace is case-sensitive, I think this alteration would work (again, not sure why Ankit is getting a debug error)

Code:
Sub Mod_Hyperlinks()

Dim h As Hyperlink

Dim fromtxt As String

Dim totxt As String

Dim wsheet As String


  fromtxt = "FEB" 'To to change from

totxt = "MAR" 'Text to change to

wsheet = ActiveSheet.Name


  For Each h In Worksheets(wsheet).Hyperlinks

If h.TextToDisplay <> "" Then

h.Address = Replace(UCase(h.Address), fromtxt, totxt)

h.TextToDisplay = Replace(UCase(h.TextToDisplay), fromtxt, totxt)

End If

Next

End Sub
 

Hui

Excel Ninja
Staff member
Ankit

The modfication to the code made by Luke (Thanx Luke) is working in all Excel versions from 2002-2010.

Have you copied the code into a code module or onto a worksheet module ?


Also what language are you using ?

Why I ask is that Alt+E+K doesn't do anything in the English version of Excel !

and so some of the words on the

For Each h In Worksheets(wsheet).Hyperlinks

line may be different in a different language
 

AnkitHanda

New Member
Hui- can i have your email Id. I will send you one sample file for better explanation.


What I am doing with this code is-


Open the file in which links are there>>> alt+f11>> Insert>> Module>> simple copy this code>> F5.
 
Top