[Solved] Macro to copy data to active cell from cell above

Creating a macro - Writing a Script - Using the API (OpenOffice Basic, Python, BeanShell, JavaScript)
Post Reply
hubertT
Posts: 5
Joined: Fri Apr 07, 2017 1:24 pm

[Solved] Macro to copy data to active cell from cell above

Post by hubertT »

Good afternoon (from UK). While entering text, date and numbers data ror-by-row on spreadsheet, it will be helpful to be able to copy into active/cursor cell, contents of cell immediately to cursor-cell. Tips and ideas will be welcome. I use AOO Calc version 4.1.3 on Windows 10 laptop.
Last edited by Hagar Delest on Fri Apr 07, 2017 9:29 pm, edited 1 time in total.
Reason: tagged [Solved].
OpenOffice 4.1.3 on Windows 10
UnklDonald418
Volunteer
Posts: 1544
Joined: Wed Jun 24, 2015 12:56 am
Location: Colorado, USA

Re: Macro to copy data to active cell from cell above

Post by UnklDonald418 »

The easiest way would be to record a macro using copy and paste.
If your problem has been solved, please edit this topic's initial post and add "[Solved]" to the beginning of the subject line
Apache OpenOffice 4.1.14 & LibreOffice 7.6.2.1 (x86_64) - Windows 10 Professional- Windows 11
User avatar
RoryOF
Moderator
Posts: 34586
Joined: Sat Jan 31, 2009 9:30 pm
Location: Ireland

Re: Macro to copy data to active cell from cell above

Post by RoryOF »

Would pull down insertion not work as effectively?
Apache OpenOffice 4.1.15 on Xubuntu 22.04.4 LTS
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Macro to copy data to active cell from cell above

Post by Villeroy »

Code: Select all

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 
Last edited by Villeroy on Fri Apr 07, 2017 6:22 pm, edited 1 time in total.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
hubertT
Posts: 5
Joined: Fri Apr 07, 2017 1:24 pm

Re: Macro to copy data to active cell from cell above

Post by hubertT »

Thank you UnklDonald418 and Villeroy; and RoryOF whose point looks to be exemplified by Villeroy's macro. I will try the options then report-back.
OpenOffice 4.1.3 on Windows 10
User avatar
RoryOF
Moderator
Posts: 34586
Joined: Sat Jan 31, 2009 9:30 pm
Location: Ireland

Re: Macro to copy data to active cell from cell above

Post by RoryOF »

My point is even simpler than Villeroy's macro: if the intention is to copy the previous data, then edit in the changes, it is simple to select the previous cell and either copy/paste or pull it down to the next cell, then edit the changes.
Apache OpenOffice 4.1.15 on Xubuntu 22.04.4 LTS
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Macro to copy data to active cell from cell above

Post by Villeroy »

Preserving any formula:

Code: Select all

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
with the same helper functions as above
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
hubertT
Posts: 5
Joined: Fri Apr 07, 2017 1:24 pm

Re: Macro to copy data to active cell from cell above

Post by hubertT »

Further thanks, RoryOF and Villeroy.

Recorded macro worked, UnklDonald418, but ended in edit mode or with cell highlighted. Villeroy's corrected macro appears to do 'cleanest' and best. UnklDonald418's and Villeroy's pullDown options are ruled out as they pulled-down formatting.

Here's the Villeroy macro which I will use:

Code: Select all

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
Thank you again. I will try to setup a 'function key combination' to run it.

Please consider the following further points and say if I need close this topic s 'solved' and start new topics to address them, or, if the additional points can be dealt with here under this post:

Further points for the macro:
(1) Run the macro as it is, here.
(2) Run and directly into cell-edit mode.
(3) Run and move directly to next cell to right.
(4) Run and then move back-to-left (a number of columns -presently from column W back to column J), and down one row, ready to start next row's data entries
OpenOffice 4.1.3 on Windows 10
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Macro to copy data to active cell from cell above

Post by Villeroy »

I keep the generic helper functions in a global library named "Calc" since they are useful with many other macros.
getActiveCell is extremely useful because there is always one active cell even if the user selected a range, multiple ranges or some kind of shape. As long as ThisComponent points to a spreadsheet document, I never got any error from this function call. There is no built-in API method to access this object directly. In Excel-VBA the active cell is one of the most prominent objects.
getOffsetRange works like spreadsheet function OFFSET or VBA oRange.Offset(x,y).Resize(m,n). I used to use it in dozends of macros.
getRangeByAddress(obj, addr) is not particularly useful but more handy than obj.getCellRangeByPosition(addr.StartColumn, addr.StartRow,addr.EndColumn,addr.EndRow)

Code: Select all

Sub SomeMacro()

GlobalScope.BasicLibraries.loadLibrary("Calc")

ac = getActiveCell(ThisComponent.getCurrentController())
tc = getOffsetRange(ac, -1, 0, 0, 0)

End Sub
If the calling macro and the "Calc" library are embedded in a document, leave out "GlobalScope.".
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
hubertT
Posts: 5
Joined: Fri Apr 07, 2017 1:24 pm

Re: [Solved] Macro to copy data to active cell from cell abo

Post by hubertT »

I note the further 'SomeMacro()' routine, Villeroy, but unfortunately, I do don't have sufficient understanding the lead you intend it to give me; but I will read & reread it in conjunction with your copy-macro that solved the originating topic I posted.

In the meantime, 'danke sehr'.
OpenOffice 4.1.3 on Windows 10
Post Reply