Goodness only knows how many copies of this will turn up - tried 2x so far
and it's claimed to have failed both times. So I'll try with my own post
instead of Phil's last:
----------------------------
Phil,
I kind of figured I'd missed the boat later on. That's the reason for the
second post (below) with different
code that will pull the link out of the formula in the cells on the Excel
sheet.
As for the Red Text - yes, that indicates an error. The editor here broke
that one line into two pieces. Should be in the code module as one long line
OR as
ActiveSheet.Hyperlinks.Add Anchor:=Selection, _
Address:=ActiveCell.Value
The space and _ at the end tells
VB that the line is continued. But since
the code is generally useless to you, the point is rather moot now.
The code I put up below will do what I really think you want: look at the
formulas in a column and if it is an
=HYPERLINK(
formula, then it will dig out the link portion and place it in a cell on the
same row in a column you designate. The code was written assuming links in
column D, with column E empty. Just change the reference to column D in
"D65536" and "D1" to the actual column, and adjust the ,1 portion of the
ActiveCell.Offset(0,1).Value = TheLink
to move it. the ,1 is the number of columns to the right of the ActiveCell
to place the results. So when in D and offset is ,1 the results go into E.
If you were working in column A, then ,1 would put results into B. Hope that
makes sense.
"JLatham" wrote:
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