Dave,

I had a look through the link you mentioned and have found some code that i
need help to modify slightly. The code is from Dick Kusleika's post
http://groups.google.com/group/micro...7ae7cd9cf83f34

Unfortunately its from 2002 so i don't fancy my chances of getting a reply
in there. What i want to achieve is to modify the hyperlinks after they have
been identified in this code below.

Sub FindHlinks()


Dim MyPath As String
Dim HL As Hyperlink
Dim sh As Worksheet
Dim Currfile As String
Dim CurrWb As Workbook
Dim i As Integer


'Change this path to where the workbooks are
MyPath = "C:\Dick\Tester\Test2\"
i = 1


'Find the first xls file in the directory
Currfile = Dir(MyPath & "*.xls")


'Do while there is at least one xls file
Do While Currfile < ""
'Open the file
Set CurrWb = Workbooks.Open(MyPath & Currfile)
'Cycle through the sheets
For Each sh In CurrWb.Worksheets
'Cycle through the hyperlinks on the sheet
For Each HL In sh.Hyperlinks
'See if the name of your backup directory is in
'the hlink address. I searched for NewTest, but
'you will want to search for the name of your
'own backup directory
If InStr(HL.Address, "NewTest") 0 Then
With ThisWorkbook.Sheets(1)
'Write the info to cells
.Cells(i, 1) = CurrWb.Name
.Cells(i, 2) = sh.Name
.Cells(i, 3) = HL.Address
.Cells(i, 4) = HL.Range.Address
End With
i = i + 1
End If
Next HL
Next sh
'Close the workbook
CurrWb.Close False
'Find the next xls file
Currfile = Dir
Loop


Set CurrWb = Nothing
Set sh = Nothing
Set HL = Nothing


End Sub

Better yet, if someone can also tell me how to make have it allow a user to
input the path of the folder before it runs that would be ideal.

Cheers.

Matt




"Dave Peterson" wrote:

One thing that could cause trouble is that
application.worksheetfunction.substitute is case sensitive.

So if your links actually contained /matt or /MaTt, then the code would not work
as expected.

If you're using xl2k or higher, you could use Replace instead of
application.worksheetfunction.substitute.

Take a look at David McRitchie's site:
http://www.mvps.org/dmcritchie/excel/buildtoc.htm
look for:
Fix Hyperlinks (#FixHyperlinks)

========
If this doesn't help, you may want to post back with samples (directly copied
from the insert hyperlink dialog) and pasted into your message.



fordrules01 wrote:

Hi,

Completely new to VBA in Excel. I've got a large number of excel files
(approx 600) all which contain up to 20 hyperlinks to drawings and other
files contained on a workgrouped computer. Due to the computer crashing we
have had to move all these drawings to another computer and i need to find a
way to update what is potentially 12,000 hyperlinks. (i'm aware that the
setup of these computers is by no means ideal)

Anyway i've tried to copy some vba off the microsoft site with no luck as
yet. If anyone can find the error or has a better solution please let me know.

Code below: (http://support.microsoft.com/default...b;en-us;247507)

Sub HyperLinkChange()
Dim oldtext As String
Dim newtext As String
Dim h As Hyperlink

' These can be any text portion of a hyperlink, such as ".com" or ".org".
oldtext = "/Matt"
newtext = "/Bob"

' Check all hyperlinks on active sheet.
For Each h In ActiveSheet.Hyperlinks
x = InStr(1, h.Address, oldtext)
If x 0 Then
If h.TextToDisplay = h.Address Then
h.TextToDisplay = newtext
End If
h.Address = Application.WorksheetFunction. _
Substitute(h.Address, oldtext, newtext)
End If
Next
End Sub

Cheers

--

Dave Peterson