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 FunctionHi
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
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?
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.
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.
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 …
Ok. Thank you for help. I hope I will get no other “surprises” except quoted sheet names while parsing absolute names.
@StephaniumLupis 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
“$‘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)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)
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]