How do you "strip" a hyperlink

[Guest]

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

 

[Guest]

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

 

[Guest]

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

 

[Guest]

I'm sorry if I misled you, but all I want to accomplish is to "strip" off all
the elements so that only the filepath is showing. The Access database needs
the literal path, not the "hyperlink" representing the path.

Let me explain further. In cell D5, there is a value of:

=HYPERLINK("C:\Reef\Data\DOC\Prospect_Name\Hope\6-12-06.doc","6-12-06.doc")

that you can see in the F2 window.

All I want to do is take all of the text above, and distill out the ACTUAL
filepath, without the reference to HYPERLINK and the other stuff. So, my
intended result would look like this:

C:\Data\DOC\Prospect_Name\Hope\6-12-06.doc

Does that help explain it a little?

Thanks for your patience.

 

[Guest]

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.

 

[Guest]

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.

 

[Guest]

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.

 

[Jim Cone]

Phil,
Run the program with the "List Duplicate File Paths" button checked.
After the list is created, click the "Remove Hyperlinks" button.
That should do it.
The List Files program current version is 1.51.
--
Jim Cone ( the program author)
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware




"Phil" <[email protected]>
wrote in message
I'm sorry if I misled you, but all I want to accomplish is to "strip" off all
the elements so that only the filepath is showing. The Access database needs
the literal path, not the "hyperlink" representing the path.
Let me explain further. In cell D5, there is a value of:

=HYPERLINK("C:\Reef\Data\DOC\Prospect_Name\Hope\6-12-06.doc","6-12-06.doc")

that you can see in the F2 window.
All I want to do is take all of the text above, and distill out the ACTUAL
filepath, without the reference to HYPERLINK and the other stuff. So, my
intended result would look like this:
C:\Data\DOC\Prospect_Name\Hope\6-12-06.doc

Does that help explain it a little?
Thanks for your patience.

 

[adamo222]

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

Hi Phil,

how about using the basic 'text to columns' function in excel.

select the column or cells with your hyperlink formulas, then choose
Data->Text to Columns.

Choose Delimited as the data type then Next.

In the delimiters slection tick Other, and enter " as the delimiter.
You will then see proposed column markers in the preview pane, with
your file path fitting into one of the columns.

Select Next.

In the data preview pane click on the columns with data you don't
need, and check 'do not import column(skip)' radio button in the
Column data format area at the top. Make sure that the column you
want is still set to General or Text, then click Finish.

This should replace all the hyperlink formulas with flat text file
paths.