« Charting: Pie charts | Main | Autocorrect »
November 24, 2004
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
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
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
For more information.. http://api.openoffice.org/docs/common/ref/com/sun/star/table/XCellRange.html
Posted by Dave at November 24, 2004 03:09 PM
Comments
So how could one select the entire filled range of cells starting from cell A1 and going downwards using macro code?
Posted by: jkpieterse at January 3, 2005 02:13 PM