Macros: Getting Cell Information

Invariably, macros written for use within the Calc application will need to access the contents of the cells on a spreadsheet. This tip is an introduction to the various available methods.

The three methods we will look at are getCellByPosition, getCellRangeByPosition and getCellRangeByName

The function that is first encountered for most people is getCellByPosition. In the sample below, we access cell A1 (on Sheet1)

Sub getCellInfo

‘get the first sheet of the spreadsheet doc

xSheet = ThisComponent.Sheets(0)

‘Get value of Cell A1

A1_value = xSheet.getCellByPosition(0,0).value

print A1_value

End Sub

The second example shows the use of getCellRangeByName and may be easier to use - because the cells are referenced by the traditional column/row identifiers that are displayed along each axis. However, for applications requiring looping through an array of cells, getCellByPosition is easier to use.

Sub getCellInfo

‘get the first sheet of the spreadsheet doc

xSheet = ThisComponent.Sheets(0)

‘Get value of Cell A3

A3_value = xSheet.getCellRangeByName(”A3″).value

print A3_value

End Sub

The next example shows how getCellInfo grabs an array of cells - myTable. A subsequent call to getCellByPosition for the myTable object is relative to the origin of this array.

Sub getCellInfoByRange

Dim myTable as Object

‘get the first sheet of the spreadsheet doc

xSheet = ThisComponent.Sheets(0)

‘Grab array A3:A5

myTable = xSheet.getCellRangeByName(”A3:A5″)

A5_value = myTable.getCellByPosition(0,2).value

print A5_value

End Sub

The final method that needs discussion is getCellRangeByPosition and the example below illustrates it’s use. It is equivalent in functionality to the previous example.

Sub getCellInfoByRange

Dim myTable as Object

‘get the first sheet of the spreadsheet doc

xSheet = ThisComponent.Sheets(0)

‘Grab array A3:A5

myTable = xSheet.getCellRangeByPosition(0,2,0,4)

A5_value = myTable.getCellByPosition(0,2).value

print A5_value

End Sub

Дата добавления: 2015-08-03