libreofficelibreoffice-basicopenoffice-basic

How to copy data into cells, make a table and add more data to it


UI looks like:
Account:Wessex bank plc
Income: 200€
Costs:
Date: 28.02.2021

Output should be a list below in the cells:
Date: | Account: | Income: | Costs: |

28.02.2021 | Wessex Bank plc | 200€ |
28.02.2021 | Food | - | 175€ |
Hint: I would like to have a list of 5-7 bookings and when
making a new booking the latest booking is going to be at the top position and the first booking in the last row, like when the table starts at row 13 and I make 5 bookings with different accounts, the first booking will be at 17 in the end.

this is copying the content in the table

Sub MyBuchenMakro   
    Dim currDoc As Object
    Dim currSheet As Object
    Dim curr Cell As Object
    Dim destCell As Object  
    Dim oDate As Date
    Dim einnahmen As Currency
    Dim ausgaben As Currency  
    currDoc = ThisComponent
    currSheet = currDoc.sheets(0)  
    currCell = currSheet.getCellByPosition(1, 5)
    destCell = currSheet.getCellByPosition(1, 12)
    destCell.String = currCell.String  
    currCell = currSheet.getCellByPosition(1, 6)
    destCell = currSheet.getCellByPosition(2, 12)
    destCell.setValue(CCur(currCell.getValue()))  
    currCell = currSheet.getCellByPosition(1, 7)
    destCell = currSheet.getCellByPosition(3, 12)
    destCell.setValue(CCur(currCell.getValue()))  
    currCell = currSheet.getCellByPosition(1, 8)
    destCell = currSheet.getCellByPosition(0, 12)
    destCell.setValue(CDate(currCell.getValue()))    
For i = 160 To 13 Step 1
  destCell = currSheet.getCellByPosition(0, i)
  If destCell == "" Then
     GoTo Continue
  End if
destCell = currSheet.getCellByPosition(0,i+1)
destCell.setValue(CDate(currCell.getValue()))
currCell = currSheet.getCellByPosition(1,i)
destCell = currSheet.getCellByPosition(1,i+1)
destCell.String = currCell.String
currCell = currSheet.getCellByPosition(2,i)
destCell = currSheet.getCellByPosition(2,i+1)
destCell.setValue(CCur(currCell.getValue()))
currCell = currSheet.getCellByPosition(3,i)
destCell = currSheet.getCellByPosition(3,i+1)
destCell.setValue(CCur(currCell.getValue()))
 Next i
End Sub  


  [1]: https://i.sstatic.net/Mw7pJ.png

Solution

  • In fact, it is written a little shorter:

    Option Explicit 
    Sub BuchenMacro
    Dim oCurrentController As Variant   ' get Activesheet and select first cell of form
    Dim oSheet As Variant   ' Activesheet
    Dim oSourceRange As Variant ' Range B6:B9 - fields of input form
    Dim oDataArray As Variant           ' Data from input form
        oCurrentController = ThisComponent.getCurrentController()
        oSheet = oCurrentController.getActiveSheet()
    Rem Range with data
        oSourceRange = oSheet.getCellRangeByName("B6:B9")
    Rem Data from this range as "array of arrays"
        oDataArray = oSourceRange.getDataArray()
    Rem To prevent insert empty row - validate source cells:
    Rem If 3 first cells are empty then stop:
        If Trim(oDataArray(0)(0))+Trim(oDataArray(1)(0))+Trim(oDataArray(2)(0)) = "" Then Exit Sub 
    Rem "Transpose" source data to single row:
        oDataArray = Array(Array(oDataArray(3)(0), oDataArray(0)(0), oDataArray(1)(0), oDataArray(2)(0)))
    Rem Insert new row after header and shift all other rows down:
        oSheet.getRows().insertByIndex(12, 1)
    Rem Paste data from form to this new row
        oSheet.getCellRangeByPosition(0, 12, 3, 12).setDataArray(oDataArray)
    Rem Clear input cells to prevent duplicates
    Rem (Only the data is cleared, the formulas remain in place. 
    Rem Put in cell B9 the formula =TEXT(TODAY();"DD.MM.YYYY")
    Rem and it will always show the current date)
        oSourceRange.clearContents(7)
    Rem Select first cell
        oCurrentController.Select(oSheet.getCellByPosition(1,5))
    Rem Deselect cell
        oCurrentController.Select(ThisComponent.createInstance("com.sun.star.sheet.SheetCellRanges"))
    End Sub