Insert a cell with a Basic Macro in LibreOffice Calc

Question

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

Thanks in advance for any help!

P.S. I really dislike Basic, but it seems that with spreadsheets and office app automation, that's the preferred language. Is there any way to do LibreOffice/OpenOffice macros in a more C-like language?

Maciej · Accepted Answer · 2015-09-10T00:55:00.263

2

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

edited Sep 10 '15 at 00:55

answered Sep 10 '15 at 00:42

Maciej

9,355
2
15
18

Thanks! Your code helped me get to where I wanted to go. I changed it to match my code, and will answer my own question below thanks to your help. However, the API in C++ doesn't really help me, since I want an alternative macro language, not to edit LibreOffice itself. I found that you can add javascript to Calc, but not sure if this works like a macro or not, and if you can use javascript to write new functions usable in cells, like you can with Basic (eg: function MYFUNC(parm1 as object, parm2...)) – John Suit Sep 10 '15 at 17:30

John Suit · Answer 2 · 2015-09-10T17:41:04.440

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

Insert a cell with a Basic Macro in LibreOffice Calc

2 Answers2