#### how to copy 2350 hyperlink full paths to any column in a worksheet ?

```Hi all,

First of all, thank for the help I received before.

I have another question :

I have an excel worksheet,  with about 2350 entries.  All of them have an
hyperlink in column A.  I need to find a way to copy ONLY THE HYPERLINKS FULL
PATHS of all 2350 cells to column D, NOT THE CELLS CONTENT !!
The reason is that from this column D, I can generate playlists without problem.

In my search for a solution, I came across following UDF which did not work
and gave an error.  First of all, I'm not sure if this UDF will solve my problem
an secondly, if it does, how can I repare it ?

Function HyperLinkText(pRange As Range) As String

Dim ST1      As String
Dim ST2      As String
Dim LPath    As String
Dim ST1Local As String

Exit Function
End If

LPath = ThisWorkbook.FullName

If Mid(ST1, 1, 15) = "..\..\..\..\..\" Then
ST1Local = ReturnPath(LPath, 5) & Mid(ST1, 15)
ElseIf Mid(ST1, 1, 12) = "..\..\..\..\" Then
ST1Local = ReturnPath(LPath, 4) & Mid(ST1, 12)
ElseIf Mid(ST1, 1, 9) = "..\..\..\" Then
ST1Local = ReturnPath(LPath, 3) & Mid(ST1, 9)
ElseIf Mid(ST1, 1, 6) = "..\..\" Then
ST1Local = ReturnPath(LPath, 2) & Mid(ST1, 6)
ElseIf Mid(ST1, 1, 3) = "..\" Then
ST1Local = ReturnPath(LPath, 1) & Mid(ST1, 3)
Else
ST1Local = ST1
End If

If ST2 <> "" Then
ST1Local = "[" & ST1Local & "]" & ST2
End If

End Function

Kontiki
```
 0
12/10/2004 9:16:00 PM
excel.misc 78881 articles. 5 followers.

4 Replies
606 Views

Similar Articles

[PageSpeed] 55

```Try this one

If I remember correct this is from
Dick Kusleika
hlnk.Parent.Offset(0, 3).Value = HypToPath(hlnk)
Next
End Sub

Function HypToPath(hyp As Hyperlink) As String

Dim GoBack As Long
Dim CurrFldr As String
Dim i As Long
Dim OldDir As String

CurrFldr = hyp.Parent.Parent.Parent.Path
OldDir = CurDir

If GoBack > 0 Then
ChDir CurrFldr

For i = 1 To GoBack
ChDir ".."
Next i

If Not CurDir Like "?:\" Then
End If

ChDir OldDir
ElseIf Mid(CurrAdd, 1, 2) = "\\" Then
Else
HypToPath = CurrFldr & "\" & CurrAdd
End If
End Function

--
Regards Ron de Bruin
http://www.rondebruin.nl

"kontiki" <vanpottelberge@easynet.be> wrote in message news:ef40b9b2.0412101316.291545a4@posting.google.com...
> Hi all,
>
> First of all, thank for the help I received before.
>
> I have another question :
>
> I have an excel worksheet,  with about 2350 entries.  All of them have an
> hyperlink in column A.  I need to find a way to copy ONLY THE HYPERLINKS FULL
> PATHS of all 2350 cells to column D, NOT THE CELLS CONTENT !!
> The reason is that from this column D, I can generate playlists without problem.
>
> In my search for a solution, I came across following UDF which did not work
> and gave an error.  First of all, I'm not sure if this UDF will solve my problem
> an secondly, if it does, how can I repare it ?
>
> Function HyperLinkText(pRange As Range) As String
>
> Dim ST1      As String
> Dim ST2      As String
> Dim LPath    As String
> Dim ST1Local As String
>
> If pRange.Hyperlinks.Count = 0 Then
>       Exit Function
> End If
>
> LPath = ThisWorkbook.FullName
>
>
> If Mid(ST1, 1, 15) = "..\..\..\..\..\" Then
>        ST1Local = ReturnPath(LPath, 5) & Mid(ST1, 15)
> ElseIf Mid(ST1, 1, 12) = "..\..\..\..\" Then
>        ST1Local = ReturnPath(LPath, 4) & Mid(ST1, 12)
> ElseIf Mid(ST1, 1, 9) = "..\..\..\" Then
>        ST1Local = ReturnPath(LPath, 3) & Mid(ST1, 9)
> ElseIf Mid(ST1, 1, 6) = "..\..\" Then
>        ST1Local = ReturnPath(LPath, 2) & Mid(ST1, 6)
> ElseIf Mid(ST1, 1, 3) = "..\" Then
>        ST1Local = ReturnPath(LPath, 1) & Mid(ST1, 3)
> Else
>        ST1Local = ST1
> End If
>
> If ST2 <> "" Then
>       ST1Local = "[" & ST1Local & "]" & ST2
> End If
>
>
> End Function
>
> Thanks for any reply !
>
>
> Kontiki

```
 0
rondebruin (3790)
12/10/2004 9:36:50 PM
```LOL

> Dick Kusleika

It is a great guy but 3 times is to much <g>

--
Regards Ron de Bruin
http://www.rondebruin.nl

"Ron de Bruin" <rondebruin@kabelfoon.nl> wrote in message news:eg60fBw3EHA.1524@TK2MSFTNGP09.phx.gbl...
> Try this one
>
> If I remember correct this is from
> Dick Kusleika
> For Each hlnk In Columns("A").Hyperlinks
>   hlnk.Parent.Offset(0, 3).Value = HypToPath(hlnk)
> Next
> End Sub
>
> Function HypToPath(hyp As Hyperlink) As String
>
> Dim GoBack As Long
> Dim CurrFldr As String
> Dim i As Long
> Dim OldDir As String
>
> CurrFldr = hyp.Parent.Parent.Parent.Path
> OldDir = CurDir
>
>
> If GoBack > 0 Then
>    ChDir CurrFldr
>
>    For i = 1 To GoBack
>        ChDir ".."
>    Next i
>
>    If Not CurDir Like "?:\" Then
>    End If
>
>    HypToPath = CurDir & CAddStrip
>
>    ChDir OldDir
> ElseIf Mid(CurrAdd, 1, 2) = "\\" Then
> Else
>    HypToPath = CurrFldr & "\" & CurrAdd
> End If
> End Function
>
>
> --
> Regards Ron de Bruin
> http://www.rondebruin.nl
>
>
> "kontiki" <vanpottelberge@easynet.be> wrote in message news:ef40b9b2.0412101316.291545a4@posting.google.com...
>> Hi all,
>>
>> First of all, thank for the help I received before.
>>
>> I have another question :
>>
>> I have an excel worksheet,  with about 2350 entries.  All of them have an
>> hyperlink in column A.  I need to find a way to copy ONLY THE HYPERLINKS FULL
>> PATHS of all 2350 cells to column D, NOT THE CELLS CONTENT !!
>> The reason is that from this column D, I can generate playlists without problem.
>>
>> In my search for a solution, I came across following UDF which did not work
>> and gave an error.  First of all, I'm not sure if this UDF will solve my problem
>> an secondly, if it does, how can I repare it ?
>>
>> Function HyperLinkText(pRange As Range) As String
>>
>> Dim ST1      As String
>> Dim ST2      As String
>> Dim LPath    As String
>> Dim ST1Local As String
>>
>> If pRange.Hyperlinks.Count = 0 Then
>>       Exit Function
>> End If
>>
>> LPath = ThisWorkbook.FullName
>>
>>
>> If Mid(ST1, 1, 15) = "..\..\..\..\..\" Then
>>        ST1Local = ReturnPath(LPath, 5) & Mid(ST1, 15)
>> ElseIf Mid(ST1, 1, 12) = "..\..\..\..\" Then
>>        ST1Local = ReturnPath(LPath, 4) & Mid(ST1, 12)
>> ElseIf Mid(ST1, 1, 9) = "..\..\..\" Then
>>        ST1Local = ReturnPath(LPath, 3) & Mid(ST1, 9)
>> ElseIf Mid(ST1, 1, 6) = "..\..\" Then
>>        ST1Local = ReturnPath(LPath, 2) & Mid(ST1, 6)
>> ElseIf Mid(ST1, 1, 3) = "..\" Then
>>        ST1Local = ReturnPath(LPath, 1) & Mid(ST1, 3)
>> Else
>>        ST1Local = ST1
>> End If
>>
>> If ST2 <> "" Then
>>       ST1Local = "[" & ST1Local & "]" & ST2
>> End If
>>
>>
>> End Function
>>
>> Thanks for any reply !
>>
>>
>> Kontiki
>
>

```
 0
rondebruin (3790)
12/10/2004 9:44:45 PM
```At least you spelled it correctly! <g>

Ron de Bruin wrote:
> LOL
>
>
>>Dick Kusleika
>
>
> It is a great guy but 3 times is to much <g>
>
>

--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html

```
 0
dsd1 (5911)
12/10/2004 9:52:18 PM
```<vbg>

--
Regards Ron de Bruin
http://www.rondebruin.nl

"Debra Dalgleish" <dsd@contexturesXSPAM.com> wrote in message news:41BA1A92.8030308@contexturesXSPAM.com...
> At least you spelled it correctly! <g>
>
> Ron de Bruin wrote:
>> LOL
>>
>>
>>>Dick Kusleika
>>
>>
>> It is a great guy but 3 times is to much <g>
>>
>>
>
>
> --
> Debra Dalgleish
> Excel FAQ, Tips & Book List
> http://www.contextures.com/tiptech.html
>

```
 0
rondebruin (3790)
12/10/2004 10:00:48 PM

Similar Artilces:

Matching data in columns
I hope somebody can help me. I have a list in column C of 13000 names with a reference number against each of them in column D. The reference numbers are between 1 and 760 and each of the 13,000 names in column C has one of the 760 numbers against it in column D. In column F, I have the list of 760 numbers with text for each of these numbers in column G. I need to correctly insert the text for all these 760 names against my main list of 13,000 names. To try to explain it I want to: If number in any cell in column D equals the same number in any cell column F, then paste the text in...

How do add columns showing dob and years to work to give a retire.
I have a column showing date of birth and a column showing at what age they retire, 50, 55, 60. How do I add up the two to get a retirement date? Many thanks Claire Hi something like =DATE(YEAR(A1)+50,MONTH(A1),DAY(A1)) -- Regards Frank Kabel Frankfurt, Germany "Claire Le Monnier" <ClaireLeMonnier@discussions.microsoft.com> schrieb im Newsbeitrag news:8F892F61-F652-4177-A0C0-F25899C11CFA@microsoft.com... > I have a column showing date of birth and a column showing at what age they > retire, 50, 55, 60. How do I add up the two to get a retirement date? > Many than...

in VBA Sheets("mysheet").Copy Before:=Sheets(1) how do i get a reference to the newly created copy of this sheet?
in VBA Sheets("mysheet").Copy Before:=Sheets(1) how do i get a reference to the newly created copy of this sheet? Hi Daniel, Try something like: Dim WB As Workbook Dim WS As Worksheet Set WB = ActiveWorkbook With WB .Sheets("Mysheet").Copy Before:=.Sheets(1) End With Set WS = ActiveSheet --- Regards, Norman "Daniel" <softwareengineer98037@yahoo.com> wrote in message news:OdVZVvmgFHA.3088@TK2MSFTNGP10.phx.gbl... > in VBA Sheets("mysheet").Copy Before:=Sheets(1) how do i get a reference > to > th...

Locking and Hiding Columns
I have a spreadsheet that I need to share without the user being able to unhide certain columns yet still have the ability to enter data in the other fields. Any suggestions? Thanks -- JerryS Have you tried Locking all the the cells except those cells you want to allow user input, then hide the columns and protect the sheet with a password? Rob "JerryS" <JerryS@discussions.microsoft.com> wrote in message news:70FA2D38-3FBB-4B39-BF30-CC294BF05B29@microsoft.com... >I have a spreadsheet that I need to share without the user being able to > unhide certain columns yet ...

Copying number to clipboard, subtracting 398 then pasting the value to overwrite the original
Hi. I am very new to this. I'd be really grateful if someone could help/guide me. I want to create a macro in Microsoft Word but I don't know visual basic. I want to be able to highlight a number then: - copy it to the clipboard - subtract 298 - paste the value to the Word document, overwriting the original text Hi John, There is no need to involve the clipboard if you are only changing the selected number. The following macro subtracts 298 from the selected number. Sub Subtract298() If IsNumeric(Selection.Text) Then Selection.Text = Val(Selecti...

check boxes
I would like to copy (hundreds) of check boxes in a spreadsheet. The checkbox must be assigned to a cell to work in a formula. When I copy the checkbox down however, all check boxes will either be checked, or unchecked. Is there a way to copy check boxes, when they are assigned to another cell, so that each check box can be used individually? I created the check boxes through the forms toolbar. Thanks for your help, this is a great forum and I only hope to give some day as much help as I am currently receiving! Hey, Mark- Things got busy yesterday, and I missed your post. You can t...

I created a field called FileName in Access the type is hyperlink then I imported from Excel the list of thousand of file names such as “\\MAIN-SERVER\DOWN-SERVER\My_Documents.doc“ after the import succeeded then I clicked to open the file, however it does not work, it won't open the file. I tried an experiment and paste that file name in Word, Ctrl-Click does not open it. So, next I put the cursor to the end of File Name hit the Backspace button which invalidates the linkage, then I hit the Enter button after which Word made the File Name a linkage again. I do the Ctrl...

I have a field on a form for email address. I have the field in the form, and the underlying table, formated as a hyperlink so once you enter an email into the feild it will automaticaly open an email to send to that address. Works great accept it is difficult to edit the field because if you click on the field it opens the hyperlink instead of letting you edit the field. How should I get around this? Terry. ...

Unprotecting multiple worksheets w/ Macro
I am protecting with a macro, multiple worksheets in a workbook with a macro. I can't get the unprotect of multiple worksheets to work. Does anyone have a working macro to do this. _____________ Tippy Sub UnprotectSheets() Dim sh as Worksheets for each sh in ThisWorkbook.Worksheets if sh.ProtectContents or sh.ProtectScenarios or _ sh.ProtectDrawingObjects then sh.unprotect Password:="ABCD" end if Next End Sub -- Regards, Tom Ogilvy <tippy@att.net> wrote in message news:c7vlpv0sqb9eov641aj31ohnqnps7hvgvm@4ax.com... > I am protecting with a macro,...

2006 calendar on separate worksheets with several cells within a d
Does anyone know where I can download the above? "2006 calendar on separate worksheets with several cells within a d" What's the rest of the message? For Excel Calendar Templates visit http://office.microsoft.com/en-us/templates/CT011377111033.aspx Gord Dibben Excel MVP On Mon, 12 Dec 2005 10:25:02 -0800, "2006 Calendars" <2006 Calendars@discussions.microsoft.com> wrote: >Does anyone know where I can download the above? ...

Formatting lost when copied by formula!
Hi, I'm having a problem retaining format in a target cell where a formul "copies" the contents from another cell. Here's an example tha describes the problem in detail: Say I have the following text in cell A1: "Some text." Say, also, tha the word "text" is bolded. In cell B2 I have a formula, =A1, tha "copies" the content of cell A1. I can see the content of cell A1 i B2, but without the bold (or any other formatting, including backgroun color, etc.). My question, of course, is how do I retain the original formatting? Thank you -- Message ...

Copying Info Into Numerous Cells
I have just completed creating and entering data into thousands of worksheets. Now someone has decided they want to change and add columns to my worksheets. Is there any way to have data copied from 1 cell to another in several worksheets at a time, even if the data is different? If there isn't...I'm going to have a melt-down right here at work! :eek: -- calimari ------------------------------------------------------------------------ calimari's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=24537 View this thread: http://www.excelforum.com/showthr...

Can I use a worksheet name in a formula?
I'm using Office 2007 I have a workbook with 20+ pages. On one of them I want to use a formula that refers to a worksheet name, but in different cells I want it to refer to different worksheets. I have a cell that currently has a name typed into it that matches a worksheet name. Can I refer to the contents of that cell (INDIRECT) and use the content to refer to the correct worksheet and then go to that worksheet for the data? eg. Sheet called Christmas. In cell H2 value = Pudding on another sheet cell B2 value = Christmas. I want the formula in cell J12 to go to Sheet C...

Hi all Is there a way to set a column width so that it auto-adjusts to the widest entry in the column? And what about the widest numerical entry, excluding word-wrapped cells containing text? I realise that I can double-click on the edge of the column header to achieve most of the desired result passively, but this does not work if the worksheet is protected. Thanks -- Return email address is not as DEEP as it appears Hi depending on your Excel version you can allow formating columns in the protection dialog. (I think starting with Excel 2002). In all other cases no chance but to remo...

GUID Columns in SQL 2005
Are there additional things you need to do when backing up and/or restoring databases which have tables containing guid/uniqueidentifier columns? Thanks in advance Please, ignore this question. "RG" wrote: > Are there additional things you need to do when backing up and/or restoring > databases which have tables containing guid/uniqueidentifier columns? > > Thanks in advance ...

Change Columns to Letters
I want to run a CountIf statement E.g =COUNTIF(A1:A100,"General queries") However, on the columns there are no letters, only numbers. So both the rows and the colums have letters. Does anyone know how i can change the columns from numbers to letters. Thanks for helping Dave -- David494 ------------------------------------------------------------------------ David494's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=24482 View this thread: http://www.excelforum.com/showthread.php?threadid=391226 If anyone still knows how to do this then can you...

The same worksheet has different page numbers for different users
Hi all, I have a problem where two users opening the same file and having the same page set up, end up with printed documents of different page counts when viewed through print preview and when printed. The only difference is that one user is on a W2K machine, the other on Windows 95. Has anyone any suggestions? The first suggestion is quite obvious (upgrade the '95 system) - but would expect the difference in page lengths (I presume the '95 syste fits less data to the page, thus producing more pages) would b attributed to Win2000 having a better grasp of variable width fonts -- ...

inserting more than one column
What would the VBA code be to insert 6 columns to the left of Col A? I recorded a macro but I know there has to be a quick line of code that will do the same things rather than 10 lines of code from the macro recorder. Thank you. Hi Columns("A:F").Insert Shift:=xlToRight -- Regards Roger Govier "SITCFanTN" <SITCFanTN@discussions.microsoft.com> wrote in message news:BD9FA6CB-C861-4BCB-93C9-E98232636594@microsoft.com... > What would the VBA code be to insert 6 columns to the left of Col A? I > recorded a macro but I know there has to be...

Using an equation to count non-duplicated items in column
I have a list of trailers in a sheet that are designated as T-####. Is there a way to count the number of non-duplicated entries in this column by using a formula? Thanks for any assistance, Please respond to hers2keep @ yahoo . com. Thanks, carla carla Try Chip Pearson's site........ http://www.cpearson.com/excel/duplicat.htm#CountingUnique BTW. The customary response is to the News Group, not email. That way we all learn. Gord Dibben Excel MVP XL2002 On 28 Aug 2003 14:30:58 -0700, cbr@saturnsea.com (carla) wrote: >I have a list of trailers in a sheet that are designated as...

Switching rows and columns
I am quite sure, in a long forgotten history, Lotus 1-2-3 had an option allowing one to switch column contents to rows and vice versa. I must be looking for the wrong keywords in help and google, can someone tell me how to swap rows and columns in excel? Alternatively, same question for OpenOffice Calc, which I also work with... THANKS! Sh. I don't use OO. But in Excel, you can select your range to transpose. then Edit|Copy Then select the top left cell of the new range edit|Paste special|check Transpose and then ok. You'll have to make sure that there is no overlap between the two...

Can the preset numbers in the column be changed?