Awsok


hi All

I have an excel sheet that have some cells with text and under the text there is a hyperlink to another files to the network driver

Does any one knows how to extact the value of the hyperlink (not the text value that how up).

I tried many variations like =CONCATENATE("link ",T(HYPERLINK(A1)))

but it didnt help !!

Thanks for participating

Aws





Re: how to get the hyperlink value from an excel sheet cell

Peter Mo.


Hi

Does this help

Code Snippet

Dim hl As Hyperlink

For Each hl In Sheet(N).Hyperlinks
MsgBox "Range " & hl.Range.Address & " addr " & hl.Address & " text " & hl.TextToDisplay
Next hl

Regards

Peter Mo.






Re: how to get the hyperlink value from an excel sheet cell

Awsok

Thanks Peter...

Your function helps if I run it from within Visual Basic Editor as a macro or sub

How can I get the same hyperlink address from within Excel, by referencing the cells !

The thing I am trying is to export data from Excel to Oracle by writing something like

="Insert into stofbib (Plate_ID,Well_ID,Compound_ID,Stofoversigt,Dokumentation, Fremstillet_af,Modtaget_dato,Projekt,ml_leveret) values ('" & A5 & "','" & B5 & "','" & C5 & "','" & D5 & "','" & E5 & "','" & F5 & "','" & G5 & "','" & H5 & "','" & I5 & "');"

Some of these cells have a hyper link under them that I want to get, without I need really to make a VBA program!

Any idea if I can get that







Re: how to get the hyperlink value from an excel sheet cell

Awsok

Whats up guys

All Microsoft muscles can not solve this little trick

Where are the experts of Excel

Please if you know another forums to post this question, or any one who might know better people than Microsoft MVP, then please let me know L






Re: how to get the hyperlink value from an excel sheet cell

Peter Mo.

Hi

If you can't a better answer you could always try a user defined function ...

Code Snippet

Function GetHyperLinkAddress(rng As Range) As String

Dim hl As Hyperlink

For Each hl In rng.Parent.Hyperlinks
If hl.Range.Address = rng.Address Then
GetHyperLinkAddress = hl.Address
Exit Function
End If
Next hl
GetHyperLinkAddress = "Not Found"
End Function

Regards

Peter Mo.





Re: how to get the hyperlink value from an excel sheet cell

Ref4E_Bob

Peter

I don't program and can't understand the post. I have 5,000 cells in one column. 90% contain hyperlinks. I need to correct the hyperlink information using data from another cell in their individual row. How can this be done Just having the ability to display the hyperlink in an empty cell on the row and then sorting would be nice. Having the ability to globally change the hyperlink to the correct data would be heaven!

Robert





Re: how to get the hyperlink value from an excel sheet cell

Duane in Japan

I am not good enough with VB or VBA to give you the answer but I use an external piece of software and it may do what you want and it may work pretty fast also.

Can you please post a sample of your excel cells and explain in great detail exactly what you would like to do if it could be done in any way, shaper or form, lets assume that VB or VBA is not going to be used, cause if I think you want to do what I think you want to do, I can probably do it with another piece of software, this of course is assuming that you dont absolutely need to use VBA.

Can you do what you want to do by hand for just one row of info, grab the hyperlink, put it in its own column, repair it or update it to a new address and since it is in its own column now, this will make this column of hyperlinks or the entire sheet sortable based on the column of hyperlinks

If you can do it one time, I can do it 5000 times automated, I hope. What is different about the 10% without links and the 90% of the rows with links. Do I understand that the links are not visible but hidden behind the text in a cell Or just click on the text and it goes to the link address, the link dialog box should show the text in the top section but the link address in the bottom section if I am thinking correctly

Sorry I am not much of a VB guy even I been playing with it for some time, I just have an extremely bad memory to do anything these days.





Re: how to get the hyperlink value from an excel sheet cell

Ref4E_Bob

Pictures\ST2E0D016F.JPG ST2E00016F Brunt, FCA Liquidator
Pictures\ST2E0D017F.JPG ST2E00017F Charles Tucker III, "Trip"
Pictures\ST2E0D018F.JPG ST2E00018F U.S.S. Enterprise-D, Personal Flagship
Pictures\ST2E0P074F.JPG ST2E00074F Gowron, Leader of the High Council
Pictures\ST2E0P075F.JPG ST2E00075F Amnesty Talks

Right clicking on "Brunt....." and selecting "Edit Hyperlink" displays "Pictures/ST2E0D016F.jpg" which is what I want to be there (the subdirectory is co-located with the excel file). However a great percentage of the 5,917 other entries still have the hyperlink to the original web site of http://decipher.fanhq.com/TCG/Card.aspx gameID=8&cardID=908 (card number changes).I desire to change all of the hyperlinks to the value in the first column but can't figure out how to set up a macro to do this.

I can manually left click on the first cell in the rom three times to highlight in edit mode the value there and the CTRL-C. I then right click on the third column select "Edit Hyperlink" (if it exists) and paste the clipboard value) and then close the window. If a hyperlink doesn't exist at all, then I create one using the clipboard value.

It can be done manually but I can't figure out how to create a macro to do this.

Robert





Re: how to get the hyperlink value from an excel sheet cell

Duane in Japan

Pictures\ST2E0D016F.JPG

This is not a complete hyperlink above,

http://forums.microsoft.com/MSDN/Pictures/ST2E0D016F.jpg

Is this Plus it did not work, a dialog box says there is no such web address

I started in cell A10,

Sub Macro1()
'
' Macro1 Macro
' Macro recorded 12/2/2007 by Duane
'

'
Range("A10").Select
ActiveCell.FormulaR1C1 = "Pictures\ST2E0D016F.JPG"
Range("C10").Select
ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:= _
"Pictures/ST2E0D016F.JPG", TextToDisplay:="Brunt, FCA Liquidator"
ActiveWindow.SmallScroll Down:=1
Range("A11").Select
ActiveCell.FormulaR1C1 = "Pictures\ST2E0D017F.JPG"
Range("C11").Select
ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:= _
"Pictures/ST2E0D017F.JPG", TextToDisplay:="Charles Tucker III, ""Trip"""
End Sub

I am guessing that later you will add more rows, are you going to need to run a macro to fix them or will you keep new rows updated as they are entered

ActiveWindow.SmallScroll Down:=1
Range("A11").Select

This moved row 11 up to the top of the screen where row 10 was just located.

This does not change an incorrect hyperlink but you can remove 100% of the hyperlinks up front with a couple simple moves, copy the entire column, paste special (values) to a new column, then cut and paste this new column back to your original column, all the text will be hyperlinkless. Then run the macro above from cell A1 on down the line.



Here is an old hyperlink macro I used to hyperlink the actual text in column A to the link that was actually printed in the cell.

Sub Hyperlink()
'
' Macro1 Macro
' Macro recorded 3/29/2002 by duane.donaldson
'

'
Dim a As Integer
Dim cells, textCC As String
columnCC = 1
a = 1

cells = Sheet1.cells(a, 1).Value
a = a - 1
Do While cells <> ""

a = a + 1

cells = Sheet1.cells(a, 1).Value

' MsgBox (cells)


ActiveSheet.Hyperlinks.Add Anchor:=Sheet1.cells(a, 1), _
Address:=Sheet1.cells(a, 1).Value, _
TextToDisplay:=Sheet1.cells(a, 1).Value

On Error GoTo 0

Loop


End Sub

My friend who wrote this for me did not know how many rows to stop at because every sheet was different so we just let it run until it error and stopped it at that error dialog box, no biggie. This did not care what row you started at, in this case it was row 1, but if I changed
a = 1 to a = 311, then I could start it at a new updated start point, row 311, and the counter, a = a + 1 did the SmallScroll Down:=1 automatically without caring what the cell reference was. I think this last macro will help more than the stuff up top but because some of your items are already hyperlinked and some are not and I do not know how to do a good macro for them, my external piece of software would do the job from the sample that I tried.

This last macro could be tailored to your needs if you copy and paste special (values) like I mentioned above to get rid of all current hyperlinks. Lets see what the gurus have to say on the subject.





Re: how to get the hyperlink value from an excel sheet cell

Ref4E_Bob

Still digesting the macros...

The existing hyperlink (Pictures/ST2E0D016F.jpg) is used to link to a file in a subdirecory that is co-located with the excel spreadsheet, not to an external link, that is why it is short. This allows me to give the spreadsheet and the subdirecory containing the pictures to someone else without having to rename the links. As long as the subdirecory is in the same directory as the spreadsheet, the links will work.

Robert





Re: how to get the hyperlink value from an excel sheet cell

Duane in Japan

Copy and paste the entire spreadsheet to a new spreadsheet and lets play with the copied sheet. Highlight column C if that is where all your hyperlinks are, copy the entire column and paste special (values) to column E, all hyperlinks should be gone. You can delete C or cut and paste E to C to overwrite C. I do something similar all the time and I have updated what I do but I use an external piece of software but I want to try the macro or VBA to get this done for you, it should not be hard, I have another idea once you create a new copy as mentioned above, no chance to loose your original data this way.