Sorry, I might not have explained my problem properly, but I was looking for a way to highlight occurrences of some text directly in Calc. The use case was going to be reviewing numerous .xlsx documents on ad-hoc basis. Thank you for showing me that this can be done in another way, but I think that transferring data between programs will require too many steps for this particular use case.
A spreadsheet is definitively the wrong application to store 100% textual data. It does not provide the means to handle text other than labels and short descriptions. Sooner or later, when the amount of data has reached a certain level, you will lose all spreadsheet data, assuming that you do not have any backup strategy.
100% agree, but many commercial projects keep misusing them for all kinds of purposes (timesheets, project trackers, timelines) and there is not much I could do to stop it. Some way to search through them flexibly would still be very helpful.
Apologies for the late reply, my account was blocked for 17hours as it was recently created.
I was engaged with different rails
(Apache OpenOffice Community Forum - [Solved] “This Cell” formula or function? - (View topic) e.g.) for
some time.
Concerning yopur issue I did a bit of experimenting (my way), but didn’t get yet a
satisfying solution. Its partly a problem of handling “entangled code” for me. If
I find more time I may suggest a solution moving text with respective highlighting
to annotations (comments) to be added to the respective cells.
For now there is banking, probably taxes, car washing …
Ordinary life, you see.
1 Like
simplified relative addressing would be useful. Thanks for sharing!
That sounds like a good idea, the least “invasive”.
I was trying in the meantime (as you saw in the other thread) to find a way to just change color font of a sought word in the cell. You could then undo it with Ctrl-Z, so it would be a temporary hack.
No worries, while I hope that this idea won’t be forgotten, it’s definitely nothing urgent neither.
As a temporary hack I wrote a Basic script that underlines each occurrence of a string in selected cells (prompts you with an InputBox).
Sadly it doesn’t work when selected cells are not adjacent (like with find tool) - I’ll try to find out why:
Also it seem to treat each run of the tc.CharUnderline = 2 assignments as a separate undo item (meaning if there are 4 occurrences highlighted you need to undo 4 times for each one separately), which may be okay, but it would be nice to have an option to “batch” them all into a single “undo action”.
REM ***** BASIC *****
Sub findAndMark
Dim target As String
target = InputBox ("Search phrase")
Set oRange = ThisComponent.CurrentSelection
For i = 0 To oRange.Rows.getCount() - 1
For j = 0 To oRange.Columns.getCount() - 1
Set cell = oRange.getCellByPosition( j, i )
REM https://stackoverflow.com/questions/35239915/find-all-strings-within-a-string
start = 1
Do
pos = InStr(start, cell.String, target)
If pos > 0 Then
start = pos + 1
tc = cell.CreateTextCursorByRange(cell.Start)
tc.goRight(pos - 1, False)
tc.goRight(Len(target), True)
tc.CharUnderline = 2
End If
Loop While pos > 0
Next
Next
End Sub
Use the object inspection tool to examine the selected range (ranges). Maybe you need one more loop to handle the non adjacent ranges (the Range array). Use the GetByIndex method to get each of the selected ranges. Maybe you need determine the type of the selection before (if it is a Range or Range array)
1 Like
REM ***** BASIC *****
option explicit
Sub findAndUnderline
Dim oCurrSel as object
Dim oRange as object
Dim sTarget As String
Dim selIndex as integer
sTarget = InputBox ("Search phrase")
oCurrSel = ThisComponent.CurrentSelection
'xray oCurrSel
if oCurrSel.ImplementationName = "ScCellRangesObj" then
Print "Some Ranges are selected"
For selIndex = 0 to oCurrSel.getCount() - 1
oRange = oCurrSel.getByIndex(selIndex)
MarkStrings(oRange, sTarget)
Next
end if
if oCurrSel.ImplementationName = "ScCellRangeObj" then
Print "One Range is selected"
oRange = oCurrSel
MarkStrings(oRange, sTarget)
end if
if oCurrSel.ImplementationName = "ScCellObj" then
Print "One Cell is selected"
oRange = oCurrSel
MarkStrings(oRange, sTarget)
end if
End Sub
Sub MarkStrings(oOneRange as object, sText as string)
Dim oCell as object
dim tc as object
Dim i, j, start, pos as integer
For i = 0 To oOneRange.Rows.getCount() - 1
For j = 0 To oOneRange.Columns.getCount() - 1
oCell = oOneRange.getCellByPosition( j, i )
REM https://stackoverflow.com/questions/35239915/find-all-strings-within-a-string
start = 1
Do
pos = InStr(start, oCell.String, sText)
If pos > 0 Then
start = pos + 1
tc = oCell.CreateTextCursorByRange(oCell.Start)
tc.goRight(pos - 1, False)
tc.goRight(Len(sText), True)
tc.CharUnderline = 2
End If
Loop While pos > 0
Next
Next
End Sub
1 Like
That is fantastic, thank you very much for your suggestions and implementation! It works much better now. Also it shows many good conventions and techniques for writing Basic that I need to start using as well. 'xray also seems interesting.
I was wondering if you might also know if there is some “trick” in Calc’s basic to batch together TextCursors operations so undoing them would act on all of them after the script runs?
If you are using the Cell Styles (read: ONLY the Cell Styles) for the formatting of the spreadsheet, then you can use the Ctrl-A + Ctrl-M key combinations to delete all of the direct (manual) formatting properties. These underscores (applied by the macro) are direct formatting properties.
You can use the MRI too. Install one of them (XrayTool or MRI) and delete the ’ character (that means: REM) from the code the Object Inspector will list the properties and methods (and more) of the examined programming object. You must LOAD the inspector before you use it in your code.
1 Like
I don’t think he can use this as condition as @dan11 wrote above
So we have to expect direct formatting and maybe a bunch of auto-created styles from conversion.
.
Ctrl-M could be used for cleanup, if we assume “there is no important formatting inside the cells, endangered to be lost.”
1 Like
Please, do not use (and do not advise using) ImplementationName in your code. Ever. This property (actually, getImplementationName function) value is not guaranteed to be stable. Additionally, an implementation name tells you nothing about what it supports - and the mapping of an implementation name to supported methods is only in a programmer’s head.
If you need to know what you deal with, do it correctly: you need to call a method of an interface → either check if this interface is implemented, directly using HasUnoInterfaces function (this function is specific to Basic, using introspection internally); or check if a service is supported, that includes the needed interface - using XServiceInfo::supportsService method - this is the UNO object’s own method, so portable to other programming languages. And the mapping from services to interfaces, and from interfaces to methods is documented in the API, and guaranteed to be stable.
2 Likes
I am trying to remember it (and use it) in the future. 
Here is the modified code:
REM ***** BASIC *****
option explicit
Sub findAndUnderline
Dim oCurrSel as object
Dim oRange as object
Dim sTarget As String
Dim selIndex as integer
sTarget = InputBox ("Search phrase")
oCurrSel = ThisComponent.CurrentSelection
' xray oCurrSel
if oCurrSel.supportsService("com.sun.star.sheet.SheetCellRanges") then
Print "Some Ranges are selected"
For selIndex = 0 to oCurrSel.getCount() - 1
oRange = oCurrSel.getByIndex(selIndex)
MarkStrings(oRange, sTarget)
Next
end if
if oCurrSel.supportsService("com.sun.star.sheet.SheetCellRange") then
Print "One Range is selected"
oRange = oCurrSel
MarkStrings(oRange, sTarget)
end if
if oCurrSel.supportsService("com.sun.star.sheet.SheetCell") then
Print "One Cell is selected"
oRange = oCurrSel
MarkStrings(oRange, sTarget)
end if
End Sub
Sub MarkStrings(oOneRange as object, sText as string)
Dim oCell as object
dim tc as object
Dim i, j, start, pos as integer
For i = 0 To oOneRange.Rows.getCount() - 1
For j = 0 To oOneRange.Columns.getCount() - 1
oCell = oOneRange.getCellByPosition( j, i )
REM https://stackoverflow.com/questions/35239915/find-all-strings-within-a-string
start = 1
Do
pos = InStr(start, oCell.String, sText)
If pos > 0 Then
start = pos + 1
tc = oCell.CreateTextCursorByRange(oCell.Start)
tc.goRight(pos - 1, False)
tc.goRight(Len(sText), True)
tc.CharUnderline = 2
End If
Loop While pos > 0
Next
Next
End Sub
2 Likes
Two other things that may be worth noting:
- Similar functionality could be also somewhat achieved with Calc’s builtin “Find and Replace” tool (as explained recently in the other thread) with the added benefit of undoing the action with a single press of ctrl+z (current script requires you to undo changes for each occurrence of a keyword separately). This script however also formats the sought keyword, which I think makes it stand out more (YMMV).
- Final use case: If you assign this script to a keybinding (I chose Shift+Alt+F) you can start the search for a keyword on the sheet (Ctrl+F) to select the first cell, close the search tool (Esc) and press the new keybinding (Shift+Alt+F) to highlight the keyword within that cell. If you haven’t found it undo the changes (press Ctrl+Z a few times) and search for the next cell with your keyword (press Ctrl+Shfit+F - no need to open the “Find” tool again). Run the script again on the next cell (Shift+Alt+F). Repeat the last two steps until you found what you were looking for. There are other ways to go about it, but this worked for me.
3*. Also I found tc.CharBackColor = 255 instead of tc.CharUnderline = 2 (on line 50) stand out a bit more. It has a downside though that to undo it you will need to press Ctrl+Z twice.
Meanwhile the question has internally and externally split up in many questions.
Let me name this variant “find cells containing text, find this text also inside the found cells, and apply a character attribute to those findings (without changing previously set attribution)”.
I now present a preliminary solution to this “mission”. It is obviously incomplete but I won’t use the solution myself, and I’m not on the way to make it pretty for selling. To the contrary I consider to retire from this complex. Let spreadsheets be spreadsheets is good advice though often disregarded.
One more thing: The solution contains a few rather matured parts from my personal Standard library. That’s public domain as everything made by me, but I would ask you, to NOT SPREAD REWORKED versions without taking full responsibility (and possibly inform me).
searchIntoCellsDinosaurs_sampleB.ods (27.8 KB)
There was a bug resulting in an error. The version attached below has a workaround for it.
searchIntoCellsDinosaurs_sampleCwithWorkaround.ods (27.7 KB)
2 Likes
Greetings to @JohnSUN. You surely will find a better and more complete solution.
I consider your remark that we share a similar spirit to be high praise.
However, I cannot keep up in many respects.
May peace embrace your life again.
2 Likes
