That dashed line is just to indicate from where we have to cut the page. The dashed line is appearing on top of the page which is not needed. Sorry for giving you trouble, but ```
.String=data(i)(0) 'Act Section
I suppose the .String=data(i)(0) 'Act Section means there is problem with the count of lines and sometimes it put text below “IN THE COURT OF” instead above. I add the test if the line contains “IN THE COURT OF”.
And also there is commented line '.String=data(i)(0) 'Act Section 'string from ODS and it puts string found in ODT, you can uncomment this line and comment next one to write string from ODS.
Updated part:
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
.goToStartOfLine(false)
oDoc.Text.insertControlCharacter(oVCur.End, com.sun.star.text.ControlCharacter.LINE_BREAK, false) 'add Shift+Enter at the top of Found
.goUp(1, false)
.CharWeight=com.sun.star.awt.FontWeight.BOLD
'.String=data(i)(0) 'Act Section 'string from ODS
.String=oFound.String 'string found in ODT
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
1 Like
Still the Case No column is appearing where I expected Act Section column to appear.
Change one line in the part started rem get data from ODS
oRange=oSheet.getCellRangeByPosition(0, 1, oCur.RangeAddress.EndColumn, oCur.RangeAddress.EndRow) 'columns without 1st row
And .String= in main loop:
if InStr(sLine, "IN THE COURT OF")>0 then 'test if "IN THE COURT OF" is in current line
.String=data(i)(2) 'Act Section
1 Like
After this Act Section is appearing. Thank you so much for this. But above that the case number is also appearing which is not needed. Also if the dashed line is at the beginning of a page it should be removed.
For me, it is time to say STOP. I respect you don’t know programming, but you work with the system that is bigger and more exacting. In some cases it seems easily, but it isn’t so easy. I will not write next and next and next and next and next macros that move the Cursor to some positions to write some string. If you don’t understand to the movements of Cursor and insertings the strings from previous examples - and it seems you don’t understand to it - then next and next and next already same or similar requests are only degradation of my knowledge and help → to more and more and more chaos and swamp.
It seems you work with some systems that generates the “simple” text with “simple” tables, but there are some differences that need more and more and more conditions to “convert” data to some better design. I don’t know all combinations that could be occured and I will not write next and next and next and next same or similar operations to serve next and next and next and next exceptions etc. And I think there is big chance the system that generates the “raw data” will change the output and then there will be next and next and next and next fixing of previous macros - with the similar or same operations, but probably with next and next and next and next conditions for next and next and next and next exceptions.
Probably you need (or you will need) also some operations with strings like truncate ones, replace in ones etc., replace with regexp in ones etc. → but I suppose for next and next and next and next more and more strange and probably unnecessary “operations” to improve system that is made partially badly from the start. It isn’t good way to change raw data to direct formating, it could be exampled like: raw data are affair of 19th century, direct formatting of 20th century, any Styled formatting of 21th century. And spend a lot of time to get data from 19th century to the design of 20th century while 21th century is → it is useless work. And then much more complicated work will be to convert data from 20th century to 21th.
Yes, it isn’t so easy to make (high-)quality design completely based on Styles for your tables, and then make the conversion directly to full-styled design, but it isn’t impossible.
And do you write quality documentation for the macros you already collectioned? If yes, it must be really very hard work to describe all replacements and modifications for direct formatting only from your oDesc1, oDesc2, oDesc3, oDesc4, oDesc5, oDesc6, big serie of RDescrip etc. Of course with reasons why it is made as it is. To faciliate the understanding of used system to next people that will work with it. So I suppose there will be probably only talked “click here and run this and then run this and then run that and then run this and then … of course → run this! … oh sorry, run that… ooh sorry, some error, we must start from the start :-(”. And again and again.
And improvements of that systems are still the same → irritationed and exhaustioned. And I surely know it isn’t work for me.
It is complicated for me to translate well the Czech word nadšení. In Czech, it is mostly obvious term, but translation to English could be: enthusiasm, zeal, avidness or keenness or some next words. There was good example in dictionary for keenness → “My keenness for sports faded away.”, so I use the keenness. Sometimes I say: “Where is keenness, (there) will come burnout.” It is not some moralization, sermonizing or preachment, but the description of my experience, and I think you are in keenness from partial improvement of system, but the system that is partially bad from the start. I don’t wish you any burnout, but I think the burnout will come with next and next work on that system. And I surely know it isn’t for me.
1 Like
Ok, You helped me a lot. and the problem remaining of removing dashed line is not that much important. and I also can adjust the case number. thank you so much. More help is expected on different issues in furture.
I managed to delete the case number by following code. Thank you so much.
dim oDoc23 as object, oDesc23 as object
oDoc23=ThisComponent
oDesc23=oDoc23.createReplaceDescriptor
with oDesc23
.SearchString="[:print:]{1,100} / [:digit:]{1,100} / [:digit:]{1,4}\n"
.SearchRegularExpression=true 'by regexp
.ReplaceString=""
end with
oDoc23.replaceAll(oDesc23)
Small tips for multiReplacing, to eliminate the series of oDesc1, oDesc2, … oDescXXX, where XXX could be some sufficiently amazing number 
.
Sub multiReplace
dim oDoc as object, oDesc as object, aRepl(), arr()
aRepl=array( array("Find", "Replace"), array("aaa", "AAA"), array("bbb", "BBB") ) 'array with sub-arrays with: "Find", "Replace"
oDoc=ThisComponent
oDesc=oDoc.createReplaceDescriptor
with oDesc
.SearchRegularExpression=true 'by regexp
.SearchCaseSensitive=false 'no case-sensitive
end with
for each arr in aRepl 'all replacements in one loop
with oDesc
.SearchString=arr(0) 'Find
.ReplaceString=arr(1) 'Replace
end with
oDoc.replaceAll(oDesc)
next
End Sub
There can be also for example the place for regexp possibility in array(), with detection of ubound of this array to set regexp searching.
Sub multiReplaceRegexp
dim oDoc as object, oDesc as object, aRepl(), arr()
aRepl=array( array("ccc", "123"), array("a+", "A", true), array("b{2}", "@@", true) ) 'array: "Find", "Replace", byRegexp -> array( ... , ... , true) means Find&Replace by regexp
oDoc=ThisComponent
oDesc=oDoc.createReplaceDescriptor
for each arr in aRepl 'all replacements in one loop
with oDesc
.SearchString=arr(0) 'Find
.ReplaceString=arr(1) 'Replace
if ubound(arr)>1 then .SearchRegularExpression=arr(2) 'by Regexp
end with
oDoc.replaceAll(oDesc)
next
End Sub
But of course the well arranged way is to have the definitions in single lines.
aRepl=array( _
array("ccc", "123"), _
array("a+", "A", true), _
array("b{2}", "@@", true) _
)
@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.