Record a Macro records functions, not necessarily keypresses… I need to repeatedly edit a cell in Calc to delete the first 12 characters and move down to the next cell. In the old days I knew how to do that with functions like SendKeys () and could enter the ASCII values for F2, Home, DEL (x 12), CR. I’d assign my macro to a hotkey and be done, but I can’t find a similar function in the LibreOffice Macro IDE… been a while since I programmed anything. Can anybody point me in the right direction PLZ.
Other approach:
Search for regular expression .{12}(.*)
Replace with $1
You have to activate regular expressions in option checkboxes below search and replace lines.
.
Maybe I should add: if you mark only one column and use also the checkbox “only in selected area” you can work down the column.
1 Like
Im familiar with RegEx in Linux (I assume its similar) but what is the function call to load characters into the keyboard bufffer? I also need to do other functions with SendKeys() capability later.
I have not used keyboard buffers since TSR software with MS-Dos… ( But considering your original description, I doubt there would have been anything useful in your buffer after 12 DEL and a CR)
.
I can recommend “Macros explained” by Andrew Pitonyak, but I don’t remember SendKeys…
https://www.pitonyak.org/oo.php
Your approach won’t work.
You should try to be more precise about what’s meant by “repeatedly”. Is it just “frequently” or are you talking of a sequence of actions stopping under a specific condition?
A possible Sub for your hotkey:
Sub verySpecilaTrick()
On Local error Goto fail
cCtrl = ThisComponent.CurrentController
cCell = cCtrl.Selection
cString = cCell.String
If Len(cString)<12 Then Exit Sub
cCell.String = Right(cString, Len(cString) - 12)
cellCur = cCell.Spreadsheet.createCursorByRange(cCell)
cellCur.goToOffset(0, 1)
cCtrl.select(cellCur)
fail:
End Sub
The recorder can’t record actions inside the cells. Using the F&R as suggested by @wanderer when recording would create a sub with about 70 hardly understandable lines of Basic code.
Test this macro, it doesn’t remove the formatting in cell when some characters are formatted like bold, italic etc.
Sub cursorOperation
dim oDoc as object, oCur as object, oSel as object, document as object, dispatcher as object
oDoc=ThisComponent
document=oDoc.CurrentController.Frame
dispatcher=createUnoService("com.sun.star.frame.DispatchHelper")
dispatcher.executeDispatch(document, ".uno:Deselect", "", 0, array()) 'no multiselection
oSel=oDoc.CurrentCOntroller.Selection
if oSel.supportsService("com.sun.star.sheet.SheetCellRange") then 'selection is the cell
oCur=oSel.createTextCursor
if Len(oCur.String>=12) then
oCur.goToStart(false) 'text cursor to start of cell
oCur.goRight(12, true) 'select 12 characters
oCur.String="" 'delete characters
dim args1(1) as new com.sun.star.beans.PropertyValue
args1(0).Name="By" : args1(0).Value=1
args1(1).Name="Sel" : args1(1).Value=false
dispatcher.executeDispatch(document, ".uno:GoDown", "", 0, args1()) 'cell cursor to downer cell
end if
end if
End Sub
BTW (for connoisseurs):
Both solutions given above suffer from an old annoying inconsistency of the Calc API (related to an incompatibility with Writer):
In Calc hyperlinks are implemented as instances of com.sun.star.text.textfield.URL and counted as one step in TextCursor movement (by the API method and by the UI as well), but the complete .Representation (anchor string) with all its characters is added to the .String property of the TextCursor when such a step occurs.
This way the string getting replaced with the empty string by the above codes will have more than 12 characters if containing a URL representation of more than 1 character.
1 Like