Read hyperlink from Excel without URL

Help Studio
1

Hi,

how do I read hyperlink when cell is like this:
image

There is no url but when I hover over the text, it shows the url. How do I get the URL?

2

hi @markosc

kindly look this

best regards!

3
  1. first create a txt file e.g. “Data\macro.txt” with this code inside
Function GetURL(cellAddress as string) As String
GetURL = Range(cellAddress).Hyperlinks(1).Address
End Function

  1. in your invoke VBA activity, pass in the cell where you want to read the hyperlink
    image

  2. assign output to an object variable e.g. “output”

    image
    image1201Ă—386 23 KB

  3. print output using output.tostring
    image

My example:
Excel:
image
workflow:

image
image913Ă—545 23.8 KB

Result:
image
TestGetLink.xaml (6.4 KB)
test.xlsx (8.8 KB)

1 Like
4

Thank you! Now how do I do this if I have like 1000 rows? Because I don’t want to do this like 1000 times. For example I have range A2:A1000 and I have to do this for every row (cell)

5

just use a foreach loop
here enumerable.Range(2,11) means start at row 2 and end at row 12

image
image1171Ă—499 22.2 KB

then in the vba call , change the parameter to this

image

also, change the macro.txt to this to handle cells with NO hyperlinks (will output “No Link” if cell has no hyperlink)

Function GetURL(cellAddress as string) As String
On Error goto ErrorHandling:
GetURL = Range(cellAddress).Hyperlinks(1).Address
Exit Function
ErrorHandling:
GetURL = "No Link"
End Function

macro.txt (194 Bytes)
test.xlsx (9.2 KB)
TestGetLink.xaml (7.4 KB)

image
image

1 Like
6

if you have A2:A1000, just do enumerable.Range(2,999)

and for VBA parameters do {“A”+rowNumber.toString}

7

Thank you so much! You helped me a lot.

1 Like
9

This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.