• Libre calc: For macros is it possible to target multiple sheets at once?

[michaely]

Im currently using this code to delete the cell contents in the active sheet. But im wondering what changes I should make so it will delete the contents in mutlipel sheets?

Sub SearchAndDoStuff3
    Const needle = "Rory "
    Const searchRange = "A1:Z1500"
    Set oSheet = ThisComponent.CurrentController.ActiveSheet
    Set oRange = oSheet.getCellRangeByName(searchRange)
    For i = 0 To oRange.Rows.getCount() - 1 
        For j = 0 To oRange.Columns.getCount() - 1 
            Set oCell = oRange.getCellByPosition( j, i )
            idx = InStr(oCell.String,needle)
            If (  idx > 0) Then
                    oCell = oRange.getCellByPosition( j-1, i )
                    oCell.String = "Rob"
            Endif
        Next
    Next 
End Sub

[Lupp]

You don’t delete cell contents, but search specific ones and replace them.
You work with single cells anyway. Of course you can do this in the same way for multiple ranges and multiple sheets. Simply get the sheets by ThisComponent.Sheets(sheetIndex) and loop through the sheet indices you need.
If you want to get the result in a more efficient way, use a SearchDescriptor with

.SearchRegularExpression = True
.SearchString = "^.*?Rory .*$"
.ReplaceString = "Rob"  

and then apply it per range:

range.ReplaceAll(SearchDescriptor)

[JohnSUN]

@Lupp He want to replace the text in the previous cell, and not in the target one - you see, oRange.getCellByPosition( j-1, i )? He was still lucky and the word “Rory” never appeared in column A

[Lupp]

Oh my! Sorry.
Anyway: Working with every single cell is inefficient in such a case. He (f/m) should use the DataArray of the target range.
And again: To do something of the kind for more than one sheet (in specific: not only for the ActiveSheet) isn’t an issue at all. The sheet to work on simply must be accessed as an object based on its name or on its index. If the sheets aren’t listed in a clear way nobody can help. How to pass parameters (like a sequence of sheet indices) to a Sub is a much more general issue. For a spreadsheet document a dedicated cell is one of the obvious ways to do it.