We have an ODS file with two spreadsheets. (Picture 1)
We are able to use ‘Find All’ to locate cells containing the string ‘Steve’. (Picture 2)
However, we are unable to copy them. Because they are on two different spreadsheeds
Is it possible to achieve this somehow?" (Picture 3)
Brief: We need to copy all rows on all spreadsheets that includes string
"I can partially achieve what I want using LibreOffice Base. With SQL, I’m able to retrieve all cells containing ‘Steve’, but only within a single spreadsheet. I need a better solution that works across all spreadsheets.
My idea is:
The Find and Replace button can modify all matches at once.
Would it be possible to use a regex or a similar approach to ‘mark’ all matches as selected?
For example:
Is it possible at that way?
probably need to move away from your all spreadsheets.
https://help.libreoffice.org/latest/en-US/text/shared/guide/data_tables.html
Writing GUI is little complicated to work on different devices. (Like Mobile)
Also data security is another problem.
At calc you have build in security as well. I’m not sure it is exist at Base Database
Macro for simple F&R in all sheets
Sub replaceInAllSheets 'F&R in all sheets
dim oDoc as object, oSheet as object, oDesc as object, sFind$, oFound as object, o as object
oDoc=ThisComponent
sFind="Steve" 'what to find
for each oSheet in oDoc.Sheets
oDesc=oSheet.createReplaceDescriptor
with oDesc
.SearchString=sFind
.ReplaceString="select(" & sFind & ")"
.SearchCaseSensitive=false
.SearchRegularExpression=false
end with
oSheet.replaceAll(oDesc)
next
End Sub
Test macro for your primal ask, it creates a new Sheet with copies of all rows that have found text.
Sub copyFoundToNewList 'copy all rows that have the found text to new List
dim oDoc as object, oSheets as object, oSheet as object, oCopySheet as object, oDesc as object, sFind$, oFound as object, o as object, oRows as object, oRow as object, _
oRange as object, oCur as object, iLast&, iEnd&, iRow&, oCopy as object, oCopyCur as object, iSheets&, i&, bCopy as boolean
oDoc=ThisComponent
oSheets=oDoc.Sheets
sFind=inputbox("Find:")
if sFind="" then exit sub
iSheets=oSheets.Count-1 'count of sheets without the sheets for copied rows
for i=0 to iSheets 'traverse all sheets
oSheet=oSheets.getByIndex(i)
oDesc=oSheet.createSearchDescriptor
with oDesc 'search descriptor
.SearchString=sFind
.SearchCaseSensitive=false
end with
oFound=oSheet.findAll(oDesc) 'find all in current sheet
if NOT isNull(oFound) then 'something is found
oCur=oSheet.createCursor
oCur.goToEndOfUsedArea(false)
iEnd=oCur.RangeAddress.EndColumn
if isNull(oCopySheet) then oCopySheet=createNewSheet(oSheets, sFind) 'create sheet for copy if not exists
oCopyCur=oCopySheet.createCursor()
iLast=-1 'last checked row for copy
for each oRange in oFound 'traverse the founds in found
for each oRows in oRange.Rows 'check the rows in found
for each oRow in oRows.Rows 'copy row by row
iRow=oRow.RangeAddress.StartRow 'number of current row
o=oSheet.getCellRangeByPosition(0, iRow, iEnd, iRow)
if iRow<>iLast then 'current row wasn't copied (because there can be more cells with the found text in one row)
oDoc.CurrentController.select(o) 'select row
oCopy=oDoc.CurrentController.getTransferable() 'Copy row
oCopyCur.goToEndOfUsedArea(false) 'found last used position in copy-sheet
if bCopy then oCopyCur.goToOffset(-oCopyCur.RangeAddress.EndColumn, 1) 'if it isn't 1st copy then go to new line in copy sheet
bCopy=true
oDoc.CurrentController.select(oCopyCur)
oDoc.CurrentController.insertTransferable(oCopy) 'Paste copied row
end if
iLast=iRow 'remember the number of last row
next
next
next
end if
next i
End Sub
Function createNewSheet(oSheets as object, sFind$) 'create new sheet for copies of found rows
dim s$, i%, sName$
sName="COPY-" & sFind 'base name of copy-sheet
s=sName
while oSheets.hasByName(s) 'copy-sheet exists
i=i+1 'add the number to the name
s=sName & "-" & i 'new name with some number
wend
oSheets.insertNewByName(s, oSheets.Count) 'add copy-sheet to the end of lists
createNewSheet=oSheets.getByName(s) 'return the name of copy-shet
End Function