Well, I kind of figured later that I might be on the wrong path. You'll see
more code offered below.

As for the Red Line of code - that was caused by the editor here breaking
that line into two lines - it should have been just one line of code.

Looking at my post below, it doesn't seem that it has done that to it. I
thought that we might be able to do the job with some string handling
functions right in the worksheet, but the hyperlink portion of it, with the
path, is part of the formula, not part of the value, so had to stick with
code. The only thing that might throw things off for you is the addition of
the single ' character in front of the developed link - that's to make sure
it ends up in the cell as text, but you can probably change that near the end
line to:
ActiveCell.Offset(0,1).Value = TheLink
and get ok results. Might want to pre-format the column that is to receive
the results as text and set up to not wrap-text. To change the column that
gets the results change the ,1 to another number. Assuming column D with the
links, then ,1 = Column E, 2 would be F, 3 would be G, etc. It is the number
of columns to the right of the column with the links that you want to put the
results into.

"Phil" wrote:

BTW, forgot to mention that I tried to load the code in the VB window as a
new module, and there is red text showing on the lines

ActiveSheet.Hyperlinks.Add Anchor:=Selection,
Address:=ActiveCell.Value

which based on my very limited VB knowledge, might mean that there is an
error in the syntax for those 2 lines.

Please advise.

Phil.

"JLatham" wrote:

I"m not sure I understand the problem completely. But this code should run
under '97 to convert entries in cells to hyperlinks - I use code very similar
to this in several 2000, XP and 2003 workbooks:
Assuming your document path/names are in column D and that is all that's in
D -

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
ActiveSheet.Hyperlinks.Add Anchor:=Selection,
Address:=ActiveCell.Value
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