Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
DiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWord
DirectoryUser Groups
Related Topics
Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ...

MS Office Forum / Excel / Programming / June 2006

Tip: Looking for answers? Try searching our database.

convert table of hyperlinks

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
Jakobshavn Isbrae - 22 Jun 2006 21:15 GMT
I have a large table (over 600 entries) of hyperlinks.
They are all in a single column.
They are all of the form:   =HYPERLINK("http://www.something.com","something")
I need to convert the table into a table of hyperlinks without function
calls.  The result table would look like it was made by using  
Insert=>Hyperlink...

I have tried various copy/paste specials and they all failed.

I am not much of a programmer, but I can copy and paste into the VBA editor.
Thank you in advance for any guidance you give me.
Signature

jake

Mike Fogleman - 23 Jun 2006 00:03 GMT
Jake, this may not look to efficient but it will do the job that you
described.

Sub convrt_hyper()
Dim oldHyp As Hyperlink
Dim newHyp As String
Dim HypAdr As String
Dim Hyptxt As String
Dim ln As Long  'string length
Dim cnt As Long 'comma position
Dim rng As Range
Dim Lrow As Long

'in next line change A to the column letter
'where your old hyperlink functions are
Lrow = Cells(Rows.Count, "A").End(xlUp).Row

'change both A's to match column letter in line above
'change the 1 to the rownumber they begin on
Set rng = Range("A1:A" & Lrow)

'the following will put the new hyperlink
' in the next column to the right
For Each oldHyp In rng.Hyperlinks
   HypAdr = oldHyp.Address
   cnt = InStr(1, HypAdr, ",", vbTextCompare)
   ln = Len(HypAdr)
   newHyp = Left(HypAdr, cnt - 2)
   Hyptxt = Mid(HypAdr, cnt + 2, ln - cnt - 2)
   ActiveSheet.Hyperlinks.Add Anchor:=rng.Offset(0, 1), Address:= _
   newHyp, TextToDisplay:=Hyptxt
Next oldHyp
End Sub

If this doesn't quite do it for you, we can tweak it to work.
Mike F
>I have a large table (over 600 entries) of hyperlinks.
> They are all in a single column.
[quoted text clipped - 9 lines]
> editor.
> Thank you in advance for any guidance you give me.
 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2008 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.