• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 173
  • Last Modified:

Excel VBA question

Ok.  I have a column full of hyperlinks in Excel.

I need to find out how to seperate the 'text' from the 'link'

Please help!!!
0
AAMFK
Asked:
AAMFK
1 Solution
 
bobbit31Commented:
don't really understand:

    Dim url As String
   
    url = Sheet1.Cells(1, 1) '' this is a hyperlink
    MsgBox url

all i get is text what do you mean by the 'link'
0
 
wileecoyCommented:


Ok - try this - this is with the 'Selection'.  You can also use it with the 'Worksheet'.


Dim h As Hyperlink

For Each h In Selection.Hyperlinks
    MsgBox h.Name
    MsgBox h.Address
Next


HTH,

Wileecoy.
0
 
Richie_SimonettiIT OperationsCommented:
Hearing for further details...
0
Cloud Class® Course: Ruby Fundamentals

This course will introduce you to Ruby, as well as teach you about classes, methods, variables, data structures, loops, enumerable methods, and finishing touches.

 
bruintjeCommented:
Hey thanks Wileecoy, that's what i was after ;) i used it with the worksheet object but didn't look for a narrower thing with the selection you can use it in a range and do something like

i=1
Range("A1:A20000").select
For Each h In Selection.Hyperlinks
   Range("B" & i).value= h.Name
   Range("C" & i).value= h.Address
   i=i+1
Next

going through the worksheet took to much time with 16000 hyperlinks and only half of them relevant

:O)Bruintje

PS not meant as comment to this question
0
 
wileecoyCommented:
Any progress AAMFK?
0
 
AAMFKAuthor Commented:
uh i ended up saving it as a HTML and parsing--

but ill award these points thanks
0
 
bruintjeCommented:
wileecoy, since an accepted answer can't be reversed i'll post a seperate Q for you here

http://www.experts-exchange.com/visualbasic/Q_20334794.html

:O)Bruintje
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: CompTIA Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now