[Solved] Copying data from one sheet to another using macro

Creating a macro - Writing a Script - Using the API (OpenOffice Basic, Python, BeanShell, JavaScript)
Post Reply
jeevanthara
Posts: 25
Joined: Tue Jul 30, 2019 12:31 pm

[Solved] Copying data from one sheet to another using macro

Post by jeevanthara »

Hi all. I have recorded a macro which copy data from one sheet to another. Issue I am facing is since I have recorded this macro it automatically copy data to the adjacent sheet. If I change the order of sheet data gets messed up . I want to copy the data to a specified sheet say "data". Is it possible.
Thanks in advance.

Code: Select all

sub Main
rem ----------------------------------------------------------------------
rem define variables
dim document   as object
dim dispatcher as object
rem ----------------------------------------------------------------------
rem get access to the document
document   = ThisComponent.CurrentController.Frame
dispatcher = createUnoService("com.sun.star.frame.DispatchHelper")

rem ----------------------------------------------------------------------
dim args1(0) as new com.sun.star.beans.PropertyValue
args1(0).Name = "ToPoint"
args1(0).Value = "$C$2:$C$12"

dispatcher.executeDispatch(document, ".uno:GoToCell", "", 0, args1())

rem ----------------------------------------------------------------------
dispatcher.executeDispatch(document, ".uno:Copy", "", 0, Array())

rem ----------------------------------------------------------------------
dim args3(0) as new com.sun.star.beans.PropertyValue
args3(0).Name = "Nr"
args3(0).Value = 18

dispatcher.executeDispatch(document, ".uno:JumpToTable", "", 0, args3())

rem ----------------------------------------------------------------------
dispatcher.executeDispatch(document, ".uno:InsertRows", "", 0, Array())

rem ----------------------------------------------------------------------
dim args5(0) as new com.sun.star.beans.PropertyValue
args5(0).Name = "ToPoint"
args5(0).Value = "$C$6"

dispatcher.executeDispatch(document, ".uno:GoToCell", "", 0, args5())

rem ----------------------------------------------------------------------
dim args6(0) as new com.sun.star.beans.PropertyValue
args6(0).Name = "ToPoint"
args6(0).Value = "$A$3:$K$3"

dispatcher.executeDispatch(document, ".uno:GoToCell", "", 0, args6())

rem ----------------------------------------------------------------------
dim args7(5) as new com.sun.star.beans.PropertyValue
args7(0).Name = "Flags"
args7(0).Value = "SVD"
args7(1).Name = "FormulaCommand"
args7(1).Value = 0
args7(2).Name = "SkipEmptyCells"
args7(2).Value = false
args7(3).Name = "Transpose"
args7(3).Value = true
args7(4).Name = "AsLink"
args7(4).Value = false
args7(5).Name = "MoveMode"
args7(5).Value = 4

dispatcher.executeDispatch(document, ".uno:InsertContents", "", 0, args7())

rem ----------------------------------------------------------------------
dim args8(0) as new com.sun.star.beans.PropertyValue
args8(0).Name = "Nr"
args8(0).Value = 17

dispatcher.executeDispatch(document, ".uno:JumpToTable", "", 0, args8())

rem ----------------------------------------------------------------------
dim args9(0) as new com.sun.star.beans.PropertyValue
args9(0).Name = "ToPoint"
args9(0).Value = "$C$2"

dispatcher.executeDispatch(document, ".uno:GoToCell", "", 0, args9())

rem ----------------------------------------------------------------------
dim args10(0) as new com.sun.star.beans.PropertyValue
args10(0).Name = "ToPoint"
args10(0).Value = "$C$2:$C$10"

dispatcher.executeDispatch(document, ".uno:GoToCell", "", 0, args10())

rem ----------------------------------------------------------------------
dim args11(0) as new com.sun.star.beans.PropertyValue
args11(0).Name = "Flags"
args11(0).Value = "SVDFN"

dispatcher.executeDispatch(document, ".uno:Delete", "", 0, args11())


end sub
Last edited by jeevanthara on Mon Jun 01, 2020 7:27 am, edited 1 time in total.
Open Office 4.1.6

Ubuntu 12.1.14
User avatar
JohnSUN-Pensioner
Volunteer
Posts: 876
Joined: Fri Jan 14, 2011 1:21 pm
Location: Kyiv, Ukraine

Re: Copying data from one sheet to another using macro

Post by JohnSUN-Pensioner »

Yes, of course, it is possible. Andrew Pitonyak described 3 methods in chapter 5.23. of this book (PDF-version) - just to read description, copy code and run it
I may not have a lot to give but what I got I'll give to you...
Apache OpenOffice 4.1.5, LibreOffice 6.4.4.2 (x64) on Windows 7
If you think that I did not answer your question, make allowances for my imperfect English
jeevanthara
Posts: 25
Joined: Tue Jul 30, 2019 12:31 pm

Re: Copying data from one sheet to another using macro

Post by jeevanthara »

I have read the ebook.

Code: Select all

Sub CopySpreadsheetRange
  REM Get sheet 1, the original, and 2, which will contain the copy.
  oSheet1 = ThisComponent.Sheets.getByIndex(0)
  oSheet2 = ThisComponent.Sheets.getByIndex(1)

  REM Get the range to copy and the rang to copy to.
  oRangeOrg = oSheet1.getCellRangeByName("A1:C10").RangeAddress
  oRangeCpy = oSheet2.getCellRangeByName("A1:C10").RangeAddress

  REM The insert position
  oCellCpy = oSheet2.getCellByPosition(oRangeCpy.StartColumn,_
    oRangeCpy.StartRow).CellAddress

  REM Do the copy
  oSheet1.CopyRange(oCellCpy, oRangeOrg)
End Sub



The code works fine but issue I am facing is I need copy data from a column and paste it into a row. Here code copy data from column to column
Open Office 4.1.6

Ubuntu 12.1.14
User avatar
Zizi64
Volunteer
Posts: 11353
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: Copying data from one sheet to another using macro

Post by Zizi64 »

The code works fine but issue I am facing is I need copy data from a column and paste it into a row.
A possible soution:

Get the data one-by-one from the source cell range (FOR... cycle in the "vertical direction"), and then put the data into the target cell range into "horizontal direction". You can use same FOR... cycle for the "get"/"put" task.

How to get an individual cell by its position:
https://wiki.openoffice.org/wiki/Docume ... s_directly

Just use the x/y coordinates (the Vertical/Horizontal NUMERIC coordonates) of the cells to convert the orientation of the copied cell range. You can copy the Cell Content and/or the Cell Style with this method.
Last edited by Zizi64 on Sun May 31, 2020 8:24 am, edited 1 time in total.
Tibor Kovacs, Hungary; LO7.5.8 /Win7-10 x64Prof.
PortableApps/winPenPack: LO3.3.0-7.6.2;AOO4.1.14
Please, edit the initial post in the topic: add the word [Solved] at the beginning of the subject line - if your problem has been solved.
User avatar
Zizi64
Volunteer
Posts: 11353
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: Copying data from one sheet to another using macro

Post by Zizi64 »

Here is a topic with a very similar task:
viewtopic.php?f=5&t=89993
Tibor Kovacs, Hungary; LO7.5.8 /Win7-10 x64Prof.
PortableApps/winPenPack: LO3.3.0-7.6.2;AOO4.1.14
Please, edit the initial post in the topic: add the word [Solved] at the beginning of the subject line - if your problem has been solved.
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Copying data from one sheet to another using macro

Post by Villeroy »

Record a copy&paste-special action with option "Transpose"
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
User avatar
Zizi64
Volunteer
Posts: 11353
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: Copying data from one sheet to another using macro

Post by Zizi64 »

Record a copy&paste-special action with option "Transpose"
O.P. has uploaded such macro code in the first post. But the rest part of the problem is related to the reordering sheets:
If I change the order of sheet data gets messed up .
O.P must combine the Recorded an Written parts of the macro code, or he must WRITE all parts of the code - instead of the recording.


(Get the sheets by name. There are many sample codes for this task.)
Tibor Kovacs, Hungary; LO7.5.8 /Win7-10 x64Prof.
PortableApps/winPenPack: LO3.3.0-7.6.2;AOO4.1.14
Please, edit the initial post in the topic: add the word [Solved] at the beginning of the subject line - if your problem has been solved.
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Copying data from one sheet to another using macro

Post by Villeroy »

When you record a macro, you can access a sheet by its name by entering the sheet name into the name box. Using the navigator, you jump to a fixed sheet index.

Anyway, this may help to transpose a data array:

Code: Select all

Function TransposeDataArray(a())
REM take a data array, give a data array
c = uBound(a)
r = uBound(a(0))
Dim y(r)
Dim x(c)
for i = 0 to r
  Redim x(c)
  for j = 0 to c
    x(j) = a(j)(i)
  next j
  y(i) = x()
next i
TransposeDataArray = y()
End Function
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
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Copying data from one sheet to another using macro

Post by Villeroy »

Villeroy wrote:When you record a macro, you can access a sheet by its name by entering the sheet name into the name box. Using the navigator, you jump to a fixed sheet index.

Anyway, this may help to transpose a data array:

Code: Select all

Function TransposeDataArray(a())
REM take a data array, give a data array
c = uBound(a)
r = uBound(a(0))
Dim y(r)
Dim x(c)
for i = 0 to r
  Redim x(c)
  for j = 0 to c
    x(j) = a(j)(i)
  next j
  y(i) = x()
next i
TransposeDataArray = y()
End Function
This clears a sheet named "Target" and dumps the transposed data from the current range selection to that empty sheet.

Code: Select all

a() = ThisComponent.CurrentSelection.getDataArray()
sh = ThisComponent.Sheets.getByName("Target")
with com.sun.star.sheet.CellFlags
	nFlags = .VALUE + .DATETIME + .STRING + .FORMULA
End with
sh.clearContents(nFlags)
b() = TransposeDataArray(a())
rg = sh.getCellRangeByPosition(0, 0, uBound(b(0)), uBound(b()))
rg.setDataArray(b())
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
jeevanthara
Posts: 25
Joined: Tue Jul 30, 2019 12:31 pm

Re: Copying data from one sheet to another using macro

Post by jeevanthara »

I have recorded the macro just as you said. View-> Navigator-> Selected the sheet where I want to paste the transposed data.

But when I change the order of sheet data gets copied to wrong sheet.

here is the code of macro I recorded

Code: Select all

REM  *****  BASIC  *****
sub Main
rem ----------------------------------------------------------------------
rem define variables
dim document   as object
dim dispatcher as object
rem ----------------------------------------------------------------------
rem get access to the document
document   = ThisComponent.CurrentController.Frame
dispatcher = createUnoService("com.sun.star.frame.DispatchHelper")

rem ----------------------------------------------------------------------
dim args1(0) as new com.sun.star.beans.PropertyValue
args1(0).Name = "ToPoint"
args1(0).Value = "$C$1:$C$12"

dispatcher.executeDispatch(document, ".uno:GoToCell", "", 0, args1())

rem ----------------------------------------------------------------------
dispatcher.executeDispatch(document, ".uno:Copy", "", 0, Array())

rem ----------------------------------------------------------------------
rem dispatcher.executeDispatch(document, ".uno:GoToCell", "", 0, Array())

rem ----------------------------------------------------------------------
dispatcher.executeDispatch(document, ".uno:InsertRows", "", 0, Array())

rem ----------------------------------------------------------------------
dim args5(0) as new com.sun.star.beans.PropertyValue
args5(0).Name = "ToPoint"
args5(0).Value = "$A$3"

dispatcher.executeDispatch(document, ".uno:GoToCell", "", 0, args5())

rem ----------------------------------------------------------------------
dim args6(0) as new com.sun.star.beans.PropertyValue
args6(0).Name = "ToPoint"
args6(0).Value = "$A$3:$L$3"

dispatcher.executeDispatch(document, ".uno:GoToCell", "", 0, args6())

rem ----------------------------------------------------------------------
dim args7(5) as new com.sun.star.beans.PropertyValue
args7(0).Name = "Flags"
args7(0).Value = "SVD"
args7(1).Name = "FormulaCommand"
args7(1).Value = 0
args7(2).Name = "SkipEmptyCells"
args7(2).Value = false
args7(3).Name = "Transpose"
args7(3).Value = true
args7(4).Name = "AsLink"
args7(4).Value = false
args7(5).Name = "MoveMode"
args7(5).Value = 4

dispatcher.executeDispatch(document, ".uno:InsertContents", "", 0, args7())


end sub
Open Office 4.1.6

Ubuntu 12.1.14
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Copying data from one sheet to another using macro

Post by Villeroy »

Add a sheet name to the value of named value "ToPoint", for instance:

Code: Select all

dim args5(0) as new com.sun.star.beans.PropertyValue
args5(0).Name = "ToPoint"
args5(0).Value = "Sheet2.$A$3"
A popular macro to merge sheets of a document into one list: viewtopic.php?f=21&t=93099

And of course, everything might be far easier if you would collect your data in a database table. A spreadsheet is a poor database surrogate at best.
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
jeevanthara
Posts: 25
Joined: Tue Jul 30, 2019 12:31 pm

Re: Copying data from one sheet to another using macro

Post by jeevanthara »

I have added the sheet name to the code. But still if the order changes data get pasted into same sheet. My primary sheet name is "sum". and data is to be transferred to sheet named "data"

Code: Select all

REM  *****  BASIC  *****
sub Main
rem ----------------------------------------------------------------------
rem define variables
dim document   as object
dim dispatcher as object
rem ----------------------------------------------------------------------
rem get access to the document
document   = ThisComponent.CurrentController.Frame
dispatcher = createUnoService("com.sun.star.frame.DispatchHelper")

rem ----------------------------------------------------------------------
dim args1(0) as new com.sun.star.beans.PropertyValue
args1(0).Name = "ToPoint"
args1(0).Value = "sum$C$1:$C$12"

dispatcher.executeDispatch(document, ".uno:GoToCell", "", 0, args1())

rem ----------------------------------------------------------------------
dispatcher.executeDispatch(document, ".uno:Copy", "", 0, Array())

rem ----------------------------------------------------------------------
dim args3(0) as new com.sun.star.beans.PropertyValue
args3(0).Name = "Navigator"
args3(0).Value = true

dispatcher.executeDispatch(document, ".uno:Navigator", "", 0, args3())

rem ----------------------------------------------------------------------
dim args4(0) as new com.sun.star.beans.PropertyValue
args4(0).Name = "Nr"
args4(0).Value = 24

dispatcher.executeDispatch(document, ".uno:JumpToTable", "", 0, args4())

rem ----------------------------------------------------------------------
dispatcher.executeDispatch(document, ".uno:InsertRows", "", 0, Array())

rem ----------------------------------------------------------------------
dim args6(0) as new com.sun.star.beans.PropertyValue
args6(0).Name = "ToPoint"
args6(0).Value = "$A$3"

dispatcher.executeDispatch(document, ".uno:GoToCell", "", 0, args6())

rem ----------------------------------------------------------------------
dim args7(0) as new com.sun.star.beans.PropertyValue
args7(0).Name = "ToPoint"
args7(0).Value = "Data.$A$3:$L$3"

dispatcher.executeDispatch(document, ".uno:GoToCell", "", 0, args7())

rem ----------------------------------------------------------------------
dim args8(5) as new com.sun.star.beans.PropertyValue
args8(0).Name = "Flags"
args8(0).Value = "SVD"
args8(1).Name = "FormulaCommand"
args8(1).Value = 0
args8(2).Name = "SkipEmptyCells"
args8(2).Value = false
args8(3).Name = "Transpose"
args8(3).Value = true
args8(4).Name = "AsLink"
args8(4).Value = false
args8(5).Name = "MoveMode"
args8(5).Value = 4

dispatcher.executeDispatch(document, ".uno:InsertContents", "", 0, args8())

rem ----------------------------------------------------------------------
dim args9(0) as new com.sun.star.beans.PropertyValue
args9(0).Name = "Nr"
args9(0).Value = 23

dispatcher.executeDispatch(document, ".uno:JumpToTable", "", 0, args9())

rem ----------------------------------------------------------------------
dim args10(0) as new com.sun.star.beans.PropertyValue
args10(0).Name = "ToPoint"
args10(0).Value = "$C$2"

dispatcher.executeDispatch(document, ".uno:GoToCell", "", 0, args10())


oCtrl = ThisComponent.CurrentController
oCtrl.Select(oCtrl.ActiveSheet.GetCellRangeByName("c2:c10"))
ThisComponent.getCurrentSelection.ClearContents(23)
oCtrl.Select(oCtrl.ActiveSheet.GetCellRangeByName("c2"))


end sub

Added the sheet name just like you told me my sheet name is data .
Open Office 4.1.6

Ubuntu 12.1.14
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Copying data from one sheet to another using macro

Post by Villeroy »

The point is missing
args1(0).Value = "sum.$C$1:$C$12"









why do people who never used a spreadsheet try to master macro code?
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
jeevanthara
Posts: 25
Joined: Tue Jul 30, 2019 12:31 pm

Re: Copying data from one sheet to another using macro

Post by jeevanthara »

It was a typo. I am working a cashier in a bank. Just want to record the cash each customer deposits with denominations. Our office software works in Ubuntu. I didn't write those code just recorded the macro by the advise given by the fellow forum members. I am no expert

Code: Select all

sub Main
rem ----------------------------------------------------------------------
rem define variables
dim document   as object
dim dispatcher as object
rem ----------------------------------------------------------------------
rem get access to the document
document   = ThisComponent.CurrentController.Frame
dispatcher = createUnoService("com.sun.star.frame.DispatchHelper")

rem ----------------------------------------------------------------------
dim args1(0) as new com.sun.star.beans.PropertyValue
args1(0).Name = "ToPoint"
args1(0).Value = "sum.$C$1:$C$12"

dispatcher.executeDispatch(document, ".uno:GoToCell", "", 0, args1())

rem ----------------------------------------------------------------------
dispatcher.executeDispatch(document, ".uno:Copy", "", 0, Array())

rem ----------------------------------------------------------------------
dim args3(0) as new com.sun.star.beans.PropertyValue
args3(0).Name = "Navigator"
args3(0).Value = true

dispatcher.executeDispatch(document, ".uno:Navigator", "", 0, args3())

rem ----------------------------------------------------------------------
dim args4(0) as new com.sun.star.beans.PropertyValue
args4(0).Name = "Nr"
args4(0).Value = 24

dispatcher.executeDispatch(document, ".uno:JumpToTable", "", 0, args4())

rem ----------------------------------------------------------------------
dispatcher.executeDispatch(document, ".uno:InsertRows", "", 0, Array())

rem ----------------------------------------------------------------------
dim args6(0) as new com.sun.star.beans.PropertyValue
args6(0).Name = "ToPoint"
args6(0).Value = "$A$3"

dispatcher.executeDispatch(document, ".uno:GoToCell", "", 0, args6())

rem ----------------------------------------------------------------------
dim args7(0) as new com.sun.star.beans.PropertyValue
args7(0).Name = "ToPoint"
args7(0).Value = "data.$A$3:$L$3"

dispatcher.executeDispatch(document, ".uno:GoToCell", "", 0, args7())

rem ----------------------------------------------------------------------
dim args8(5) as new com.sun.star.beans.PropertyValue
args8(0).Name = "Flags"
args8(0).Value = "SVD"
args8(1).Name = "FormulaCommand"
args8(1).Value = 0
args8(2).Name = "SkipEmptyCells"
args8(2).Value = false
args8(3).Name = "Transpose"
args8(3).Value = true
args8(4).Name = "AsLink"
args8(4).Value = false
args8(5).Name = "MoveMode"
args8(5).Value = 4

dispatcher.executeDispatch(document, ".uno:InsertContents", "", 0, args8())

rem ----------------------------------------------------------------------
dim args9(0) as new com.sun.star.beans.PropertyValue
args9(0).Name = "Nr"
args9(0).Value = 23

dispatcher.executeDispatch(document, ".uno:JumpToTable", "", 0, args9())

rem ----------------------------------------------------------------------
dim args10(0) as new com.sun.star.beans.PropertyValue
args10(0).Name = "ToPoint"
args10(0).Value = "$C$2"

dispatcher.executeDispatch(document, ".uno:GoToCell", "", 0, args10())


oCtrl = ThisComponent.CurrentController
oCtrl.Select(oCtrl.ActiveSheet.GetCellRangeByName("c2:c10"))
ThisComponent.getCurrentSelection.ClearContents(23)
oCtrl.Select(oCtrl.ActiveSheet.GetCellRangeByName("c2"))


end sub
Open Office 4.1.6

Ubuntu 12.1.14
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Copying data from one sheet to another using macro

Post by Villeroy »

A bank with no IT department relying on spreeadsheet spreadsheet macros recorded by the cashier? What is the name of that "bank"?
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
Post Reply