@KamilLanda Happy Teachers Day (In India, 5th of September is celebrated as Teachers Day). You taught me lot. I know you are exhausted but if you help me to figure out the problem that after running the macro the line “In the court of” is disappearing how to avoid this. I am extremely sorry to give you trouble. But if you help me to solve this problem it will be useful for hundreds and thousands of my colleagues.
And what to do with these files? There isn’t any “IN THE COURT OF”, so how to solve something that doesn’t exist?
I cannot solve it and I will not solve it. I cannot help with that system. It is system built on mixed macros with Ctrl+C/V without needed understanding to operations in used macros. Maybe it is better don’t public it, than public it without needed understanding. And understanding and modifications of move of cursor and inserting text is on you, not on me. I don’t want to continue in Ctrl+C/V “chaos”, and unfortunately it is sufficient chaos for me 
.
And why I have feeling you are able to solve it alone? But you try to talk me into it, because you think it will be easier or more comfortable for you? For interest (joke), this is patron of all Czech teachers, but I really wouldn’t want to be like him 
. https://www.youtube.com/watch?v=k8YMr9oMtz0
Sorry I sent you a file after running the macro. I will send the file before running the macro tomorrow.
06-09-2023 Criminal.odt (53.8 KB)
Please check this file. I understand you are busy these days. But I am also extremely busy these days. One post is vacant and one person is on leave in my office. I have to do work of three persons alone. So I am not getting time to learn macros.
Then it’s time that this error-prone job creation measure is replaced by a better solution!
@karolus Just one night before your rude comment. I was thinking of recommending ask libreoffice to my colleagues and followers, who consider me as a computer expert. I know I am child in front of @KamilLanda . But because of your rude replies I think it is not at all for persons who have less computer knowledge than me.
I understand your situation. When I was MS-Office teacher there used to be questions in demo test make the fourt word in fifth sentence bold. Then one of my student used to say I learn this now tell me the answer of this question make third word of the fourth sentence bold.
Here is the fix :-). It adds two lines under IN THE COURT OF, I’m not sure if 2nd line is needed.
Sub addDataFromODS
on local error goto bug
rem ODS info
const cODSname="Roznama Vlookup.ods", cSheetName="Sheet3"
dim oDoc as object, sUrl$, p()
oDoc=ThisComponent 'current ODT
p=split(oDoc.URL, "/") : p(ubound(p))=cODSname 'your ODS is in the same directory like current ODT
sUrl=join(p, "/") 'URL to ODS
rem get data from ODS
dim ODS as object, oSheet as object, oRange as object, oCur as object, data(), args(0) as new com.sun.star.beans.PropertyValue
args(0).Name="Hidden" : args(0).Value=true
ODS=StarDesktop.loadComponentFromUrl(sUrl, "_blank", 0, args()) 'open ODS as hidden
oSheet=ODS.Sheets.getByName(cSheetName)
oCur=oSheet.createCursor : oCur.goToEndOfUsedArea(false) 'detect last cell in sheet
oRange=oSheet.getCellRangeByPosition(0, 1, oCur.RangeAddress.EndColumn, oCur.RangeAddress.EndRow) 'columns without 1st row
'1st and 2nd column without 1 row
data=oRange.getDataArray() 'data from oRange
ODS.close(true)
rem modify ODT
dim i&, s$, oStatusbar as object, oDesc as object, j&, iLen&, oFound as object, oVCur as object, undoMgr as object, sUndo$, sLine$, iLine%
oStatusbar=oDoc.CurrentController.StatusIndicator 'progressbar in Statusbar
oStatusbar.start("", ubound(data))
oVCur=oDoc.CurrentController.ViewCursor 'visible cursor
undoMgr=oDoc.UndoManager 'object for Undo/Redo
oDoc.lockControllers 'no screen rendering, faster
oDesc=oDoc.createSearchDescriptor
oDesc.SearchRegularExpression=true
for i=lbound(data) to ubound(data)
p=split(data(i)(0), "/")
iLen=Len(p(0)) : s=""
for j=1 to iLen 'adapt the string from ODS, for example SCC/304357/2012 -> S\.*C\.*C\.*\s*/\s*304357\s*/\s*2012
s=s & Mid(p(0), j, 1) & "\.*" '!!! regular expression supposes only dots among letters, no other marks (SCC -> S.C.C.)
next j
p(0)=s
oDesc.SearchString=join(p, "\s*/\s*")
oFound=oDoc.findFirst(oDesc)
if NOT isNull(oFound) then
sUndo=data(i)(0) & " " & data(i)(1)
undoMgr.enterUndoContext(sUndo) 'title for Undo
oDoc.CurrentController.Select(oFound)
with oVCur
.goToRange(oFound.Start, false)
.goToStartOfLine(false)
.String=" "
.collapseToStart
.CharWeight=com.sun.star.awt.FontWeight.BOLD 'set the bold format
.String=data(i)(1) & ")" 'Sr. No.
do 'find "IN THE COURT OF" in lines above the Sr. No.
.goUp(1, false)
.goToStartOfLine(false)
.goToEndOfLine(true)
sLine=oVCur.String
if InStr(sLine, "IN THE COURT OF")>0 then 'test if "IN THE COURT OF" is in current line
rem 1st line
.goToEndOfLine(false)
oDoc.Text.insertControlCharacter(oVCur.End, com.sun.star.text.ControlCharacter.LINE_BREAK, false) 'add Shift+Enter at the top of Found
.String=data(i)(2) 'Act Section
rem 2nd line
.goToEndOfLine(false)
oDoc.Text.insertControlCharacter(oVCur.End, com.sun.star.text.ControlCharacter.LINE_BREAK, false) 'add Shift+Enter at the top of Found
.CharWeight=com.sun.star.awt.FontWeight.BOLD
.String=data(i)(0) 'CASE NO from ODS (column A)
'oFound.String 'string found in ODT (instead CASE NO from ODS)
exit do
end if
iLine=iLine+1
loop while iLine<5 'maximum lines to try above Sr. No.
end with
undoMgr.leaveUndoContext(sUndo) 'more operations (Sr. No. + Act Section) as one operation for Undo
end if
if i MOD 10=0 then oStatusbar.setValue(i) 'update statusbar
next i
oVCur.goToStart(false)
if oDoc.hasControllersLocked then oDoc.unlockControllers 'screen rendering yes
oStatusbar.end : oStatusbar.reset
exit sub
bug: 'some error
if NOT isNull(ODS) then ODS.close(true)
if oDoc.hasControllersLocked then oDoc.unlockControllers
msgbox("line: " & Erl & chr(13) & Err & ": " & Error, 16, "addDataFromODS")
End Sub
1 Like
Thank you so much. God bless you.