user416752
user416752

Reputation:

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

Upvotes: 2

Views: 4482

Answers (2)

user416752
user416752

Reputation:

Thanks to Mac, my code is now...

Public Doc As Object
Public Sheet As Object

Sub AddManualBalance()

    GetCurrentSheet()

    REM insert two new cells, move cells down
    Dim TargetCells As New com.sun.star.table.CellRangeAddress
    TargetCells.Sheet = 3
    TargetCells.StartColumn = 1
    TargetCells.StartRow = 8
    TargetCells.EndColumn = 2
    TargetCells.EndRow = 8
    Sheet.insertCells(TargetCells, com.sun.star.sheet.CellInsertMode.DOWN)

    REM get date and balance from text boxes, add value to cells
    Dim BalanceDate As Object
    Dim BalanceAmount As Object
    Dim Forms As Object
    Dim MainForm As Object
    Forms = Doc.getCurrentController().getActiveSheet().getDrawPage().getForms()
    MainForm = Forms("MainForm")
    BalanceDate = MainForm.getByName("BalanceDate")
    BalanceAmount = MainForm.getByName("BalanceAmount")
    Sheet.getCellByPosition(1,8).setValue(BalanceDate.CurrentValue)
    Sheet.getCellByPosition(2,8).setValue(BalanceAmount.CurrentValue)

End Sub

Sub GetCurrentSheet()

    REM get references to document and active sheet, test if exist
    If ThisComponent Is Nothing Then
        Return
    End If
    Doc = ThisComponent
    If Doc Is Nothing Then
        Return
    EndIf
    Sheet = Doc.getCurrentController().getActiveSheet()

End Sub

Upvotes: 1

Maciej
Maciej

Reputation: 9605

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

Upvotes: 1

Related Questions