Star, Open, Libre. Automating your spreadsheets

I started using StarOffice when it first came out and it was good. OpenOffice was great and did everything I ever needed. When Oracle bought Sun Microsystems the developers left to create LibreOffice. OpenOffice was released from Oracle and now lives in the Apache Software Foundation. Some day the two may get unified but for now LibreOffice is the popular choice, and it is great.

I love to create spreadsheets and often offer to help my co-workers with automating their scalc files for them. I have a lot of fun writing StarBasic macros. With a little work the StarBasic macros can be modified to work in other office programs.

Two functions that I add to almost every spreadsheet are GetCell and GetDataCell to make it easy and fast to read and change the cell contents.

Function GetDataCell(SheetName As String, DataName As String, Row) As com.sun.star.table.XCell
 AllSheets = ThisComponent.Sheets()
 FindSheet = AllSheets.GetByName(SheetName)
 For ALoopCounter = 0 to 1000 Step 1
  TheCell = FindSheet.GetCellByPosition(ALoopCounter,0)
  If TheCell.String = DataName then
   TheCell = FindSheet.GetCellByPosition(ALoopCounter,Row)
   Exit for
  End if
  If TheCell.String = "" then
   MsgBox("Could not find DataField = "+DataName)
   Stop    
  End if
 Next ALoopCounter 
 GetDataCell = TheCell
End Function


Function GetCell(SheetName As String, Column, Row) As com.sun.star.table.XCell
 AllSheets = ThisComponent.Sheets()
 FindSheet = AllSheets.GetByName(SheetName)
 TheCell = FindSheet.GetCellByPosition(Column,Row)
 GetCell = TheCell
End Function