[Solved] SetDataArray setting array with 1 column

[sokolowitzky]

Hi,

I'm trying to split some string into a range. SetDataArray would help me do this better.
But apparently this function does not work with a one line array. It gives me "Object variable not set." error. I feel like this is because the array is not 2d. But I can't figure out how to turn this into an array with 2 columns

Here below you can find a code I wrote.

Code: Select all

SUB Test_2_Array_to_String
otext = "splitted"

dim da(len(otext))

for ii = 0 to LEN(OTEXT) - 1
da(ii) = mid(otext, ii + 1, 1)
next
msgbox """" & join(da(), chr(10)) & """"
for yy = 0 to ubound(da()) -1
next
oRange = ThisComponent.Sheets(3).getCellRangeByPosition(0,0,0,len(otext)-1)
oRange.setdataarray(da())


END SUB

[Villeroy]

menu:Data>Text to columns... does the trick quite well and easily.

[sokolowitzky]

Actually I use this splitting macro as a part of another macro. So it'd be helpful if I could do this without "text to columns".

[RoryOF]

There is discussion on setDataArray in this topic
viewtopic.php?f=20&t=90995
which may be helpful.

Do I recollect correctly that the term setDataArray is case sensitive?

[Villeroy]

Do I recollect correctly that the term setDataArray is case sensitive?

With a little help from my friends:

Code: Select all

REM  *****  BASIC  *****
Sub test_Splitter()
REM calling helper functions: getOffsetRange, getRangeFromAddress, bas_PushArray
Const cSplit = "/"
src = ThisComponent.getCurrentSelection()
tgt = getOffsetRange(src,0,0,0,src.Columns.getCount()+1)
da() = src.getDataArray()
rows = uBound(da())
cols = uBound(da(0))
for i = 0 to rows
	row = da(i)
	s() = split(row(0), cSplit)
	row(0) = s(0)
	bas_PushArray(row, s(1))
	da(i) = row
next i
tgt.setDataArray(da())
End Sub

Function getOffsetRange(oRg, nRowOffset&, nColOffset&, nRowResize&, nColResize&)
Dim addr
'calls: getRangeByAddress
	addr = oRg.getRangeAddress()
	addr.StartRow = addr.StartRow + nRowOffset
	addr.EndRow = addr.EndRow + nRowOffset
	addr.StartColumn = addr.StartColumn + nColOffset
	addr.EndColumn = addr.EndColumn + nColOffset
	if nRowResize > 0 then addr.EndRow = addr.StartRow + nRowResize -1
	if nColResize > 0 then addr.EndColumn = addr.StartColumn + nColResize -1
	getOffsetRange = getRangeByAddress(oRg.getSpreadsheet(), addr)
End Function

'pass a spreadsheet-document, sheet or range  together with a c.s.s.table.CellRangeAddress
'return empty if oAddr out of bounds or wrong obj
Function getRangeByAddress(obj, oAddr as com.sun.star.table.CellRangeAddress)
on error goto nullErr:
Dim oSheet
	If obj.supportsService("com.sun.star.sheet.SpreadsheetDocument") then
		REM use the sheet specified by given address
		oSheet = obj.getSheets.getByIndex(oAddr.Sheet)
	else
		REM use given object (range/sheet) as parent range
		oSheet = obj
	endif
	getRangeByAddress = oSheet.getCellRangeByPosition(oAddr.StartColumn,oAddr.StartRow,oAddr.EndColumn,oAddr.EndRow)
exit function
nullErr:
	getRangeByAddress = Null
End Function

'very simple routine appending some element to an array which can be undimensioned (LBound > UBound)
Sub bas_PushArray(xArray(),vNextElement)
Dim iUB%,iLB%
	iLB = lBound(xArray())
	iUB = uBound(xArray())
	If iLB > iUB then
		iUB = iLB
		redim xArray(iLB To iUB)
	else
		iUB = iUB +1
		redim preserve xArray(iLB To iUB)
	endif
	xArray(iUB) = vNextElement
End Sub

[JeJe]

Code: Select all

SUB Test_2_Array_to_String
otext = "splitted"
dim da(len(otext)-1)
for ii = 0 to LEN(OTEXT) - 1
da(ii) =array( mid(otext, ii + 1, 1))
next
oRange = ThisComponent.Sheets(3).getCellRangeByPosition(0,0,0,len(otext)-1)
oRange.setdataarray(da)
END SUB

[sokolowitzky]

JeJe & Villeroy, RoryOF thank you all for your help and suggestions

[karolus]

Hallo

With python its litterally "easy as shit":

Code: Select all

doc = XSCRIPTCONTEXT.getDocument()
sel = doc.CurrentSelection
sheet = sel.Spreadsheet
cursor =sheet.createCursorByRange(sel)

data = [list("some Text")]

# for AOO:
#data= tuple((tuple("some Text"),))

cursor.collapseToSize( len(data[0]) , len(data) )
cursor.setDataArray(data)