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
    
If pRange.Hyperlinks.Count = 0 Then
       Exit Function
End If
    
LPath = ThisWorkbook.FullName
    
ST1 = pRange.Hyperlinks(1).Address
ST2 = pRange.Hyperlinks(1).SubAddress
    
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
    
HyperLinkText = ST1Local 

End Function

Thanks for any reply !


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

4 Replies
606 Views

Similar Articles

[PageSpeed] 55

Try this one

If I remember correct this is from
Dick KusleikaDick Kusleika
Dick Kusleika
Sub ShowLinks()
Dim hlnk As Hyperlink
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 CurrAdd As String
Dim GoBack As Long
Dim CurrFldr As String
Dim CAddStrip As String
Dim i As Long
Dim OldDir As String

CurrAdd = hyp.Address
CAddStrip = Replace(CurrAdd, "..\", "")
CurrFldr = hyp.Parent.Parent.Parent.Path
OldDir = CurDir

GoBack = (Len(CurrAdd) - Len(CAddStrip)) / 3

If GoBack > 0 Then
    ChDir CurrFldr

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

    If Not CurDir Like "?:\" Then
        CAddStrip = "\" & CAddStrip
    End If

    HypToPath = CurDir & CAddStrip

    ChDir OldDir
ElseIf Mid(CurrAdd, 1, 2) = "\\" Then
    HypToPath = CurrAdd
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
>
> ST1 = pRange.Hyperlinks(1).Address
> ST2 = pRange.Hyperlinks(1).SubAddress
>
> 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
>
> HyperLinkText = ST1Local
>
> End Function
>
> Thanks for any reply !
>
>
> Kontiki 


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

> Dick KusleikaDick Kusleika
> 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 KusleikaDick Kusleika
> Dick Kusleika
> Sub ShowLinks()
> Dim hlnk As Hyperlink
> 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 CurrAdd As String
> Dim GoBack As Long
> Dim CurrFldr As String
> Dim CAddStrip As String
> Dim i As Long
> Dim OldDir As String
>
> CurrAdd = hyp.Address
> CAddStrip = Replace(CurrAdd, "..\", "")
> CurrFldr = hyp.Parent.Parent.Parent.Path
> OldDir = CurDir
>
> GoBack = (Len(CurrAdd) - Len(CAddStrip)) / 3
>
> If GoBack > 0 Then
>    ChDir CurrFldr
>
>    For i = 1 To GoBack
>        ChDir ".."
>    Next i
>
>    If Not CurDir Like "?:\" Then
>        CAddStrip = "\" & CAddStrip
>    End If
>
>    HypToPath = CurDir & CAddStrip
>
>    ChDir OldDir
> ElseIf Mid(CurrAdd, 1, 2) = "\\" Then
>    HypToPath = CurrAdd
> 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
>>
>> ST1 = pRange.Hyperlinks(1).Address
>> ST2 = pRange.Hyperlinks(1).SubAddress
>>
>> 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
>>
>> HyperLinkText = ST1Local
>>
>> 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 KusleikaDick Kusleika
>>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 KusleikaDick Kusleika
>>>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
Reply:

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...

Hyperlink that does not link
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...

Hyperlink Edit
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...

Auto-adjust column width
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?
I am trying to do a sign in sheet and would like horizontally at the top name, address etc, and name is in A1, address is in B1 etc. for example. Is there a way to make it so the numbering will reflect how many people sign in without adding a column with numbering? Thanks in advance. Teresa "=?Utf-8?B?c2hld2FoeWE=?=" <shewahya@discussions.microsoft.com> wrote in news:177A3969-6C4A-4633-BA49-32449EBBCABE@microsoft.com: > I am trying to do a sign in sheet and would like horizontally at the > top name, address etc, and name is in A1, address is in B1 etc. for >...

Total a column from sheet 2 based on value in sheet 1
Col B Col C Col F Mary Team 1 $331.00 George Team 1 $222.00 Sam Team 2 $186.00 Tom Team 2 $100.00 Above is an example of my data on Sheet 2. On Sheet 1, I want to total all the total funds raised per Team shown on Sheet 2. I am trying to create a summary of what each team raised. I tried using the formula: =SUM(('sheet 2'!F2:F482=Sheet1!B2)) and I get just a dash in my total col. Can anyone provide some help? Thanks Look in HELP for the SUMIF() function -- Kind regards, Niek Otten Microsoft MVP - Excel "Nee...

copy protection
Hi I'd like to find out if this is possible in Excel. When creating an Adobe Acrobat file, the security options can be set so that no content copying or extraction allowed. Meaning, you cannot highlight text and right-click to copy, etc. Can this be done in Excel (or in Word for that matter) ? Steve Steve, Not in any way that will ultimately prevent a determined user. HTH, Bernie MS Excel MVP "Steve" <none@none.com> wrote in message news:#MYsaCmAEHA.916@tk2msftngp13.phx.gbl... > Hi > > I'd like to find out if this is possible in Excel. > > When...

Reseting the last row/column
Hello all, I have a spreadsheet of data, lets say 35,000 rows long. So when I do a [Ctrl] + [End], it goes to the cell at row 35,000 (and the last column of the data). Now lets say that I run a macro that deletes 5,000 rows. So now the last row is 30,000. But if I do a [Ctrl] + [End] again, it still goes to row 35,000. Is there any way to reset the spread sheet so it knows that the last row/column of data has changed since row/column deletions? Thanks for any help anyone can provide, Conan Kelly Here is an article from Msft on the topic: http://support.microsoft.com/?kbid=2444...

All day event (Birthday) start time setting for a WORK day, not full day.
Hello, this problem is literally robbing my sleep. I was using Outlook 2003 and now updated to Vista and Outlook 2007. If I create a new contact and enter his/her birthday, Outlook will create a new all day event for this birthday. This is being synched to my Windows Mobile PDA. So far so good. In OL 2003 this birthday "all day event" would remind me 15 minutes before I start working at 8 am. After the Upgrade 2007 now starts an all day event at 0:00 am. This means, my PDA now reminds me at 11:45 in the night about a birthday. How can I set this back? Or how can I tell Outloo...