[SOLVED] Insert a cell with a Basic Macro in LibreOffice Calc

Insert a cell with a Basic Macro in LibreOffice Calc

I'm trying to insert a cell beginning with the first cell in a range (via the .getCellRangeByName() method of a Document's active Sheet).

I found out how to do this with a Dispatcher from the OpenOffice library (.uno:InsertCell), but I'd prefer to use something that doesn't require the dispatcher if possible.

Example code that I plan to wire-up to a button...

Sub AddManualBalance(EntryDate As Date, EntryAmount As Currency)

    Dim Doc As Object
    Dim Sheet As Object
    Doc = ThisComponent
    If Doc Is Nothing Then
        Return
    EndIf
    Sheet = Doc.getCurrentController().getActiveSheet()
    If Sheet Is Nothing Then
        Return
    EndIf
    
    Dim TargetCells As Object
    TargetCells = Sheet.getCellRangeByName("B9:C9");

    // insert a cell in both the B and C columns at position 9,
    // then move all other cells down

    // add my EntryDate as a value to the new cell in B column
    // add my EntryAmount as a value to the new cell in C column

End Sub

Solution

Following code does what you want:

Dim Doc As Object
Dim Sheet As Object
Dim oDestCell As Object
Dim CellRangeAddress As New com.sun.star.table.CellRangeAddress

Doc = ThisComponent
Sheet = Doc.Sheets(0)

CellRangeAddress.Sheet = 0
CellRangeAddress.StartColumn = 1
CellRangeAddress.StartRow = 8
CellRangeAddress.EndColumn = 2
CellRangeAddress.EndRow = 8

Sheet.insertCells(CellRangeAddress, com.sun.star.sheet.CellInsertMode.DOWN)

oDestCell=Sheet.getCellByPosition(1,8)
oDestCell.setValue(EntryDate)

oDestCell=Sheet.getCellByPosition(2,8)
oDestCell.setValue(EntryAmount)

You can read about C++ and LibreOffice at api.libreoffice.org