Oops - forgot a minor element. For files, the hyperlink needs to have
file:/// in front of the drive identifier, so if all of your text statrs out
with the filename, you are going to have to add the initial portion of the
link to it, including the actual path, by changing one line of the code from
above:

ActiveSheet.Hyperlinks.Add Anchor:=Selection, _
Address="file:///C:\Data\DOC\Prospect_Name\Hope\" & ActiveCell.Value

If you have lots of Prospects, this could get quite tedious.

If you are trying to pull the hyperlink address portion out of the
=HYPERLINK(" formula, then some text manipulation via VBA code could help.
Assume your hyperlinks are set up in column D again, and that column E is
available for use.

Sub MakeHyperlinks()
Dim lastRow As Long

lastRow = Range("D65536").End(xlUp).Row
Range("D1").Select
Application.ScreenUpdating = False
Do Until ActiveCell.Row lastRow
If Not(IsEmpty(ActiveCell)) Then
If ActiveCell.HasFormula Then
TheFormula = ActiveCell.Formula
If Left(TheFormula, 11) = "=HYPERLINK(" Then
TheLink = Right(TheFormula, Len(TheFormula) - 12)
TheLink = Left(TheLink, InStr(TheLink, Chr$(34)) - 1)
'put single ' in front of the link to keep it as text
Activecell.Offset(0,1).Value = "'" & TheLink
End If
End If

End IF
ActiveCell.Offset(1, 0).Activate
Loop
Application.ScreenUpdating = True

End Sub


"Phil" wrote:

Hello,

I have just ran a program called List Files (by Primitive Software, rel
1.43) which created a list of all my docs from a folder. My goal is to
import the newly created table into a database which will utilize the
hyperlinks. The problem is that the database is Access 97, which means I
must save the file in Excel 97 format (I am currently using Excel 2003), but
if I save the table in 97, the hyperlinks will disappear.

This is the formula behind the Excel for the given document path:

=HYPERLINK("C:\Data\DOC\Prospect_Name\Hope\Carol Ann Colon et vir Allen
Colon DDrep 6-12-06.doc","Carol Ann Colon et vir Allen Colon DDrep
6-12-06.doc")

My intended result would be this format:
C:\Data\DOC\Prospect_Name\Hope\Carol Ann Colon et vir Allen Colon DDrep
6-12-06.doc

Any input would be greatly appreciated.

Phil