Make a range to CVS string

[Gundrada]

I’m trying to create a function that searches a range of cells,
and returns cells with text as a CSV string.

Blockquote
Function SearchAndReturnCSV(oRange As Range) As String
’ Declare variables
Dim oCell As Range
Dim sCSV As String

' Loop through each cell in the range
For Each oCell In oRange
    ' If the cell has text, add it to the CSV string
    If Len(oCell.Text) > 0 Then
        sCSV = sCSV & oCell.Text & ","
    End If
Next

' Return the CSV string
SearchAndReturnCSV = sCSV

End Function

Blockquote

any help will be appreciated.

[Villeroy]

da() = oRange.getDataArray()
for each r in da()
    sCSV = join(r(), ",")
    print sCSV
next

[Gundrada]

HI Villeroy
Do you mind posting the whole function in full,
with your solution, as I can’t get it to work.

Function SearchAndReturnCSV(oRange As Range) As String
’ Declare variables
Dim oCell As Range
Dim sCSV As String

' Loop through each cell in the range
For Each oCell In oRange
    ' If the cell has text, add it to the CSV string
    If Len(oCell.Text) > 0 Then
        sCSV = sCSV & oCell.Text & ","
    End If
Next

' Return the CSV string
SearchAndReturnCSV = sCSV

End Function

thanks for your prompt reply

[Lupp]

Did you mean Join where you wrote split?

[Villeroy]

Yes, indeed

[Lupp]

@Gundrada

' If the cell has text, add it to the CSV string

1. To get usable csv you need to also create the columns completely which may contain empty cells in some places.
2. To join an actually rectangular range into something looking roughly like a single row of a csv file will mostly not make sense. If you think it does in your special case, you should explain.

[Lupp]

Why do you think to need user code. Since V4.4 LibreOffice Calc has a well working TEXTJOIN() function.
See attached example:
disask86264JoinCSVlike.ods (19.7 KB)
If you create user code for Calc, you shouldn’t start with VBA.

[erAck]

Note that anything simply joining cell content with a , comma separator will not produce valid CSV if a cell contains a comma or a newline or a double quote character, in which case the content needs to be enclosed in double quotes and any double quote contained needs to be escaped by doubling it.
See RFC 4180: Common Format and MIME Type for Comma-Separated Values (CSV) Files .

[karolus]

@erAck

Note that anything simply joining cell content with a , comma separator will not produce valid CSV ……

+1 for @erAck
additional:
even if you know what youre doing, you should use native calc-csv-export or in some special corner cases https://docs.python.org/3/library/csv.html?highlight=csv

[Gundrada]

To simplify the problem
I would like to create a user function in basic that multiplies the numbers in a range excluding empty cells

the solution must be in LibreOffice Basic

[mariosv]

=PRODUCT(B3:B10) => 30
(English function name),
empty cells are excluded.

[karolus]

@Gundrada

the solution must be in LibreOffice Basic

@mariosv : you failed, the solution MUST BE IN BASIC

[Lupp]

@karolus

@mariosv : you failed, the solution MUST BE IN BASIC

@karolus is right. I managed, however, to meet this requirement:

Function myProductFunction(pRangeData) As Double
fa = CreateUnoService("com.sun.star.sheet.FunctionAccess")
myProductFunction = fa.callFunction("PRODUCT", Array(pRangeData))
End Function

Seriously now. This looks like many examples from the VBA world I’ve seen over the years: Bloated emptiness.

Of course, there may be reasons to want a MY_PRODUCT() function that has certain differences compared to PRODUCT(). For example, I may want to include contents for which VALUE() returns a valid result.
In no case is the conversion to a comma-separated list an appropriate step.

See XY problem - Wikipedia

[mikekaganski]

@Gundrada

create a function that searches a range of cells, and returns cells with text as a CSV string

@Gundrada

To simplify the problem
I would like to create a user function in basic that multiplies the numbers in a range excluding empty cells

Is there anyone who sees the latter being a simplification of the former?