Ask Your Question
0

[Calc Macro] How to get cell by address?

asked 2016-06-12 12:06:38 +0100

Stephanium Lupis gravatar image

How to get cell object by a string address?

GetCellByAddress("$'Sheet.name.with.dots'.$G$9").setString("Hello World")

Function GetCellByAddress(Address As String)
    GetCellByAddress = ???
End Function
edit retag flag offensive close merge delete

2 Answers

Sort by » oldest newest most voted
1

answered 2016-06-12 13:19:10 +0100

pierre-yves samyn gravatar image

Hi

You can use the getCellRangeByName method of a sheet object. So, e.g. something like this:

oSheet = thiscomponent.sheets.getByName("Sheet.name.with.dots")
oCell = oSheet.getCellRangeByName("$G$9")
oCell.setString("Hello World")

Regards

edit flag offensive delete link more

Comments

How do I parse the address string to get the sheet name and cells names then? Is there any documentation on it's syntax or a built-in parser?

Stephanium Lupis gravatar imageStephanium Lupis ( 2016-06-12 13:49:25 +0100 )edit

You can get sheet names with thiscomponent.sheets.ElementNames. (array of strings).

You can also access a sheet by index, e.g. for first sheet:

oSheet = thiscomponent.sheets.getByIndex(0)

You will understand that I can not list here all the properties and methods of the various objects. A lot of Information and resources here.

pierre-yves samyn gravatar imagepierre-yves samyn ( 2016-06-12 14:53:13 +0100 )edit

I was asking about parsing Cell.AbsoluteName format/syntax string to it's parts. Because GetCellByAddress must accept absolute cell names as the input. I know how to get sheet names but it's not the question I'm asking.

Stephanium Lupis gravatar imageStephanium Lupis ( 2016-06-12 17:38:53 +0100 )edit

Use the BASIC function 'Split': Spli(AbsoluteName,".") returning an array of slices. If there unfortunately should be a way to (mis-)use strings containing dots as sheet nams (I never tried and will never do.), you may reconcatenate the parts except the last one using Join(). You also may use the BASIC Mid function/statement for the purpose or the Calc MID function via a function acces object ...

Lupp gravatar imageLupp ( 2016-06-12 19:36:18 +0100 )edit

Ok. Thank you for help. I hope I will get no other "surprises" except quoted sheet names while parsing absolute names.

Stephanium Lupis gravatar imageStephanium Lupis ( 2016-06-12 23:28:19 +0100 )edit

@Stephanium Lupis I did not understand your new request because my English is poor... and in your example you already had the sheet & cell names as constants.

That said the solution would rather be:

sDummy = split(oCell.absolutename, "'")
msgbox sDummy(1) & chr(13) & mid(sDummy(2), 2)

Regards

pierre-yves samyn gravatar imagepierre-yves samyn ( 2016-06-13 11:49:18 +0100 )edit

"$'Sheet.name.with.dots'.$G$9" was given just as an example of input for the GetCellByAddress function. Your code above will work for this particular example but will fail for an absolute name of cell located on a sheet that doesn't have dots in name. I need a common solution for all possible forms of absolute name.

absolute_name2 = "$Sheet4.$B$7"
sDummy = split(absolute_name2, "'")
msgbox sDummy(1) & chr(13) & mid(sDummy(2), 2)
Stephanium Lupis gravatar imageStephanium Lupis ( 2016-06-13 12:35:07 +0100 )edit

Just test (with the Mid function) if the string contains a quote. But I think it would be better if you give specific context of use of your function. I do not see concrete case we would have the cell object (and therefore its .AbsoluteName property) without being able to know the name of the sheet. Even if you work on the current selection, it is possible to "go up" the sheet by its index or e.g. thiscomponent.currentselection.spreadsheet.name (you also have to test if selection is a cell)

pierre-yves samyn gravatar imagepierre-yves samyn ( 2016-06-13 17:06:20 +0100 )edit
0

answered 2016-06-14 09:32:58 +0100

Lupp gravatar image

updated 2016-06-14 16:58:28 +0100

This is basically answering quite a different question that came up in the discussion concerning the first answer. I will copy this answer to a new question concerning its actual subject, if asked.

I think we can do it by a function as well usable from inside BASIC as in a Calc formula:

Function anlyseAbsoluteName(pAN)
REM Except for the one error case tested,
REM the function will return the sheet part 
REM and the range part as an array (1 To 2)
REM whether used in BASIC or in Calc.
REM If used in Calc for evaluation in array mode
REM the output will go to two consecutive cells of a row.
REM Indexing the result inside of a Calc formula is possible.
Dim theParts, theOutput (1 To 2) As String
If Mid(pAN,2,1) = "'" Then
    theParts = Split(pAN,"'.")
    theParts(0) = theParts(0) + "'"
Else
    theParts = Split(pAN,".")
EndIf
If NOT (UBound(theParts)=1) Then 
    anlyseAbsoluteName = "Not acceptable!"
    Exit Function
EndIf
theOutput(1) = theParts(0)
theOutput(2) = theParts(1)
anlyseAbsoluteName = theOutput
End Function

[Edit1] The slightly reworkd code below should be better usable, and accept also range addresses with sheet parts not made absolute ($ prefixed).

Function anlyseFullRangeName(pFNR)
REM A FullRangeName in the sense the term is used here
REM in, is consisting of a SheetName and a single RangeName
REM separated by a dot (full stop). 
REM The range named may also be a single cell.
REM Except the one error case tested,
REM the function will return the sheet part 
REM and the range part as an array (1 To 2)
REM whether used in BASIC or in Cac.
REM If used in Calc for evaluation in array mode
REM the output will go to two consecutive cells of a row.
REM Indexing the result inside of a Calc formula is possible.
Dim theParts, theOutput (1 To 2) As String
If (Mid(pFNR,1,2) = "$'") OR (Mid(pFNR,1,1) = "'") Then
    theParts = Split(pFNR,"'.")
    theParts(0) = theParts(0) + "'"
Else
    theParts = Split(pFNR,".")
EndIf
If NOT (UBound(theParts)=1) Then 
    anlyseFullRangeName = "Not acceptable!"
    Exit Function
EndIf
theOutput(1) = theParts(0)
theOutput(2) = theParts(1)
anlyseFullRangeName = theOutput
End Function

[/Edit1]

edit flag offensive delete link more
Login/Signup to Answer

Question Tools

1 follower

Stats

Asked: 2016-06-12 12:06:38 +0100

Seen: 1,570 times

Last updated: Jun 14 '16