How to put a hyperlink into a cell using BASIC

nap · January 13, 2022, 1:00pm

I need to be able to set a cell’s value to be a hyperlink which can be clicked.
I know I can use the =HYPERLINK() function if I put it as a formula into a cell, but how do you do this from BASIC such that the hyperlink can be clicked (without requiring any further formatting in the spreadsheet itself)?

joshua4 · January 13, 2022, 1:21pm

Can you clarify if you are okay with the user Ctrl-clicking to follow the hyperlink and if you want the link to look different or not? I’ll admit, I thought you could just use

ThisComponent.Sheets().getByIndex(0).getCellByPosition(3,3).Formula = "=HYPERLINK(""https://www.libreoffice.com"",""Go to TDF"")"

but while it looks identical, it produces a 508 error.

nap · January 13, 2022, 1:32pm

@joshua4 thanks, that was a quick response.
As you may have already noticed, I posted an rudimentary answer that I worked out from some code from another example I was able to find.
However, you brought up several points that I consider nice to have. (I’ve only achieved the basic functionality thus far.)

I don’t mind having to hold Command (I’m on a Mac) but I’m interested in how to make it so that’s not required.
I would also like to know how to make the URL appear as a URL (formatting wise).

nap · January 13, 2022, 1:26pm

I figured it out from a more difficult example: Use macro to insert hyperlink on one sheet to link to another sheet in same document - #12 by JohnSUN

			url = "https://website.com/" & Result.getLong(Result.findColumn("typeID"))
			urlFormula = "=HYPERLINK(""" + url + """;""" + materialName + """)"
			oSheet.getCellByPosition(2,row).setFormula(urlFormula)

In the above code; I’ve got the typeID, from a MySQL database, which I’m using to construct the URL. Then, I’m constructing the formula string that will go into the cell, and last, I’m assigning the formula string to the nominated cell as a formula.

joshua4 · January 13, 2022, 1:31pm

Yep, I just had the “,” instead of “;”…old Excel habit. Sorry.

ThisComponent.Sheets().getByIndex(0).getCellByPosition(3,3).Formula = "=HYPERLINK(""https://www.libreoffice.com"";""Go to TDF"")"

Lupp · January 13, 2022, 2:01pm

If you want to get a link directly anchored to a (piece of) text in a cell, you can create and insert a textfiled like discribed (e.g.) in Andew Pitonyaks famous text “Useful Macro Information”, subchapter 5.18.5, Listing 5.53. (For the macro cell.Text is the same objkect as the cell again.)
Example:

Sub insertLinkedTextIntoSheetCell()
doc = ThisComponent
explSheet = doc.Sheets(0)
explCellN = "D11"
explURL = "https://ask.libreoffice.org/c/english/5"
explURLanchor = "You may visit the disask site."
cell = explSheet.getCellRangeByName(explCellN)
tfURL = doc.createInstance("com.sun.star.text.TextField.URL")
tfURL.Representation = explURLanchor
tfURL.URL = explURL
tc = cell.Text.createTextCursorByRange(cell.Text.Start)
cell.insertTextContent(tc, tfUrl, False)
End Sub

Whether you use a HYPERLINK() formula or a linked textrange makes some differences now and then.