I have around 3000 cells with a string comprising words in each. I am trying to create a macro which converts the first word into a hyperlink (which still works after exporting to pdf), and leaves the rest of the text as is, but am struggling to get it to work. Note that the built in hyperlink formula does not appear as a clickable link after converting to pdf, but dispatcher.executeDispatch(document, “.uno:SetHyperlink”, “”, 0, args2()) does.
Any help would be greatly appreciated.
My macro so far is listed below. It finds the first word and converts it to a hyperlink. I then want to append the rest of the text as a string, but this just overwrites the hyperlink. I can do what I want manually, but not with this macro.
sub HyperLink
rem ----------------------------------------------------------------------
rem define variables
dim document as object
dim dispatcher as object
dim linktext as String
dim appendedtext as String
dim pos
dim contents as String
rem ----------------------------------------------------------------------
rem get access to the document
document = ThisComponent.CurrentController.Frame
dispatcher = createUnoService("com.sun.star.frame.DispatchHelper")
ThisCell = ThisComponent.CurrentSelection
rem ----------------------------------------------------------------------
rem get data from selected cell
contents = ThisCell.String
pos = Instr(contents," ") 'get position of space character
rem ----------------------------------------------------------------------
ThisCell.clearContents(4)
linktext = Mid(contents, 1, pos-1)
appendedtext = Mid(contents, pos)
dim args1(0) as new com.sun.star.beans.PropertyValue
args1(0).Name = "ToPoint"
args1(0).Value = ThisComponent.CurrentSelection.AbsoluteName
dispatcher.executeDispatch(document, ".uno:GoToCell", "", 0, args1())
rem ----------------------------------------------------------------------
dim args2(5) as new com.sun.star.beans.PropertyValue
args2(0).Name = "Hyperlink.Text"
args2(0).Value = linktext '"link"
args2(1).Name = "Hyperlink.URL"
args2(1).Value = "https://www.someurl.php?link=" & linktext
args2(2).Name = "Hyperlink.Target"
args2(2).Value = ""
args2(3).Name = "Hyperlink.Name"
args2(3).Value = ""
args2(4).Name = "Hyperlink.Type"
args2(4).Value = 1
args2(5).Name = "Hyperlink.ReplacementText"
args2(5).Value = ""
dispatcher.executeDispatch(document, ".uno:SetHyperlink", "", 0, args2())
'Don't know how to now append text without deleting the hyperlink
end sub
