Ask Your Question
0

How to write macro to get hyperlink work [closed]

asked 2019-12-01 08:23:22 +0100

bonny gravatar image

Hello...

I have trouble making hyperlink within macro. For example just create empty spreadsheet and create another sheet within so we have 2 empty sheets. In Sheet1 A:1 just paste

=HYPERLINK("#Sheet2")

For A:3 create macro and execute it

Sub TestMacro
    dim document as object
    oSheet = thiscomponent.sheets.getByName("Sheet1")
    oCell = oSheet.getCellRangeByName("$A$3")
    xx="=HYPERLINK(""#Sheet2"")"
    oCell.setString(xx)
End Sub

So sheet1 now have something on cell A1 and A3. the both should be link but just A1 works. If jou press CTRL+click on Sheet1 A1 the Sheet2 is selected. If you press CTRL+click on Sheet1 A3 nothing happens

I tested ou xubuntu 18.04LTS LibreOffice Version: 6.0.7.3 Build ID: 1:6.0.7-0ubuntu0.18.04.10

and same after upgrading to LibreOffice Version: 6.3.3.2 Build ID: 1:6.3.3-0ubuntu0.18.04.1

edit retag flag offensive reopen merge delete

Closed for the following reason the question is answered, right answer was accepted by bonny
close date 2019-12-02 08:59:10.974090

Comments

2

Replace oCell.setString(xx) with oCell.setFormula(xx)

Mike Kaganski gravatar imageMike Kaganski ( 2019-12-01 08:51:22 +0100 )edit

1 Answer

Sort by » oldest newest most voted
2

answered 2019-12-01 15:37:21 +0100

Lupp gravatar image

Unfortunately @Mike Kaganski posted his answer as a comment.
Without claiming a preference I would like to state that recent versions of LibreOffice come with an API where SheetCell objects have a property .Hyperlink which makes the usage of the .Formula property dispensable.

Sub test
a1 = ThisComponent.Sheets(0).getCellByPosition(0,0)
a1.String    = "Jump to Sheet2.B12:E19"
a1.hyperlink = "#Sheet2.b12:e19"
a2 = ThisComponent.Sheets(0).getCellByPosition(0,1)
a2.Formula   = "=HYPERLINK(""#Sheet1.h6:j10"";""Jump to Sheet1.H6:J10"")"
End Sub

There is, however, a bug concerning the sensitive area of the respective cell.

edit flag offensive delete link more

Question Tools

1 follower

Stats

Asked: 2019-12-01 08:23:22 +0100

Seen: 27 times

Last updated: Dec 01