How do I populate multiple cells from a macro function in Libre Calc from a csv string?
Sample function
Function GetCsv
Dim csvData As String
csvData = "column1,column2,column3" & Chr(10) & "1,2,3" & Chr(10) & "4,5,6" & Chr(10) & "7,8,9"
GetCsv = csvData
End Function
I'd like to call the function in cell A1 and in this example have A1:C4 populated with the data. What is the best way to achieve this?
Are you asking about splitting a text string into individual rectangular array elements? I would write it like this:
Function splitStr2Array(sSource As String, Optional sStrDelimiter As String, Optional sColDelimiter As String) As Variant
Dim aTemp As Variant
Dim aStr As Variant
Dim Result As Variant
Dim i As Long, j As Long, nStrLen As Long
If IsMissing(sStrDelimiter) Then sStrDelimiter = Chr(10)
If IsMissing(sColDelimiter) Then sColDelimiter = ","
On Error Resume Next
aTemp = Split(sSource, sStrDelimiter)
aStr = Split(aTemp(0), sColDelimiter)
nStrLen = UBound(aStr)
ReDim Result(0 To UBound(aTemp), 0 To nStrLen)
For i = 0 To UBound(aTemp)
aStr = Split(aTemp(i), sColDelimiter)
ReDim Preserve aStr(0 To nStrLen)
For j = 0 To nStrLen
Result(i, j) = aStr(j)
Next j
Next i
splitStr2Array = Result
End Function
and call it from cell with formula like
=SPLITSTR2ARRAY("column1,column2,column3" & CHAR(10) & "1,2,3" & CHAR(10) & "4,5,6" & CHAR(10) & "7,8,9")}
