[Solved] Multiple hyperlinks in one cell

Creating a macro - Writing a Script - Using the API (OpenOffice Basic, Python, BeanShell, JavaScript)
Post Reply
BrianME
Posts: 18
Joined: Sun Jan 03, 2021 9:30 pm

[Solved] Multiple hyperlinks in one cell

Post by BrianME »

I have a spreadsheet that right now I use the hyperlink dialog to insert 2 hyperlinks into one cell separated by a linefeed (CTRL Enter). I'm trying to automate this with a macro. I've got everything figured out except how to put the 2 links in the one cell. Using the =HYPERLINK function doesn't allow me to have 2 different links with 2 different text labels (that I've been able to figure out).

Using the Hyperlink dialog, I insert the first link, then exit the cell, go back to the cell and edit it using function f2 which takes me to the end of what's in the cell, then CTRL enter, and use the hyperlink dialog to enter the second link. All that shows up in the cell is the 2 text labels I applied in the dialog, and each is a link to a different pdf document.

Trying to duplicate this with a macro has me stumped. Is the only way to do this with the dialog? Is there a way to use, and interface with, the dialog from the macro?

I appreciate any and all help or suggestions.
Last edited by robleyd on Mon Aug 30, 2021 1:45 pm, edited 4 times in total.
Reason: Add green tick
OpenOffice 4.1.7 on Windows 10
JeJe
Volunteer
Posts: 2764
Joined: Wed Mar 09, 2016 2:40 pm

Re: Multiple hyperlinks in one cell

Post by JeJe »

Using the macro record (mostly)

Code: Select all


REM  *****  BASIC  *****

sub Main
rem ----------------------------------------------------------------------
rem define variables
dim document   as object
dim dispatcher as object
rem ----------------------------------------------------------------------
rem get access to the document
document   = ThisComponent.CurrentController.Frame
dispatcher = createUnoService("com.sun.star.frame.DispatchHelper")

rem ----------------------------------------------------------------------
dim args1(0) as new com.sun.star.beans.PropertyValue
args1(0).Name = "ToPoint"
args1(0).Value = "$C$1"

dispatcher.executeDispatch(document, ".uno:GoToCell", "", 0, args1())

rem ----------------------------------------------------------------------
dim args3(4) as new com.sun.star.beans.PropertyValue
args3(0).Name = "Hyperlink.Text"
args3(0).Value = "fish"
args3(1).Name = "Hyperlink.URL"
args3(1).Value = "fish"
args3(2).Name = "Hyperlink.Target"
args3(2).Value = ""
args3(3).Name = "Hyperlink.Name"
args3(3).Value = ""
args3(4).Name = "Hyperlink.Type"
args3(4).Value = 1

dispatcher.executeDispatch(document, ".uno:SetHyperlink", "", 0, args3())

rem ----------------------------------------------------------------------
rem dispatcher.executeDispatch(document, ".uno:SetHyperlink", "", 0, Array())
txt= thiscomponent.currentcontroller.selection.text
tc= txt.createtextcursorbyrange(txt.end)
tc.collapsetoend

'tc.string = chr(13)
'tc.collapsetoend

txt.insertControlCharacter(tc,_
com.sun.star.text.ControlCharacter.PARAGRAPH_BREAK, True)
tc.goright 1,false


args3(0).Name = "Hyperlink.Text"
args3(0).Value = "bats"
args3(1).Name = "Hyperlink.URL"
args3(1).Value = "bats"
args3(2).Name = "Hyperlink.Target"
args3(2).Value = ""
args3(3).Name = "Hyperlink.Name"
args3(3).Value = ""
args3(4).Name = "Hyperlink.Type"
args3(4).Value = 1

dispatcher.executeDispatch(document, ".uno:SetHyperlink", "", 0, args3())


end sub
Windows 10, Openoffice 4.1.11, LibreOffice 7.4.0.3 (x64)
BrianME
Posts: 18
Joined: Sun Jan 03, 2021 9:30 pm

Re: Multiple hyperlinks in one cell

Post by BrianME »

Wow, that's great. It took a little massaging to fit my code, but very little. And it works like a charm.

Thank you!!!!

I'll mark this as SOLVED.
OpenOffice 4.1.7 on Windows 10
BrianME
Posts: 18
Joined: Sun Jan 03, 2021 9:30 pm

Re: Multiple hyperlinks in one cell

Post by BrianME »

OK, I guess I spoke too quickly. When I step thru the code, everything works great, but when I just run it, it gets screwed up.

I clear the contents of the cell before putting the new hyperlinks in. When stepping, that all goes great. When I run it, the cell gets cleared but then when the first hyperlink gets pasted it, it's at the end of the contents that used to be there before I cleared it. I even put in a 5 second wait between the clear and the link paste and the cell really does look like it's been cleared. The old contents of the cell were 2 hyperlinks that I'm replacing with 2 new ones. But when I run, the old links reappear and the new ones get "appended" to the old ones.

Any ideas why the behavior changes when stepping vs. running?
OpenOffice 4.1.7 on Windows 10
JeJe
Volunteer
Posts: 2764
Joined: Wed Mar 09, 2016 2:40 pm

Re: Multiple hyperlinks in one cell

Post by JeJe »

This page shows you how to use a text cursor
https://wiki.openoffice.org/wiki/Writer/API/Text_cursor

Edit: that page is for a Writer document but the same principles apply to a cell text content.

To remove the existing contents set the text string to ""

Code: Select all

REM  *****  BASIC  *****

sub Main
rem ----------------------------------------------------------------------
rem define variables
dim document   as object
dim dispatcher as object
rem ----------------------------------------------------------------------
rem get access to the document
document   = ThisComponent.CurrentController.Frame
dispatcher = createUnoService("com.sun.star.frame.DispatchHelper")

rem ----------------------------------------------------------------------
dim args1(0) as new com.sun.star.beans.PropertyValue
args1(0).Name = "ToPoint"
args1(0).Value = "$C$1"

dispatcher.executeDispatch(document, ".uno:GoToCell", "", 0, args1())
txt= thiscomponent.currentcontroller.selection.text
txt.string= "" 

rem ----------------------------------------------------------------------
dim args3(4) as new com.sun.star.beans.PropertyValue
args3(0).Name = "Hyperlink.Text"
args3(0).Value = "fish"
args3(1).Name = "Hyperlink.URL"
args3(1).Value = "fish"
args3(2).Name = "Hyperlink.Target"
args3(2).Value = ""
args3(3).Name = "Hyperlink.Name"
args3(3).Value = ""
args3(4).Name = "Hyperlink.Type"
args3(4).Value = 1

dispatcher.executeDispatch(document, ".uno:SetHyperlink", "", 0, args3())

rem ----------------------------------------------------------------------
rem dispatcher.executeDispatch(document, ".uno:SetHyperlink", "", 0, Array())
tc= txt.createtextcursorbyrange(txt.end)
tc.collapsetoend

'tc.string = chr(13)
'tc.collapsetoend

txt.insertControlCharacter(tc,_
com.sun.star.text.ControlCharacter.PARAGRAPH_BREAK, True)
tc.goright 1,false


args3(0).Name = "Hyperlink.Text"
args3(0).Value = "bats"
args3(1).Name = "Hyperlink.URL"
args3(1).Value = "bats"
args3(2).Name = "Hyperlink.Target"
args3(2).Value = ""
args3(3).Name = "Hyperlink.Name"
args3(3).Value = ""
args3(4).Name = "Hyperlink.Type"
args3(4).Value = 1

dispatcher.executeDispatch(document, ".uno:SetHyperlink", "", 0, args3())


end sub
Windows 10, Openoffice 4.1.11, LibreOffice 7.4.0.3 (x64)
User avatar
Lupp
Volunteer
Posts: 3542
Joined: Sat May 31, 2014 7:05 pm
Location: München, Germany

Re: Multiple hyperlinks in one cell

Post by Lupp »

Would I be guilty of indecent curiosity if I ask for what you think to need two links in one cell?

The HYPERLINK() function is made for one link per cell and, if wanted for combining it with a displayed representation. This makes it extremely simple, to keep URLs and representation strings apart in a data sheet, and to combine them in one cell per URL in (mostly) a different sheet.

This way you can change the representation on the fly, or combine the unchanged representation with a new URL if one changed.

The only reason I can imagine to definitely want hyperlinks of the handiwork style is the export to different file types. If this is your concern, you should tell.
On Windows 10: LibreOffice 24.2 (new numbering) and older versions, PortableOpenOffice 4.1.7 and older, StarOffice 5.2
---
Lupp from München
BrianME
Posts: 18
Joined: Sun Jan 03, 2021 9:30 pm

Re: Multiple hyperlinks in one cell

Post by BrianME »

Lupp: I use 2 links in one cell, one for the agency's invoice and one for the client's invoice. It just keeps things a little more compact. I never change the hyperlinks. As the invoices get updated, I have a macro that renames the files so the links always point to the latest invoice as the latest always has the same name and the older files are renamed with revision letters so I can go back in history. Hope that helps.

JeJe: Thanks for the help again. I don't have time right now to test it out, but I'll give it a try later and let you know how it goes. I've been thinking about this problem and at first I thought it was timing, thus the wait 5 seconds. But the more I think about it I'm wondering if it's a "focus" problem. When the SetHyperlink gets executed from the spreadsheet window, something goes awry. But when it gets executed from the Basic code window it works. I don't know, just a thought.

I'll post after I get a chance to test out your suggestion.
OpenOffice 4.1.7 on Windows 10
BrianME
Posts: 18
Joined: Sun Jan 03, 2021 9:30 pm

Re: Multiple hyperlinks in one cell

Post by BrianME »

JeJe : I've been messing with this for awhile and I still can't get it to work right when I just run the macro. I've tried clearing the cell a few different ways, one of which is what you suggested, but that doesn't seem to matter. The cell gets cleared. I've sprinkled 1 second waits around so I can see what's going on when I'm running it and things progress past the clearing of the cell.

Right now if I just run the macro from the spreadsheet, it clears the cell and inserts the first hyperlink, but the hyperlink looks like it's "selected" and it gets deleted, then the second hyperlink is inserted. So when it ends, the second link is the only thing in the cell and it's on the first line so the Paragraph break isn't inserted. The link that's left looks right and opens the correct file when clicked on. Here's the part of the code that deals with the hyperlinks:

Code: Select all

oFrame		= ThisComponent.CurrentController.Frame
dispatcher	= createUnoService("com.sun.star.frame.DispatchHelper")

' Select and clear the invoice column cell of the inserted row
oCell = oSheet.getCellByPosition(InvoiceCol - 1, SelRow - 1)
ThisComponent.CurrentController.Select(oCell)
dispatcher.executeDispatch(oFrame, ".uno:ClearContents", "", 0, Array())
'txt= thiscomponent.currentcontroller.selection.text
'txt.string= ""
'Thiscomponent.CurrentSelection.ClearContents(1 OR 4 OR 16) ' Clears value, string, and formula
Wait 1000

' Now insert the 2 hyperlinks into the selected cell
'InsertTwoHyperlinks(FName, BookNum, FName3, "Client")



dim args3(4) as new com.sun.star.beans.PropertyValue
args3(0).Name	= "Hyperlink.Text"
args3(0).Value	= BookNum
args3(1).Name	= "Hyperlink.URL"
args3(1).Value	= FName
args3(2).Name	= "Hyperlink.Target"
args3(2).Value	= ""
args3(3).Name	= "Hyperlink.Name"
args3(3).Value	= ""
args3(4).Name	= "Hyperlink.Type"
args3(4).Value	= 1

'Thiscomponent.CurrentSelection.ClearContents(1 OR 4 OR 16) ' Clears value, string, and formula

dispatcher.executeDispatch(oFrame, ".uno:SetHyperlink", "", 0, args3())
Wait 1000

txt		= thiscomponent.currentcontroller.selection.text
tc		= txt.createtextcursorbyrange(txt.end)
tc.collapsetoend
Wait 1000

txt.insertControlCharacter(tc, com.sun.star.text.ControlCharacter.PARAGRAPH_BREAK, True)
tc.goright(1, false)
Wait 1000

args3(0).Name	= "Hyperlink.Text"
args3(0).Value	= "Client"
args3(1).Name	= "Hyperlink.URL"
args3(1).Value	= FName3
args3(2).Name	= "Hyperlink.Target"
args3(2).Value	= ""
args3(3).Name	= "Hyperlink.Name"
args3(3).Value	= ""
args3(4).Name	= "Hyperlink.Type"
args3(4).Value	= 1

dispatcher.executeDispatch(oFrame, ".uno:SetHyperlink", "", 0, args3())

oCell = oSheet.getCellByPosition(0, SelRow - 1)
ThisComponent.CurrentController.Select(oCell)
I'm done for the day!!!
OpenOffice 4.1.7 on Windows 10
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Multiple hyperlinks in one cell

Post by Villeroy »

Don't waste your precious time with stupid Basic code. Adding multiple hyperlinks to a cell manually is easy enough.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
User avatar
Lupp
Volunteer
Posts: 3542
Joined: Sat May 31, 2014 7:05 pm
Location: München, Germany

Re: Multiple hyperlinks in one cell

Post by Lupp »

I still don't understand what should be the advantage of having two links in one cell. The links are used as a kind of data or represent data - and are surely created from data.
Why not aplly the priciple "one item, one cell"?
All the "compounds" and similar tricks make sheets unhandy and aggravate maintenance.

If you can accept as a matter of sandards to make available the URL strings and their representation strings (may be the same) as pairs in two adjacent cells of a row in every case, and the two or more pairs you want to create links from as text content in a common cell in an adjacent range, you can push the couples of links to their respective target cells using the function I post below.

Since "pushing" by functions is basically not supported in spreadsheets, you need to accept in addition, that the target cell for such an operation is not allowed to be in the same sheet as the formula doing the trick.

I wrote and tested te code in LibreOffice. It also works in AOO 4.1.7, but it produces there additional text portions for pairs left empty in the source cells for reasons I did not unravel. You may fix the issue.

As already mentioned I wouldn't suggest to actually use the code, but to simplify your sheets. However, its always a kind of a strange challenge to get software do something it wasn't made for. That's behind my interest in the topic.
Anyway the code works flawlessly as expected for me.

Code: Select all

Function deliverLinksMadeFromRangeToCoordinates(pRangeData, pZ As Long, pY As Long, pX As Long, Optional pDefault, Optional pReleaseBreak As String, Optional pCompare As String)
If NOT IsMissing(pReleaseBreak) AND NOT IsMissing(pCompare) Then REM The break mechanism is "enabled".
  If NOT (pReleaseBreak=pCompare) Then
    REM The following rather compliucated consruct is needed as a workaround due to bug tdf#102381 as long as this bug isn't fixed.
    REM It is omitted concerning he other two optional parameters. 
    If NOT IsMissing(pDefault) Then 
      If NOT((pDefault=0)OR(pDefault="")) Then REM These "quaiempty" values are regarded equivalent to missing. 
        deliverLinksMadeFromRangeToCoordinates = pDefault
      End If
    End If
    Exit Function
  End If
End If
deliverLinksMadeFromRangeToCoordinates = ":empty:range:"
If hasNoContent(pRangeData) Then Exit Function
deliverLinksMadeFromRangeToCoordinates = ":need:pair:per:link:"
uR = Ubound(pRangeData, 1) : uC = Ubound(pRangeData, 2) : If (uC=0) OR ((uC MOD 2)<>0) Then Exit Function
pairsPerRow = uC \ 2
deliverLinksMadeFromRangeToCoordinates = ":target:info:bad:"
targetText = ThisComponent.Sheets(pZ-1).getCellByPosition(pX-1, pY-1)
tc = targetText.createTextCursorByRange(targetText.Start)
tc.gotoRange(targetText.End, True)
tc.String = ""
REM The Cell also supports the text services. The aspects can't be separated. Bad idea imo,
deliverLinksMadeFromRangeToCoordinates = ":unknown:error:"
For r = 1 To uR
  For n = 1 To pairsPerRow
    rn_pair = Array(pRangeData(r, n*2 - 1), pRangeData(r, n*2))
    If Len(rn_pair(0) & rn_pair(1))=0 Then Goto nextn REM Strange: Comparuson to "" failed.
    tfU = ThisComponent.createInstance("com.sun.star.text.textfield.URL")
    tfU.Representation = rn_pair(1)
    tfU.URL            = rn_pair(0)
    targetText.insertTextContent(tc, tfU, False)
    tc.gotoRange(tc.End, False)
    targetText.InsertString(tc, Chr(10), False)
  nextn:
  Next n
Next r
If targetText.String<>"" Then
  tc.goLeft(1, True)
  tc.String = ""
End If
targetText.Rows.OptimalHeight = True
deliverLinksMadeFromRangeToCoordinates = "done " & Format(Now(), "YYYY-MM-DD HH:MM:SS")
End Function
On Windows 10: LibreOffice 24.2 (new numbering) and older versions, PortableOpenOffice 4.1.7 and older, StarOffice 5.2
---
Lupp from München
BrianME
Posts: 18
Joined: Sun Jan 03, 2021 9:30 pm

Re: Multiple hyperlinks in one cell

Post by BrianME »

Well, if anyone cares, I got it to work. Not sure why, but after I clear the cell and insert the first hyperlink, I go to another cell to stop the editing of the cell I just put the hyperlink in. Then I go back to the hyperlink cell and using a piece of code from JeJe, I create a text cursor in the cell, insert the paragraph break, and insert the second link. Then again I go to another cell to stop the editing of the hyperlink cell.

I'm not sure why I need to stop editing the cell before inserting the hard return and the second hyperlink, but it works that way.

I would like to thank JeJe for their assistance in this matter!

I'll leave this open for a day or two in case anyone cares to ask questions, then I'll change it to solved.
OpenOffice 4.1.7 on Windows 10
JeJe
Volunteer
Posts: 2764
Joined: Wed Mar 09, 2016 2:40 pm

Re: Multiple hyperlinks in one cell

Post by JeJe »

Lupp's method is the proper way to do it (createInstance/insertTextContent) if you can get his code to work. The dispatch helper is useful for speed/if you don't know the API way.

The hyperlinks in calc cells are different from Writer hyperlinks... the documentation I read said they were a type of form control which isn't true... I see from Lupp's code they're actually TextFields.
Windows 10, Openoffice 4.1.11, LibreOffice 7.4.0.3 (x64)
User avatar
Lupp
Volunteer
Posts: 3542
Joined: Sat May 31, 2014 7:05 pm
Location: München, Germany

Re: Multiple hyperlinks in one cell

Post by Lupp »

Yes. In Writer a hyperlink is a text portion being subject to the general textflow. The portion constitutes one link by an URL assigned as a common character attribute. Strange, but works. In Calc hyperlinks are TextField objects which are passed over by a single step of a TextCursor each. The visible reprenentation is assigned as one property value to that field independent of its lenght. The representation of such a field isn't editable in the cell or in the FormulaBar. You need to use the dialog.

As I already told I don't understand the rationale behind the question. I was nonetheless interested in a solution like the one I posted, because it gives the option to keep apart data and presentation even in this rather subtle case. The principle of not maintaining data in cells/ranges which are dedicated to and optimized for presentation purposes can be regarded.

The general means to compute and to make presentable (and probably usable in a specific way) "result-areas" in sheets (also for printing e.g.) are formulas.

In a subtle case like the creation of text formatted in parts or containing links, there is no way to do it based on standard functions. Accepting the limitation that the intended output must go to a different sheet (and the ordinary result of the formula is rather casual, we can well use side-effects to circumvent restrictions: PUSHING functions are possible - as UDF.

(Yes. I'm tired of needing to apologize for many of my contributions. It's not my sole intention to help enhance efficiency concerning very special tasks. Fortunately this is not a Q&A site. Visitors to a forum may also be interested in discussions concerning variant handling of the concepts. )

If somebody wants to see the function actually working: I made an example. You can ask for it.
On Windows 10: LibreOffice 24.2 (new numbering) and older versions, PortableOpenOffice 4.1.7 and older, StarOffice 5.2
---
Lupp from München
BrianME
Posts: 18
Joined: Sun Jan 03, 2021 9:30 pm

Re: Multiple hyperlinks in one cell

Post by BrianME »

Since i was able to resolve this issue with the assistance of the forum volunteers, I'll mark this topic as solved.

Thanks to all.
OpenOffice 4.1.7 on Windows 10
Post Reply