LibreOffice BASIC Easy GetCell

Improving on GetCell from an earlier post I have added a Column2Index function to allow you to pass the names of the column and row rather then the index.

If the column is a number then it will use the values as index values, otherwise it will convert both the column and the row.

These two calls are the same
GetCell("Sheet1", "D", 3) <- using the names of the columns and rows
GetCell("Sheet1", 3, 2) <- using the index of the columns and rows


For cell that contain numbers, ether fixed or formula generated you can simply read from them or write to them like so.

If GetCell("Sheet1", "D", 3).Value > 100 Then
   GetCell("Sheet1", "D", 3).Value = 0
End If

For cells that contain text you use GetCell("Sheet1", "D", 3).String the same way you would any normal string variable.

Function Column2Index(ColNameX As String)
 ReturnInt = 0
 ColNameU = Ucase(ColNameX)
 While ColNameU <> ""
  LastChar = Left(ColNameU, 1)
  ColNameU = Right(ColNameU, Len(ColNameU) - 1)
  ReturnInt = ReturnInt * 26
  ReturnInt = ReturnInt + Asc(LastChar) - 64
 Wend
 Column2Index = ReturnInt - 1
End Function

' Now accepts column and row as they are named or their index value
' These two calls are the same
' GetCell("Sheet1", "D", 3) <- using the names of the columns and rows
' GetCell("Sheet1", 3, 2) <- using the index of the columns and rows


Function GetCell(SheetName As String, Column, Row) As com.sun.star.table.XCell
 ColumnNumber = 0
 RowNumber = 0
 If ISNUMERIC(Column) Then  
  ColumnNumber = Column
  RowNumber = Row
 Else
  ColumnNumber = Column2Index(Column)
  RowNumber = Row - 1 
 End If
 AllSheets = ThisComponent.Sheets()
 FindSheet = AllSheets.GetByName(SheetName)
 TheCell = FindSheet.GetCellByPosition(ColumnNumber,RowNumber)
 GetCell = TheCell
End Function

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