How to select a range of cells in a macro in Calc, using Basic, not Python?

Question

(Context is below, for those who want to know.)

I am slowly developing a set of macros for LibreOffice Calc, working from "record macro" and then trying to edit until I have a general purpose solution. I am stuck with the selection aspect.

In many examples or documentation I find something like this:

selection = ThisComponent.CurrentSelection

Now I want to create code like this pseudo-code to fully automate my macros, and cannot find the proper syntax for Calc:

selection = ThisComponent.RangeByName("D2:oLastActiveCell")
now, dear macro, do the actual selecting
now do some actual work to freshly defined selection

I have done plenty reading and found this promising "tool": getCellRangeByName() And I have found out that it does not work out of the box, and I need to define something about the active Sheet like ThisComponent.Sheets(0) And it seems that to "do" the actual selection I could use something like ThisComponent.CurrentController.Select(oHereTheAreaNeeded) So I get my macro going to a certain point and then get flooded with Error messages when I apply the "work" to my selection.

When I hand-select by mouse-drag, the "work" part of the macro runs fine (see below in context-part), so obviously I am missing elements or I fail to bring the selection-part together in the right order. Sorry, if this is too obvious. I am learning this from scratch. There are other aspects like opening-files, deleting-columns or sorting, which I have already figured out. I am not trying to be lazy.

Please note, that I am already figuring out the value of "oLastActiveCell", so that is not part of this question. You could read

selection = ThisComponent.RangeByName("D2:D215")

if it helps for the selection-question.

===

Context: I need to batch-process a large quantity of bank-statements (for the next ten years, from several different banks) to prepare them for import into accounting-programs. The statements are rather huge and ugly but luckily they are the same structure each month, so I am working on a workflow where the user never needs to open the CSV files.

This is why "CurrentSelection" will not do the job, since there is no human user and nothing is selected by anybody. But I know exactly where the work is to be done. In this case from D2 to D215 aka D2:D215 for a certain month and in general D2:oLastActiveCell

For those who want to know even more: What is being done to the selected area (range?), what I called "actual work" in my pseudo-code above? In my present phase, I am applying the reference-numbers, as needed by the accounting. I am using the "fill cell with series linear" feature in Calc.

The macro knows (from a dialog) the first reference-number oFirstReference and will figure out how many active lines in any bank statement. So something like this shall be done to the selection (this part is already working well, when I manually select a range) so is only given as context:

dim args65(5) as new com.sun.star.beans.PropertyValue
args65(0).Name = "FillDir"
args65(0).Value = "B"
args65(1).Name = "FillCmd"
args65(1).Value = "L"
args65(2).Name = "FillStep"
args65(2).Value = "1"
args65(3).Name = "FillDateCmd"
args65(3).Value = "D"
args65(4).Name = "FillStart"
args65(4).Value = "oFirstReference"
args65(5).Name = "FillMax"  'I do not understand this line, was added by record macro
args65(5).Value = "1,7E+307" 'this is possibly a protection agains some overrun?

dispatcher.executeDispatch(document, ".uno:FillSeries", "", 0, args65())

Answer 1

First, I'll answer the question you asked. In the vast majority of cases, code like this will select the range of cells from D2 to the most recently used cell on the current (active) sheet. Exceptions will occur on sheets where there are no filled cells below cell D2.

Sub selectD2_LastUsedCell()
Dim oCurrentController As Variant
Dim oActiveSheet As Variant
Dim oCellD2 As Variant
Dim oCursor As Variant
    oCurrentController = ThisComponent.getCurrentController()
    oActiveSheet = oCurrentController.getActiveSheet()
    oCellD2 = oActiveSheet.getCellRangeByName("D2")
    oCursor = oActiveSheet.createCursorByRange(oCellD2)
    oCursor.gotoEndOfUsedArea(True)
    oCurrentController.select(oCursor)
End Sub

Now I will try to convince you that you do not need this subroutine to solve your problem. In both Calc and Excel, visual actions greatly slow down the execution of the script. Therefore, it is strongly recommended to remove from the code all actions related to the selection of sheets-cells-ranges, they do not help in any way when processing data, they are harmful. In fact, your task is to obtain and then process data from a spreadsheet. And this is done differently.

Sub processAllSheets()
Dim oDoc As Variant
Dim oSheets As Variant, oSheet As Variant, oCursor As Variant
Dim nEndColumn As Long, nEndRow As Long, nRow As Long
Dim oRange As Variant
Dim aDataArray As Variant, aRow As Variant
    oDoc = ThisComponent
    oSheets = oDoc.getSheets()
    For Each oSheet In oSheets
        oCursor = oSheet.createCursor()
        oCursor.gotoEndOfUsedArea(False)
        nEndColumn = oCursor.getRangeAddress().EndColumn
        If nEndColumn > 2 Then ' If the last filled column is less than D, there is no point in continuing
            nEndRow = oCursor.getRangeAddress().EndRow
            If nEndRow > 1 Then ' If there are no values ​​below cell D2, then there is no point in continuing.
                oRange = oSheet.getCellRangeByPosition(3, 1, nEndColumn, nEndRow)
                aDataArray = oRange.getDataArray()
                For nRow = LBound(aDataArray) To UBound(aDataArray)
                    aRow = aDataArray(nRow)
                    ' Here do something with the data of each row of your table
                    ' Just for example - show content of this row
                    Print Join(aRow,"; ")
                Next nRow
            EndIf 
        EndIf 
    Next oSheet
End Sub

If you were able to read and understand the code of this procedure, you will be able to finish your project quickly. Otherwise, you should download Andrew Pitonyak's book English Macro Document. You don't need to read everything at once, use the listings as recipes from a cookbook - very quickly you will understand the essence of programming for LibreOffice (and be surprised how wrong your initial approach to solving the problem was).