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

Creating a macro - Writing a Script - Using the API

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

Postby hubertT » Fri Apr 07, 2017 1:54 pm

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
hubertT
 
Posts: 5
Joined: Fri Apr 07, 2017 1:24 pm

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

Postby UnklDonald418 » Fri Apr 07, 2017 4:10 pm

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 subject line
Apache OpenOffice 4.1.3 - Windows 10 Professional
UnklDonald418
Volunteer
 
Posts: 452
Joined: Wed Jun 24, 2015 12:56 am
Location: Colorado, USA

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

Postby RoryOF » Fri Apr 07, 2017 5:03 pm

Would pull down insertion not work as effectively?
Apache OpenOffice 4.1.3 on Xubuntu 16.04 (mostly 64 bit version) and infrequently on Win2K/XP
14 October 2016 was Pooh's 90th birthday
User avatar
RoryOF
Moderator
 
Posts: 24180
Joined: Sat Jan 31, 2009 9:30 pm
Location: Ireland

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

Postby Villeroy » Fri Apr 07, 2017 5:38 pm

Code: Select all   Expand viewCollapse view
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 16.04, OpenOffice 4.x & LibreOffice 5.x
User avatar
Villeroy
Volunteer
 
Posts: 23733
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

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

Postby hubertT » Fri Apr 07, 2017 5:57 pm

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
hubertT
 
Posts: 5
Joined: Fri Apr 07, 2017 1:24 pm

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

Postby RoryOF » Fri Apr 07, 2017 6:10 pm

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.3 on Xubuntu 16.04 (mostly 64 bit version) and infrequently on Win2K/XP
14 October 2016 was Pooh's 90th birthday
User avatar
RoryOF
Moderator
 
Posts: 24180
Joined: Sat Jan 31, 2009 9:30 pm
Location: Ireland

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

Postby Villeroy » Fri Apr 07, 2017 6:56 pm

Preserving any formula:
Code: Select all   Expand viewCollapse view
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 16.04, OpenOffice 4.x & LibreOffice 5.x
User avatar
Villeroy
Volunteer
 
Posts: 23733
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

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

Postby hubertT » Fri Apr 07, 2017 8:36 pm

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   Expand viewCollapse view
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
hubertT
 
Posts: 5
Joined: Fri Apr 07, 2017 1:24 pm

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

Postby Villeroy » Fri Apr 07, 2017 9:10 pm

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   Expand viewCollapse view
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 16.04, OpenOffice 4.x & LibreOffice 5.x
User avatar
Villeroy
Volunteer
 
Posts: 23733
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

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

Postby hubertT » Fri Apr 07, 2017 10:34 pm

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
hubertT
 
Posts: 5
Joined: Fri Apr 07, 2017 1:24 pm


Return to Macros and UNO API

Who is online

Users browsing this forum: No registered users and 8 guests