Extracting URL data from Hyperlinks in Excel - Step 1?

[theyuiop]

Hi

I am moderately advanced Excel user, but have had no VBA experience or learning.

I need to extract the URL address's in text from a single column of Hyperlinks. But need to know the very basic steps.

What i need to do....
EG
Column A
Home (hyperlink in excel)

And what i need is

http://www.tek-tips.com/index.cfm

(raw hyperlink in text or text hyperlink)


There has been a few threads about this and respondents have even posted the VBA module code that i probably will need.

Such as in this one...

http://www.tek-tips.com/viewthread.cfm?qid=1147426

Trouble is i can't get it working. I have created new VBA module and then tried to run the process, but just get an error message.

Would anyone be kind enough to check the code for a single column (column A) of hyperlinks and assist me in getting the module to run? The very basics for a novice.

I am copying the Data from a forum, the Hyperlink has the author adjacent to the Hyperlink in the same cell.

For Example
Hypertext plain text
Favourite cover? by mattyfu

Thanks!

The Yuiop

 

[ck1999]

Did you read macropod last post to the link you posted?

Does not get more step by step than that.
And yes the code works.

your cell b1 should have in it =HLinkAddr(A1)

ck1999

 

[theyuiop]

Oh Hi ck1999,

Sorry, but...
I checked today if i had a reply but could not find my thread. I have just checked then and it is not in this "Microsoft: Office Forum" thought it was moved but could not find it.

Also, clicked on "My Threads" and it said "zero" threads by me.

So can you help and link me to the thread that you saw a reply in? Or if that good person could post the simply answer a
again?

Thanks in advance!

The Yuiop

 

[ck1999]

Did you read the link you posted

Such as in this one...

http://www.tek-tips.com/viewthread.cfm?qid=1147426

read macropod's last post and use his procedure he posted earlier in the thread.

It works perfectly for me

ck1999

 

[theyuiop]

Oh Ok,

I don't think you are understanding that i need the very basics of novice help in regards to getting the module to work.

I have the VBA module code, I have the data of hyperlinks. But I can't make it work.

I am complete novice with VBA......so i need help like this;

Step 1:....open this...
Step 2.....click here.
Step 3:....paste code here..

Etc

I feel embarrassed having to ask such basics but as it hasn't worked for I thought someone would kindly walk me through the simple process.


Thanks in advance, again!

the Yuiop

 

[theyuiop]

so this is what i need....

I didn't see this response....sorry noob blindness!

can you kindly confirm these are the steps i need to use?

In the workbook in which you want the function to work:
1. Open the VBE (Ctrl-F11)
2. If you don't already have a code module attached to the workbook, add one via Insert|Module
3. Activate the module
4. Insert the 'HLinkAddr' function into the module
5. Activate your worksheet
6. Insert the formula: =HLinkAddr(A1), replacing 'A1' with your hyperlink cell's address
7. If the formula works (as it should), close the VBE

 

[macropod]

Correct!

[MS MVP - Word]

 

[ck1999]

1. Open your workbook and go to the correct sheet
2. Open the Visual Basic editor
3. Make sure you have a module for the work book look inthe project explore pain on the left
you should see vbaproject(workbookname) under this you should see microsoft excel objects underthis there should be a folder name modules. if so see if there is something below the folder molules that says module1. if you do not see the folder or anything below the folder. Click insert module.

Copy the code below ( from macropod on other thread)

Public Function HLinkAddr(Source As Range)
Dim HAddr As String
Dim HSubAddr As String
HAddr = Replace(Source.Hyperlinks(1).Address, "/", "\")
If Trim(HAddr) = "" Then HAddr = ThisWorkbook.FullName
If InStr(HAddr, "..\") Then HAddr = ThisWorkbook.Path & Replace(HAddr, "..\", "")
If InStr(HAddr, ":") = False Then HAddr = ThisWorkbook.Path & "\" & HAddr
HSubAddr = Source.Hyperlinks(1).SubAddress
If HSubAddr <> "" Then HAddr = HAddr & " - " & HSubAddr
HLinkAddr = HAddr
End Function

paste into new module you inserted

close the vba window

place hyperlink in a1
in cell b1 type =HLinkAddr(A1)

ck1999

 

[theyuiop]

THANK YOU ALL!

Really appreciate you guys walking this VBA noob through kindergarden ! lol....


So easy really.....

The Yuiop