Extracting URL from link
Kristoffer Gade
I want to extract the URL's from these links into a separate colum. I tried
with a macro, and it works fine for 1 cell - but not for the whole colum.
Any hints would be appreciated.
Kristoffer
Jan Karel Pieterse
Then please post the code you have!!!
Regards,
Jan Karel Pieterse
Excel TA/MVP
>.
>
Kristoffer Gade
facilities ..... anyway, if you require the source - then please explain how
to obtain it !
Thanks in advance (Macro newcomer)
"Jan Karel Pieterse" <pieter...@compuserve.com> wrote in message
news:cfdf01c258a5$42491890$2ae2...@phx.gbl...
Jan Karel Pieterse
Choose Tools, Macro, Visual Basic Editor to open the
Visual Basic Editor (or press Alt+F11). This is the design
environment that stores the VBA code. If this is the first
time you have opened the editor, you will probably see
three windows: the Project window, the Properties window,
and the Code window.
In the Project window, select the name of the workbook you
recorded the macro into. Double click it. Look
for "Modules", expand that. Double click the modules you
see until you find the macro you recorded (hope you know
what name you gave it when starting the recorder).
Copy the lines of that module and paste them into a msg
here.
Regards,
>.
>
Kristoffer Gade
> Selection.Hyperlinks(1).TextToDisplay = ""
>End Sub
Kristoffer Gade
For x = 1 To 1089
Selection.Hyperlinks(x).TextToDisplay = ""
Next x
End Sub
Jan Karel Pieterse
Try this code in stead of yours:
Sub test()
Dim myLink As Hyperlink
For Each myLink In ActiveSheet.Hyperlinks
Range(myLink.Range.Address).Value = myLink.Address
Next
End Sub
This replaces all hyperlinks on a sheet with their address.
Regards,
Jan Karel Pieterse
Excel TA/MVP
>.
>
Jan Karel Pieterse
Great!. Obviously I misinterpreted your question. I tought
you wanted to remove the links and retain the url's.
Regards,
Jan Karel Pieterse
Excel TA/MVP
>.
>
Bob Kanaley
I am trying to accomplish something similar to this and I
have been unable to find a way to do this. I need to
search 40-50 workbooks for possible incorrect hyperlinks.
I have a master Excel workbook that serves as a table of
contents to 40-50 workbooks (and growing). There are 3
masters used to create the individual workbooks referenced
in the table of contents workbook.
The hyperlinks from the table of contents to the
individual workbooks all point to the correct workbooks.
The individual workbooks are created from one of three
masters that each contain a hyperlink to jump back to the
table of contents.
Somehow one of the master copies being used to create the
individual workbooks had the hyperlink back to the table
of contents changed (updated) to reflect the location the
files were being backed up to (copied to) at night.
Once this happened, all the embedded links in the backup
copy of the table of contents pointed to the location of
the backup copies of the individual workbooks! The people
working on the individual workbooks were unaware of the
bad link, so they ended up editing backup copies that were
then overwritten by the unedited originals that night. The
next day confusion ensued.
I would like to know how to search this group of files for
the incorrect hyperlink and understand better how the
hyperlink can change.
I specify the hyperlink as
\\servername\file_location\filename.xls and save the file.
The workbook files are automatically copied to a backup
location each night. It seems the hyperlinks are
automatically updated to reflect the backup directory the
files are in.
If the backup file is accidently opened from it's backup
location, all the hyperlinks are automatically updated to
d:\file_location\filename.xls. If the file is saved it
seems to keep this location instead of the server location.
I was looking for a way to quickly search the individual
workbooks for the incorrect links to the backup directory,
but have been unable to find anyway to search and find
hyperlinks in workbooks.
I would appreciate any pointer to help me fix this and
keep it from happening again.
Bob
Dick Kusleika
I'm not an expert on hyperlinks, but I'll give it a shot. By default,
hyperlinks are relative to the workbook that contains them. If your
workbook was in
C:\MyDocuments\RealData\
and your hyperlink pointed to
C:\MyDocuments\OtherData\
then your hyperlink address would convert to
..\OtherData\LinkdWb.xls
The .. at the beginning tells Excel to go up one directory from the
workbooks directory the follow the rest of the path from there.
When you specify the address as a UNC type address, I always thought that it
was absolute, that is, that it didn't matter where you moved the workbook,
it would always point to the address you typed in. You may be proving me
wrong on that point. I tried to break a hyperlink with a UNC address, but I
couldn't. I created a hyperlink like this
\\Barb\c\Invite.xls
I moved the workbook to a different directory on the same drive, but it
didn't change the hyperlink. I then moved the workbook to \\Barb\c\ and the
hyperlink changed to just
Invite.xls
So it appears to be relative in that sense, but not in the same way as the
first example I gave. I then moved the workbook back to it's original
location and the hyperlink changed back to
\\Barb\c\Invite.xls
The long and the short of it is that I couldn't break the link similar to
what you described. Here are a couple of other pieces of information that
may be useful to you:
Under File - Properties, there is a textbox to enter the Hyperlink Base. If
you enter a Hyperlink Base, then all hyperlinks in that file will be
relative to that base, not the workbook. If you leave it blank, then
hyperlinks will be relative to the workbook. You might consider using
Hyperlink Base to ensure that the hyperlinks always point to the right
place. Not being able to reproduce the problem you had, I can't really say
if setting a Hyperlink Base will help you avoid future problems, but it may
be worth a try.
To fix your current problem, you will need a macro that opens all the files,
searches for hyperlinks with a certain string in their address. In the
macro below, I searched all the xls files in C:\Dick\Tester\test2\ and
within those files looked for hyperlinks that contained the string NewTest
(which is the name of another directory). If found, the relevant
information was listed on a worksheet. You could modify this macro to
actually change the addresses, but without knowing more about your setup, I
didn't want to mess up your stuff with bad information. If this macro seems
like it will work for you and you need help modifying it to fix the
addresses instead of just listing them out, post back. Here's the macro I
used:
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
Sorry I couldn't give you a better answer to your problem. I hope with the
information above you can sort it out.
--
Dick Kusleika
MVP - Excel
Post all replies to the newsgroup.
"Bob Kanaley" <r...@NOSPAM.agdia.com> wrote in message
news:152ce01c259c7$5dfd71d0$39ef2ecf@TKMSFTNGXA08...