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

Creating a macro - Writing a Script - Using the API

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

Postby jeevanthara » Sat May 30, 2020 6:32 pm

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   Expand viewCollapse view
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
jeevanthara
 
Posts: 25
Joined: Tue Jul 30, 2019 12:31 pm

Re: Copying data from one sheet to another using macro

Postby JohnSUN-Pensioner » Sat May 30, 2020 7:34 pm

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
User avatar
JohnSUN-Pensioner
Volunteer
 
Posts: 824
Joined: Fri Jan 14, 2011 1:21 pm
Location: Kyiv, Ukraine

Re: Copying data from one sheet to another using macro

Postby jeevanthara » Sun May 31, 2020 7:19 am

I have read the ebook.

Code: Select all   Expand viewCollapse view
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
jeevanthara
 
Posts: 25
Joined: Tue Jul 30, 2019 12:31 pm

Re: Copying data from one sheet to another using macro

Postby Zizi64 » Sun May 31, 2020 7:56 am

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; LO6.1.6, 6.2.8 /Win7-10 x64Prof.
PortableApps/winPenPack: LO3.3.0-6.4.5;AOO4.1.7
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: 9430
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: Copying data from one sheet to another using macro

Postby Zizi64 » Sun May 31, 2020 8:23 am

Here is a topic with a very similar task:
viewtopic.php?f=5&t=89993
Tibor Kovacs, Hungary; LO6.1.6, 6.2.8 /Win7-10 x64Prof.
PortableApps/winPenPack: LO3.3.0-6.4.5;AOO4.1.7
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: 9430
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: Copying data from one sheet to another using macro

Postby Villeroy » Sun May 31, 2020 10:03 am

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, no OpenOffice, LibreOffice 6.4
User avatar
Villeroy
Volunteer
 
Posts: 28431
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Copying data from one sheet to another using macro

Postby Zizi64 » Sun May 31, 2020 10:17 am

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; LO6.1.6, 6.2.8 /Win7-10 x64Prof.
PortableApps/winPenPack: LO3.3.0-6.4.5;AOO4.1.7
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: 9430
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: Copying data from one sheet to another using macro

Postby Villeroy » Sun May 31, 2020 11:25 am

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   Expand viewCollapse view
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, no OpenOffice, LibreOffice 6.4
User avatar
Villeroy
Volunteer
 
Posts: 28431
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Copying data from one sheet to another using macro

Postby Villeroy » Sun May 31, 2020 3:32 pm

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   Expand viewCollapse view
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   Expand viewCollapse view
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, no OpenOffice, LibreOffice 6.4
User avatar
Villeroy
Volunteer
 
Posts: 28431
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Copying data from one sheet to another using macro

Postby jeevanthara » Sun May 31, 2020 5:57 pm

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   Expand viewCollapse view
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
jeevanthara
 
Posts: 25
Joined: Tue Jul 30, 2019 12:31 pm

Re: Copying data from one sheet to another using macro

Postby Villeroy » Sun May 31, 2020 6:05 pm

Add a sheet name to the value of named value "ToPoint", for instance:
Code: Select all   Expand viewCollapse view
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, no OpenOffice, LibreOffice 6.4
User avatar
Villeroy
Volunteer
 
Posts: 28431
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Copying data from one sheet to another using macro

Postby jeevanthara » Sun May 31, 2020 6:38 pm

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   Expand viewCollapse view
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
jeevanthara
 
Posts: 25
Joined: Tue Jul 30, 2019 12:31 pm

Re: Copying data from one sheet to another using macro

Postby Villeroy » Sun May 31, 2020 6:42 pm

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, no OpenOffice, LibreOffice 6.4
User avatar
Villeroy
Volunteer
 
Posts: 28431
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Copying data from one sheet to another using macro

Postby jeevanthara » Sun May 31, 2020 6:49 pm

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   Expand viewCollapse view
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
jeevanthara
 
Posts: 25
Joined: Tue Jul 30, 2019 12:31 pm

Re: Copying data from one sheet to another using macro

Postby Villeroy » Sun May 31, 2020 7:34 pm

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, no OpenOffice, LibreOffice 6.4
User avatar
Villeroy
Volunteer
 
Posts: 28431
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany


Return to Macros and UNO API

Who is online

Users browsing this forum: No registered users and 4 guests