Simple macro to open URL in sCalc

[Orwel]

Hi folks,
I am not very familiar with writing macros, so I would appreciate your help.

I have tried some solutions I found on web, but nothing seems to work for me. I try also ask AI for help, by by using macros providing by AI I always get some Error by running the macro. So I try here:

1. I have a simple sCalc sheet called “QR”
2. In cell A24 I generate a specific URL address by formula =HYPERLINK(A22)
3. In cell A22 I generated the text of URL by formula =CONCATENATE(“https://”;A1;A2). I can change the values of A1 and A2.
4. I created a push button called “QR”.
5. I want sCalc to open the URL of A24 by pressing the button QR.
   I know I can open the URL directly by CTRL+Click on A24 (and it works), but I want to open it by clicking on the button.
   Is there some simple macro which enables it and which I could assign to the mouse click on the button?
   I am now at LO 7.6.5
   Many thanks.

[JohnSUN]

…or if you really need a macro, may be so:

Sub openLink(Optional oEvent As Variant)
	createUnoService("com.sun.star.system.SystemShellExecute").execute( _
		ConvertToURL(ThisComponent.getSheets().getByName("QR").getCellRangeByName("A22").getString()),,0)
End Sub

[Orwel]

Thank you very much for your quick reply, it works, I appreciate your help.

[Orwel]

Hi JohnSUN, after last upgrade of LO (from 24.2 to 24.8) and Java, after running the macro, which you have provided and which worked very well for months, I face now an error message which opens also the LO Basic Object Catalog. The error point to the line
createUnoService(“com.sun.star.system.SystemShellExecute”).execute( _
and says: “BASIC runtime error. Argument is not optional.”
But the web page is opened anyway, so the macro still works, indeed. The only problem is, that the LO Basic object catalog opens with this error message, which is a bit annoying. I would really appreciate your further help, if you have any idea what has happened, as I am not familiar with writing macros

UPDATE: it seems it is some problem with the new version of LO, as also other sCalc macros make problems…

[JohnSUN]

The underscore at the end of this line is a sign that the rest of the statement is on the next line. It is generally assumed that this is the very last character, and that there is nothing after it on this line. I used this syntax so that the code would be visible on the screen, but it is not necessary. Try to trick BASIC and write the entire statement on one long line.

Sub openLink(Optional oEvent As Variant)
createUnoService("com.sun.star.system.SystemShellExecute").execute(ConvertToURL(ThisComponent.getSheets().getByName("QR").getCellRangeByName("A22").getString()),,0)
End Sub

[Orwel]

Thank you for the effort, but this gives an error: BASIC runtime error. An exception occurred
Type: com.sun.star.lang.IllegalArgumentException
Message: Empty command at C:/cygwin64/home/buildslave/source/libo-core/shell/source/win32/SysShExec.cxx:215.

But before you post your answer, I have got (only to let you know) an answer from LO-bug community:

This is not a bug - the execute method of XSystemShellExecute[1] has no optional arguments, and using it as you did (putting no arguments between commas) was only working by accident before the fix for bug 162431, because the error code 448 for missing argument was passed as an integer there instead, which is definitely wrong. [1] LibreOffice: XSystemShellExecute Interface Reference

With the help of AI I have got a working solution by using the script:

Sub QRweb
    Dim url As String
    url = ConvertToURL(ThisComponent.getSheets().getByName("QR").getCellRangeByName("A14").getString())
    createUnoService("com.sun.star.system.SystemShellExecute").execute(url, "", 0)
End Sub

Many thanks for your effort.

[erAck: edited to format code as code, see This is the guide - How to use the Ask site? - #6 by erAck]

[mikekaganski]

tdf#163683

[karolus]

The simplest solution would be to imagine that the cell with the hyperlink is a button!