See my example. In reality I have 10000 bookmarks to extract.
Example for question about extracting URLs.ods
My goal is to leave column A as it is and have the URLs in column B.
Not so imported anymore as I found steps to get the desired result. But still interesting. Maybe others need it or I need it later and it is more easily applied than the solution I found with a lot of help from others. I will test your example file if it does what I wished when you will upload it for these reasons.
Hello @inJesus, to extract the URLs from the hyperlinks in your Column A to Column B, you could use a macro such as:
Sub Extract_URLs_From_Hyperlinks( iSheetIndex as Integer, iColumnHyperlinks As Integer, iColumnURLs As Integer )
REM <iSheetIndex>: Sheet index ( 0 = Sheet1 ).
REM <iColumnHyperlinks>: Column with the Hyperlinks ( 0 = A ).
REM <iColumnURLs>: Column where the URLs from the Hyperlinks should be extracted to ( 1 = B ).
On Local Error GoTo Exit_Sub
Dim oSheet : oSheet = ThisComponent.Sheets.getByIndex( iSheetIndex )
Dim oCell, oTextFields
Dim strURL as String
Dim i As Integer
REM Traverse Rows:
For i = 0 to 99999 REM 99999 = set here your maximum number of Rows to process...
oCell = oSheet.getCellByPosition( iColumnHyperlinks, i )
oTextFields = oCell.getTextFields()
If Not isNull( oTextFields ) And oTextFields.getCount() > 0 Then
strURL = oTextFields.getByIndex( 0 ).URL
oSheet.getCellByPosition( iColumnURLs, i ).setString( strURL )
End If
Next i
Exit_Sub:
End Sub
EDIT:
working example attached
( This is your own uploaded file that i made working )
14945420052665621b.ods
Thank you.
Before using it; Did you try it yourself on the sample file or another example?
Yes, i tried this myself using your sample file … so it’s tested for 2 rows …
Would you send me an example file where this is working?
Yes, i will upload a working example in my original answer.
Works great. Thank you.
To achieve goals like the one under discussion here based on a Sub comes with some disadvantages:
1 - The Sub will need to know the cell range (including the sheet) on which to apply the search.
Suggested “macros” tend to simply not deal with this problem and to apply whatever they do to a constant range.
2 - The Sub will need to know an output range.
Programmed observing well proven standards it should send an alert if a non-blank cell gets concerned.
3 - The code is highly specialised -and will probably only run once.
4 - Doing it this way is against the “spreadsheet paradigm” which is to calculate results from input to formulae.
In this special case I suppose to know that the Original Questioner is also interested in closely related tasks. It should be desirable to know all these related tasks to be able to suggest a solution achieving them in one run, or to provide means capable of helping with more than one single aspect only.
In this case the OQ posted (at least) two questions (#94793, # 94882) related in this sense, but not explicitly telling it. He also stepped in to a very old thread (#50141) seeing it related to his problem, which it not is.
I made a new demo, not only to suggest a possible solution, but also to show in what way the knowledge of the relation between question can help to find adequate solutions - based on formulae (which is the Spredsheet-Do) and on built in tools of LibO.
As opposed to a Sub a Function can get the needed info via parameters in a perfect way when called from a spreadsheet cell.
The mentioned demo is attached here.
BTW: In the code line If Not isNull( oTextFields ) And oTextFields.getCount() > 0 Then a pair of parentehses is missing:
aIf Not isNull( oTextFields ) And (oTextFields.getCount() > 0) Then
This example works here. Good to know. Thank you Lupp.
BTW, my whole project is succesfully finished now. I have split up topics as the answers to a multistep solution in one thread would have overwhelmed me (even more). And it get beome confusing. But a workaround would be to receive suggestions in the complex question and then have several tasks needed explained in other threads. What do you think?
Very interesting stuff with the Links pulldown menu, showing a Listbox and Buttons etc. I didn’t know that was possible in Calc… However in this way the user has to perform a lot of actions just to fill 1 column with URLs. As mentioned above, this task is highly specialised for the user’s case and will probably only run once. This would be an argument for using an ad-hoc macro to get the job done, instead of altering the sheet’s infrastructure.
BTW @Lupp, why are those parentheses necessary?
It was easy to do for me who is quite a beginner what I asked for in this question. I did not understand the answer or at least not all parts and also I am not interested in theory so much.
But I tested the demo file, at least the part of it I needed, and saw it works.
I copied the macro in my real project file.
And copied the formula, referenced it to the right cells.
Then copied it down with copy and paste.
So I have got the URLs. Pasting with Ctrl Shift V into the next column.
Quoting@librebel: “…why are those parentheses necessary?”
They aren’t. Sorry! Due to my obsession with being explicit i got into the habit of using parentheses around comparisons - and finally I forgot about how the precedence of operators is actually specified. (The default may not be reliably universal, however.)
A funny addendum: For a short time I used the undocumented alternative in LibO and its predecessors.to apply AND and/or OR in Calc as infix operators. This is not ruled by an order of precedence, however, It requires to put the operands in parentheses even if simple references or constants, not to talk of comparisons. Shall I talk of “infix functions”?
talking about explicit … i started omitting the “Option Explicit” statement from my BASIC code around the same time that i was diving into Python … yup, learning Python causes a “loosening” of coding habits in other languages …
So many languages I’m not just loose any more, I’m irregular.