Sub ExtractHL()
Dim HL As Object
Dim Sel As Object
Sel = ThisComponent.CurrentController.ActiveSheet
For Each HL In Sel.getCellRangeByName(“A1:Z1000”).getHyperlinks()
HL.Range.Offset(0, 1).String = HL.HyperlinkURL
Next
End Sub
Kindly notify error in the above code. I received this code from chat gpt as translation of Microsoft code as below
Sub ExtractHL()
Dim HL As Hyperlink
For Each HL In ActiveSheet.Hyperlinks
HL.Range.Offset(0, 1).Value = HL.Address
Next
End Sub
BASIC runtime error.
Property or method not found: getHyperlinks.
This is the error when i run the code in Libra calc
Because it is a MS VBA code. You must rewrite the code based on the API function of the Libreoffice. You can call them from a StarBasic macro.
.
The LO can run some of the MS VBA codes, but all of them. You must use the Compatibility options. Try it.
.
Is ther any solution? The question is marked as “resolved”. (Or maybe I have clicked accidentally somewhere?)
thank for reply ZiZi. I received the translated code of the original MS Excel code from ChatGpt. It’s not my work as i don’t know VBA. However i was interested in translating the code to the one that can be used in Libra Calc for converting image links downloaded from dropbox in to absolute hyperlinks.
If you have any suggestions, it will be helpful
P.S. I tried recoding macros for the activity of converting relative to absolute hyperlinks and was very close to it. If you are interested i can share you with that macros. However it needs modification to work for all links and not just one link. Somehow i cannot create a look to convert all the relative links to absolute ones. Thanks
There is not any way to “translate” the VBA codes directly or automatically. You must rewrite them for the LO.
( The name of the free and open source office suite is LibreOffice, but not Libra…)
Can you upload an ODF type sample file here with those linked (or embedded?) images?
The Macro Recorder has a very limited capability. You need to WRITE your macros based on the API functions of the LO and by usage one of the supported programming languages. One of them is the StarBasic. It is easy-usable, because it has a built-in IDE. But the StarBasic is not the most efficient programming language today.
.
API: Application Programming Interface - It is a language-independent (read: programming language) “library” for control all of the features of the LibreOffice.
.
IDE: Integrated Develeopment Environment. (In this case: the Code editor and a Debugger and an Interpreter for the StarBasic language.)
Here is a sample file with some graphical objects inside (one button, and two images on the graphical layer of the Sheet1), and a sample macro code: how to get the URL of a linked image by API functions
ListObjects.ods (49.3 KB)
Thanks.
Here;s a sample Libre Office calc file of what i am looking for. The main sheet has the image titles which are linked to dropbox urls, when clicked upon they open in a web browser. The second sheet is how the file should look like after running the macros. i.e. besides the image title column, the url should appear automatically. The MS excel macros i posted in my second message works for the entire sheet. However the same macros shows error when i run it on LO.
Sample images.ods (10.8 KB)
file.
Your sample have not contain linked images but Hyperlinks only. Here is a sample file what show you how to mine the URL from a hyperlink located in a cell. Note: a Cell can contains more than one hyperlinks. The sample code not examine more than one hyperlink in the referenced Cell.
The LibreOffice can not pass the Cell OBJECT to a custom defined function by usage the default reference method, (but it pass the cell content (value, string) only, therefore my code gets a Cell object by usage the Sheet number, the Column number and the Row number. The Cell object contains Textfield, that contains the Hyperlink or Hyperlinks:
.
Sample_images_Zizi64.ods (11.7 KB)
Thanks. I meant image links linked to urls. Will try and give u a feedback soon.
Many Thx. U came very close to what i was looking for. Although there are some bugs, however i can do with them as i get my solution if i add more rows to the data.
One last Q, can it work on other columns too? or just the 1st column… I mean will it work for the entire sheet and not just the first column?
Many Thx. U came very close to what i was looking for. Although there are some bugs, however i can do with them as i get my solution
if i add more rows to the data.Typing error ignore.
One last Q, can it work on other columns too? or just the 1st column… I mean will it work for the entire sheet and not just the first column?
My macro function works with many data in many rows and in many columns. Just pass the proper parameters of the actual source cell (what contains a hyperlink) to the function: the sheet number, the colums number (in my sample it is the adjacent column) and the row number (same as the target row).
.
Please upload your new sample file here.
…Or you can write the macro as a Subroutine with some For…Next (or other) cycle instead of the “one-cell-scope”. Then it will not work a user defined Function (with automatic refreshing) but you need launch it, when you need it. And the results will be constant texts.
Only you know the “rules”: the structure of your original data, where the source data are located.
You can examine even the whole sheet: if the cells contain hyperlinks or not, but the Basic macro will work very slow in this case.
Sample_images_Zizi64.ods (14.5 KB)
I am attaching the sample file as well as image of error bug that comes up as and when i open the file.
You have tried to mine out an URL from an empty cell.
To eliminate these error messages you need improve the code. Make it “foolproof” by usage of some conditional test.
Try some similar:
REM ***** BASIC *****
option explicit
Function MyMineURL(lSheetNr as long, lColNr as long, lRowNr as long) as string
Dim oDoc as object
Dim oSheet as object
Dim oCell as object
Dim oTextFields as object
Dim sURL as string
oDoc = ThisComponent
oSheet = oDoc.Sheets.getbyindex(lSheetNr-1)
oCell = oSheet.getCellByPosition(lColNr-1, lRowNr-1)
'xray oCell
sURL = "None"
if oCell.Type = 2 then
oTextFields = oCell.Textfields
'xray oTextFields
if oTextFields.Count <> 0 then
sURL = oTextFields.getByindex(0).URL
end if
end if
MyMineURL = sURL
end function
Note: my code still only extracts the URL of the first (0th) hyperlink from the referenced cell.