How can a macro get the hyperlink specified in a HYPERLINK function?

English
,
#1

I am trying to access in a macro the URL specified by a HYPERLINK function in a formula of a spreadsheet. This should work regardless of how the formula is constructed.

Test case

The file Get_Hyperlink.ods (14.4 KB) has example formulæ in A1, A2 and A3, yielding links to https://duckduckgo.com, https://google.com and https://ask.libreoffice.org/ respectively. If the function Get_Hyperlink worked as desired, positioning the cursor to one of those cells and running macro Tester__Get_Hyperlink would produce a message saying “Hyperlink is ‘https://duckduckgo.com’”, or one of the other links as appropriate.

Similar questions

The question at Evaluate a HYPERLINK formula's target URL seems to be the same question, but the macro given by Zizi64 in a file GetURL.ods just seems to be about the URL of the document itself.

Proposed solutions

Apache OpenOffice: forum della comunità - [Risolto] copiare solo Link collegamento ipertestuale - (Leggi argomento) and the function CELL_URL in the [Calc, Basic] Introspective cell functions at Apache OpenOffice Community Forum - [Calc, Basic] Introspective cell functions - (View topic)
access it as cell.getTextfields.getByIndex (0).URL, by my cell has no Textfields (Count = 0).

ask283622extractURLdemo_1.ods supplied at extract hyperlink from a hyperlinked text to another cell - #4 by anon73440385
accesses it by scanning parts of cell.text, but my cell only has one l1Part, which has 1 l2Part, and both have as String the cell text, and the l2Part has TextPortionType = Text and TextField is (null).

It is obviously not a satisfactory solution to build a formula parser in basic! The only similar approach that would at a pinch be acceptable would be to use an API function that parses a formula much as the UI function Insert Function does, yielding a tree down which one could walk to find the HYPERLINK call, and extract the value of its second argument.

Experiments

cell.getPropertyValue ("Hyperlink") yields an empty string.

I also searched in various places such as https://forum.openoffice.org/, https://wiki.openoffice.org/, https://www.openoffice.org/api/docs/, but found nothing.

None of the fields of the cell shown by the debugger seemed to contain anything relevant.

#2

perhaps

	Get_Hyperlink	= Split(y.Formula, CHR(34))(1)

Strictly for the two examples in your file.

=HYPERLINK("https://duckduckgo.com","P")
=HYPERLINK("https://"&"google"&".com","Q")


Function Get_Hyperlink (y as Object) as String
	Dim	z	as	String	:	z	= ""
	
	t  = Split(Split(Split(y.Formula, ";")(0), "(")(1), "&")
	
	If Ubound(t) = 0 Then
		z = Replace(t(0), CHR(34), "")
	Else
		For i = LBound(t) To UBound(t)
			t(i) = Replace(t(i), CHR(34), "")
		Next
		z = Join(t, "")
	End If
	
	Get_Hyperlink	= z
End Function
1 Like
#3

No, I’m sorry, but that is absolutely not what I mean! In that spirit I might as well write
Get_Hyperlink = "https://duckduckgo.com/"

My example should probably have had a few more complex formulæ, but I want it to work on any formula using HYPERLINK, and the last thing I want to do is to write a formula parser in this Basic! I shall update my question to avoid that misunderstanding.

#4

Have you even tried it?

#5

No! Maybe I have underestimated it; if so, please accept my apologies! I shall try it now.

#6

I have now tried it. and it does not work on A2 in my updated test case.

That has the formula =HYPERLINK("https://"&"google"&".com","Q") and your expression returns the string "https://". Had you tested it on my updated sheet?

The problem seems to be that y.Formulacontains the formula exactly as it appears in the cell in the sheet.

I might mention that in my use case the links are also composed of several concatenated components, only some being string literals.

#7

The suggestion by @elmau assumes that the hyperlink string is given as one complete literal. If it is the result of an expression like "https://" & "duckduckgo.com" or A1 & Z15 the approach will fail.

1 Like
#8

It fails even worse if both arguments are cell references, as it then crashes with an out of range array reference!

#9

Yes. In fact, I looked for a solution to this problem a few years ago and couldn’t find an acceptable one.
I had a very ugly “brute force” idea, but I didn’t really try it out:

  • Take the formula and memorize it.
  • Cut out the characters one by one from the end, but before the closing parenthesis, and assign the resulting string to the .Formula property.
  • At some point, the decisive parameter separator will be gone.
  • Now the formula is correct again, and the string value of the cell gives the link you are looking for.
  • Reassign the original formula to the cell.
#10

Yikes! I think I would rather hack the source!

P.S. Not that I have any idea how to find the relevant code or how to expose a function via UNO! But I do have the source to look at.

#11

https://opengrok.libreoffice.org/xref/core/sc/source/core/tool/interpr2.cxx?r=828221842b77e15e2115aff106906b95051919c3&mo=103626&fi=3185#3185

#12

And then build your personal LibreOffice every other day? Surely you are joking …
Any approach of the one (API-basded macro) or the other (hacking) way would basically destroy compatibility/exchange with other spreadsheet software, persons using a different LibO build, or not permitting the execution of document macros.
If you think this way I can only conclude that your spearsheets are home-made and only expected to meet your own wishes/needs.
If so: Accept the need of some interactive work to redesign your sheets giving them a very simple structure: Use dedicated columns: One for the insertion or calculation of the link strings, one (best adjacent) for the calls to the HYPERLINK() function making the link “working”.
Done!

If your sheets have a confused structure or thousands of HYPER$LINK() calls, you may need support for the extraction of badly placed links.
In this case you may modify the “brute-force-approach” described above to work with the .Tokens property instead of the formula string. This would be more efficient.

#13

@mikekaganski:
How are the “mouseover-tips” showing the link created?

1 Like
#14

I couldn’t withstand to demonstrate that my ideas sometimes work to some degree.
Make sure to understand that refinements are left to you.
disask125278_getLinkResultFrom HYPERLINK.ods (14.6 KB)

1 Like
#15

A note for myself, when/if I get to debugging: put a breakpoint in SfxHelp::GetURLHelpText

#16
#17

The idea is great, the implementation is not complete. :slight_smile:
And of course, this function should not be used as a UDF (in cell formulas), since the function changes the value of the cell.

@PJTraill , will the function we create be used as a UDF in formulas? If so, I don’t see any legitimate ways.

#18

The changing of the formula (.Tokens) in the cell containing the analyzed HYPERLINK() is part of the concept. The needed reference is passed in a way which is typical for VBA supported UDF calls from cells and the demo actually calls the UDF from cells.
You will get an “accident” if the changes to the referenced HYPERLINK() cell leads to uresolvable problems via dependencies.
In fact I don’t know for what reason the violation of the respective “law” doesn’t thwart the execution generally. Even a loop which I created voluntarily in an additional example had “only mild consequences”.
*I didn’t test with a version passing the cell info without VBAsupport using the triple *
(Sheet, Row, Column).
Meanwhile I did.
Result: Under LibO V 25.2.4 (Sheet, Row, Column) without VBAsupport also worked.

#19

tdf#167886

Those interested can join to increase the likelihood of the proposal being implemented. :slight_smile:

1 Like
#20

formulaparser.ods (15.7 KB) (version 0.2)

  • Displays the hyperlink formula below the named cell.
  • Works with non-English UI.
1 Like

Impressum (Legal Info) | Datenschutzerklärung (Privacy Policy)   Content on this site is licensed under a Creative Commons Attribution Share Alike 3.0 license.