Get the formatted text of a range/cell object

Question

I try to concatenate the formatted contents of some cells with a formula.
As I cannot see a way to solve it with a pure formula I add some basic code.

But I cannot figure out how to access the formatted text value out of the single cells.
It seems that oCell isn't a cell object, instead it is only the cell content.

How do I could change this, so I can use something like oCell.Text or oCell.String ...

Function StringSumme(oCellRange )
    dim result as String
    dim nRow as Integer

    result = ""
   For nRow = LBound( oCellRange, 1) To UBound( oCellRange, 1 )
        For nCol = LBound( oCellRange, 2) To UBound( oCellRange, 2 )
            oCell=oCellRange(nRow,1)
            result = result + oCell
        Next 
    Next 
    StringSumme = result 
End Function

In Excel this one works

Function StringSumme(bezug As Range) As String
    Dim txt As String
    Dim ce As Range

    txt = ""
    For Each ce In bezug.Cells
        txt = txt & ce.Text
    Next
    StringSumme = txt
End Function

I'm looking for an openoffice solution, my excel solution works — jeb, Feb 23 '12 at 12:21
jeb, I am not sure about your question. You code OO code works perfectly without the need to add anything after oCell — Siddharth Rout, Feb 24 '12 at 05:33
But it only copies the text without the formatting. I have cells with format "0,", with the OO code I lose the `,` — jeb, Feb 24 '12 at 06:16

Siddharth Rout · Accepted Answer · 2012-06-09T21:53:33.803

1

jeb

I think I understand your question now.

When you type this

Function StringSumme(oCellRange)

oCellRange is not a range. It is an array which is being passed. And hence oCell isn't a cell object, instead it is only the cell content as you right guessed.

You might want to change it to something like

oCell = Sheet.getCellByPosition(X, Y)

and then use oCell.Value

Interesting Read

http://wiki.services.openoffice.org/wiki/Documentation/BASIC_Guide/Cells_and_Ranges

edited Jun 09 '12 at 21:53

answered Feb 24 '12 at 06:49

Siddharth Rout

147,039
17
206
250

Thx, but this can be only a workaround, as I want to use formulas like in excel `=StringSumme(A1:A10). I saw the page and many other samples, but I can't find any with a desciption of using ranges with (formula)functions – jeb Feb 24 '12 at 08:37
1

@jeb: I could be wrong but my research has shown that you cannot pass range in a function in 'Calc'. Arguments passed to a macro from 'Calc' are always values. As per this link (http://www.linuxtopia.org/online_books/office_guides/openoffice_3_calc_user_guide/openoffice_calc_Passing_arguments_to_a_macro.html), you can however pass the range as a string and then parse it. – Siddharth Rout Feb 27 '12 at 11:59
Ok, that explains why I can't find any reference how to access the range objects. Now I will switch back to Excel – jeb Feb 27 '12 at 13:44
@jeb: Yup. That seems like the only option if you want to pass it as a range. Else you will have to use the option that I gave above :( – Siddharth Rout Feb 27 '12 at 14:11

Get the formatted text of a range/cell object

1 Answers1