I do not understand your reason for posting a response in Portuguese to my query in English. While they may very well be good documents I have no way to determine that since I less fluent in Portuguese than I am in OOP.
The topic that @schiavinatto mentions has a number of links to English documentation about LO macros and Basic. You may or may not find them useful.
My main objective in seeking this information is to discern how exactly some code in a macro that I found on the internet does what it does, with the intent to modify it to do something similar that I want to accomplish.
The macro steps through a range of cells which should contain URLs and converts them into hyperlinks removing the text content of the cells in the process.
This is the code for the macro that converts the original content of the cell into a hyperlink:
Sub ActivateUrlsAsLinks Const boxtitle = "URL activator" Dim msg As String Dim oSels As Object, cell As Object oSels = ThisComponent.getCurrentController().getSelection() cell = oSels.getCellByPosition(0, 0) If oSels.Columns.getCount <> 1 Or cell.getString() = "" Then msg = "Select cell range with URLs to be converted to clickable links, " & _ "then run this macro." & _ Chr(10) & Chr(10) & "NB: 1 column, and a URL in first row." MsgBox msg, MB_ICONSTOP, boxtitle Else Dim txtfld As Object, celltxt As Object Dim rix As Integer, subs As Integer, cstr as String subs = 0 For rix = 0 To oSels.Rows.getCount - 1 cell = oSels.getCellByPosition(0, rix) cstr = cell.getString() If Len(Trim(cstr)) > 0 And Not IsNumeric(cstr) And Not IsDate(cstr) Then txtfld = ThisComponent.createInstance("com.sun.star.text.TextField.URL") txtfld.Representation = cstr txtfld.URL = ConvertToURL(cstr) cell.setString("") celltxt = cell.getText() celltxt.insertTextContent(celltxt.createTextCursor(), txtfld, False) subs = subs + 1 End If Next rix msg = "Replaced " & subs & " cells" MsgBox msg, MB_ICONINFORMATION, boxtitle End If End Sub
What I want to do is step through a range of cells which contain some text that when appended to URL prefix can be used to create a hyperlink with the original text that was in the cell becoming the visible text or button text (to use the language in the tool tip of the Hyperlink dialogue box) of the hyperlink.
I feel like I understand what most of the code in the macro does except for the following line of code:
celltxt.insertTextContent(celltxt.createTextCursor(), txtfld, False)
Specifically what I want to know is what does this line of code do? What are the arguments to insertTextContent? What does the statement
return? And what does the argument False cause to happen?
And finally, in general, what do I need to change to cause the original text in the cell to be used as visible text of the hyperlink?
As far as I remember, the code you quote was preceded by the following explanation: “Credits go to Andrew Pitonyak from whose OOo Macro document the essentials were snipped” If we look in Andrew Pitonyak’s book, we can easily find the chapter 5.18.5. Insert a URL into a Calc cell:
For reasons that defy me, the following functionality has been requested numerous times. I opted to add this example because I am tired of figuring out how to do it every time. The InsertURLIntoCell macro converts the text of a cell into a URL and then inserts a URL text field into the cell. Read the comments to see how this is done. And Listing 5.53: Insert a URL into a Calc cell. indeed contains more comments than executable code.
You say you looked at Pitonyak’s site?
BTW links to this resource are also in the book, at the very beginning:
There is also information about “cursors”
If you do not now set the string to zero, then the existing text will remain and the new URL text field will be appended to the end
Another option would be to pass the whole cell range to the
insertTextContent method, and pass
true as the “absorb” last parameter.
@BinghamJC1 , Do you know this?
You can travel the world…
What we may find anywhere always also depends on the way we are looking around.
In fact the famous texts by Andrew Pitonyak to be found here (especially “the Book”) are the only ones systematically treating the OOo API (LibO still only enriched and updated somehow) in the specific way needed if you are decisive to use the included Basic as the bridge to it. The “Macro Document” (Useful Macro Information) then exemplifies a lot in a less systematic way.
As far as the API reference is concerned, however, wishes certainly remain open.
A developer, or an interested person who is already familiar with large projects in C++, can certainly find everything he (f/m) is looking for in the source code. An informed user, who only has experiences with small projects, and hardly any C++ knowledge, will have a hard time.
I remember in principle forum questions from well-informed users, who not only want to be informed about the properties of already found “objects” (services, interfaces, structures …), but also would like support in the search for suitable objects for (roughly) clear purposes, without having to call into a forum (or Q&A system) for help.
And what we get in the API reference if there is a link showing “More…” often seems to be of little value.
There is no link from
"purposes given by a few everyday words" to
"probably applicable means in the technical sense".
Yes. Anybody might start to create the “missing link” as a project.
No. I can’t do it due to age and lack in knowledge.
Andrew has to provide a surrogate under the “Basic” keyword even for those preferring a different programming language.
@schiavinatto That door swings both ways, you also could have used google translate to provide your answer.
Not this time: He only provided the link. The integration of the (foreign) text is “courtesy of discourse” wich runs this ask-site.
It was just a link to an answer. It was in a different language, so what?
Most people answering questions here don’t have English as their first language but they help and they reply in English. Why should they translate a page elsewhere for you, that you might not even bother looking at?
@BinghamJC1, I just put the page link, the text, it came automatically from the page.
Thank you all, Using the information that you provided I was able to create a macro to do create Hyperlinks in a range of cells.
Here is the macro:
Sub CreateHyperlinksWithUserInput() 'Create Hyperlinks in the cells of a one column range of cells by prepending the start of a 'URL to the text in the cell, with the existing text of the cell becoming the representation 'of the Hyperlink. 'Thanks to ChatGPT for providing much of the superstructure of the macro (even though 'the method it suggested to create the Hyperlink was incorrect). 'To urznow of https://superuser.com/ for a macro that almost did what I wanted, parts of 'which were attributed to Andrew Pitonyak. (See 'https://superuser.com/questions/1674498/how-to-edit-a-column-in-libre-office-calc-to-set-all-urls-as-clickable-links) 'To mikekaganski, JohnSUN & Lupp of ask.libreoffice.org for information pointing me to 'Listing 5.53: Insert a URL into a Calc cell in chapter 5.18.5 of Andrew Pitonyak’s book 'Useful Macro Information For OpenOffice.org (https://pitonyak.org/AndrewMacro.odt) 'where I was able to acquire the information needed that allowed me to create a 'Hyperlink in a LibreOffice Calc cell. Const cBoxtitle = "Hyperlink Creator" Dim sMsg As String Dim oRange As Object Dim oCell As Object 'Get the selected range oRange = ThisComponent.getCurrentController().getSelection() 'Get the first cell in the selected range oCell = oRange.getCellByPosition(0, 0) 'Verify that the range is one column wide and the first cell is not empty If oRange.Columns.getCount <> 1 Or oCell.getString() = "" Then sMsg = "Select cell range to create the links in, " & _ "then run this macro." & _ Chr(10) & Chr(10) & "NB: Requires 1 column and text in first row." MsgBox sMsg, MB_ICONSTOP, cBoxtitle Else Dim sURLPrefix As String 'Query the user for the URL prefix sURLPrefix = InputBox("Enter the URL prefix:", "URL Prefix Input") 'See if the user canceled the input and exit if so If sURLPrefix = "" Then MsgBox "Operation canceled." Exit Sub End If Dim oTxtFld As Object Dim oCellTxt As Object Dim iNdx As Integer Dim iCnvCnt As Integer Dim sCellCont as String Dim sFullURL As String iCnvCnt = 0 'Step through each cell in the range For iNdx = 0 To oRange.Rows.getCount - 1 'Get the cell to convert from the range (Column, row) oCell = oRange.getCellByPosition(0, iNdx) 'Get the text from the cell sCellCont = oCell.getString() 'If the trimmed cell content is greater than zero and ' the content is not a number and is not a date If Len(Trim(sCellCont)) > 0 And Not IsNumeric(sCellCont) And Not IsDate(sCellCont) Then 'Create a URL Text field oTxtFld = ThisComponent.createInstance("com.sun.star.text.TextField.URL") 'Put the cell content into the TextField Representation oTxtFld.Representation = sCellCont 'Create the full URL text by appending the cell content to the prefix sFullURL = sURLPrefix & sCellCont 'Create the URL oTxtFld.URL = ConvertToURL(sFullURL) 'Clear the current string from the cell so that it will not be appended to the 'Representation that was set above. oCell.setString("") oCellTxt = oCell.getText() oCellTxt.insertTextContent(oCellTxt.createTextCursor(), oTxtFld, False) 'count the conversion iCnvCnt = iCnvCnt + 1 End If Next iNdx sMsg = iCnvCnt & " cell" & IIf( iCnvCnt = 1, "", "s") & " converted." MsgBox sMsg, MB_ICONINFORMATION, cBoxtitle End If End Sub
Again, thanks for all your helpful insights and information.
I would like to publish this macro in a place where other users of LibreOffice would be able to find it and use it. I don’t think that my original question will match on a search in this forum for a macro that will do what it does. Do you have any suggestions?
I usually add the text of the question in the Title to the File - Properties - Description tab, several more or less unique words in the Keywords field and a link to the discussion in the Comments field and save the file in the Archive folder… There it lies for some time - sometimes several weeks, sometimes for several years - until a question on the same topic is asked again. Then I take the file out of the archive and publish the code as an answer to the question. Yes, not all of these files have already been published - perhaps the time has not yet come for them… or the answer to that question no longer interests anyone…
In general, “do good and throw it into the water” (Bulgarian proverb)
There are sections for Macros and Code-Snippets at Apache OpenOffice Community Forum - Apache OpenOffice Community Forum
@ JohnSUN Thanks for the suggestion. It sounds like you visit/monitor this forum frequently if not constantly, unfortunately being a novice at writing OO Basic macros, I do not, so that is not what I had in mind as a way to make the little I have learned in creating this macro available to others.
@Wanderer Your suggestion sounds more like what I had in mind. I took a look at the link you provided. Now I just need to figure out where in that location would be a good place to post it. So far I looked to see if anyone else had posted something similar and have not found anything. I have not seen any place where macros were posted that were not a response to a question either. Maybe I just don’t know what to look for.
See Apache OpenOffice Community Forum - Code Snippets - (View forum) From that page:
For sharing working examples of macros / scripts. These can be in any script language supported by OpenOffice.org [Basic, Python, Netbean] or as source code files in Java or C# even - but requires the actual source code listing. This section is not for asking questions about writing your own macros.
@robleyd This looks like it just might be the place that I am looking for. Thank you for saving me from a lot of flailing about.
@Wanderer had already suggested the same. Perhaps you overlooked the words.