Sub pullDownData()
ac = getActiveCell(ThisComponent.getCurrentController())
tc = getOffsetRange(ac, -1, 0, 0, 0)
if isObject(tc) then ac.setDataArray(tc.getDataArray())
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
Function getActiveCell(oView)
Dim as1(), lSheet&,lCol&,lRow$, sDum as String,bErr as Boolean
as1() = Split(oView.ViewData, ";")
lSheet = CLng(as1(1))
sDum = as1(lSheet +3)
as1() = Split(sDum, "/")
on error goto errSlash
lCol = CLng(as1(0))
lRow = CLng(as1(1))
on error goto 0
getActiveCell = oView.Model.getSheets.getByIndex(lSheet).getcellByPosition(lCol,lRow)
exit Function
errSlash:
if NOT(bErr) then
bErr = True
as1() = Split(sDum, "+")
resume
endif
End Function
Edit: forgot to paste function getRangeByAddress |
Sub PullDownContent()
ac = getActiveCell(ThisComponent.getCurrentController())
rg = getOffsetRange(ac, -1, 0, 2, 1)
if isObject(rg) then rg.fillSeries(com.sun.star.sheet.FillDirection.TO_BOTTOM,com.sun.star.sheet.FillMode.SIMPLE,0,0,0)
End Sub
Sub pullDownData()
ac = getActiveCell(ThisComponent.getCurrentController())
tc = getOffsetRange(ac, -1, 0, 0, 0)
if isObject(tc) then ac.setDataArray(tc.getDataArray())
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
Function getActiveCell(oView)
Dim as1(), lSheet&,lCol&,lRow$, sDum as String,bErr as Boolean
as1() = Split(oView.ViewData, ";")
lSheet = CLng(as1(1))
sDum = as1(lSheet +3)
as1() = Split(sDum, "/")
on error goto errSlash
lCol = CLng(as1(0))
lRow = CLng(as1(1))
on error goto 0
getActiveCell = oView.Model.getSheets.getByIndex(lSheet).getcellByPosition(lCol,lRow)
exit Function
errSlash:
if NOT(bErr) then
bErr = True
as1() = Split(sDum, "+")
resume
endif
End Function
Sub SomeMacro()
GlobalScope.BasicLibraries.loadLibrary("Calc")
ac = getActiveCell(ThisComponent.getCurrentController())
tc = getOffsetRange(ac, -1, 0, 0, 0)
End Sub
Users browsing this forum: No registered users and 6 guests