[Solved] Calc: copy data from a spreadsheet to another

Creating a macro - Writing a Script - Using the API (OpenOffice Basic, Python, BeanShell, JavaScript)
User avatar
chetan3125
Posts: 17
Joined: Sat Dec 17, 2011 4:39 pm

[Solved] Calc: copy data from a spreadsheet to another

Post by chetan3125 »

Hi guys,
I am completely new for calc and I desperately need the help.
I have two separate calc documents A ,B and C. (Please see the attachments) I want to copy data from A and B to C sheet. all three documents ar placed in same folder. Please tell me some way to copy data from A and B to C. eg. both A and B sheets contain data from A1:H34 and I want to copy data from sheet A (from these cell only) to sheet c on cells A1:H34 and data from sheet B to sheet C on cell A35:H68.
Please tell me about creating macros for this since I have to do this on each day.
It would be of more help if this can be a single click procedure (by creating a button on sheet C to execute macros)
Any help would be much appreciated.
Attachments
A.ods
(12.44 KiB) Downloaded 903 times
B.ods
(12.35 KiB) Downloaded 665 times
C.ods
This is the common file and I want to copy data on this From sheet A and B
(8.57 KiB) Downloaded 595 times
Last edited by Hagar Delest on Thu Dec 29, 2011 7:13 pm, edited 1 time in total.
Reason: tagged [Solved].
OpenOffice 3.1 on Windows 7
User avatar
JohnSUN-Pensioner
Volunteer
Posts: 876
Joined: Fri Jan 14, 2011 1:21 pm
Location: Kyiv, Ukraine

Re: Calc copy data from one spreadsheet to another with macr

Post by JohnSUN-Pensioner »

Welcome to forum, chetan3125!
You asked a very good and very valid question: How can I learn?
Try here, on this site - http://www.pitonyak.org/ - to download a book AndrewMacro.odt.
This book is very large. But for the first time will be enough to read "5.23.2. Copy Spreadsheet Cells Without The Clipboard" and "6.22. Which cells are used in a sheet?". You may also need "Listing 5.14: Load a document from a given URL."
This is quite a bit! And it works! (Believe me! I know what I'm saying. I'm studied in this book)
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
chetan3125
Posts: 17
Joined: Sat Dec 17, 2011 4:39 pm

Re: Calc copy data from one spreadsheet to another with macr

Post by chetan3125 »

sorry to bother you John,
but I am very new to this and Icant igure out what exactly the book is saying. Can you please elaborate the procedure for me in steps because in book the procedure seems to me too scattered.
Thanks for your reply.
OpenOffice 3.1 on Windows 7
User avatar
Hagar Delest
Moderator
Posts: 32627
Joined: Sun Oct 07, 2007 9:07 pm
Location: France

Re: Calc copy data from one spreadsheet to another with macr

Post by Hagar Delest »

You've to learn. So you've to study the basics about selecting a range, then change the active file and paste the clipboard.
There is no easy step by step to be described here.
LibreOffice 7.6.2.1 on Xubuntu 23.10 and 7.6.4.1 portable on Windows 10
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Calc copy data from one spreadsheet to another with macr

Post by Villeroy »

chetan3125 wrote: Please tell me about creating macros for this since I have to do this on each day.
I have to do similar things each and every day and I know very well how to write macros. I would never write a macro for such trivial keystroke operations. It is not worth the effort. It works only if the specific macro for the specific task is availlable on the current system. You never learn how to use the software efficiently in similar situations.
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
chetan3125
Posts: 17
Joined: Sat Dec 17, 2011 4:39 pm

Re: Calc copy data from one spreadsheet to another with macr

Post by chetan3125 »

I am just stuck on step to change active file. I tried to the same thing in excel by clicking on open then new document and then pasting data in new document which gets captured automatically with clicks in excel macros. can Somebody guide me to do this in calc ?
OpenOffice 3.1 on Windows 7
User avatar
chetan3125
Posts: 17
Joined: Sat Dec 17, 2011 4:39 pm

Re: Calc copy data from one spreadsheet to another with macr

Post by chetan3125 »

Villeroy wrote:
chetan3125 wrote: Please tell me about creating macros for this since I have to do this on each day.
I have to do similar things each and every day and I know very well how to write macros. I would never write a macro for such trivial keystroke operations. It is not worth the effort. It works only if the specific macro for the specific task is availlable on the current system. You never learn how to use the software efficiently in similar situations.
Is there any other alternative to this ? like VBA ?
OpenOffice 3.1 on Windows 7
User avatar
JohnSUN-Pensioner
Volunteer
Posts: 876
Joined: Fri Jan 14, 2011 1:21 pm
Location: Kyiv, Ukraine

Re: Calc copy data from one spreadsheet to another with macr

Post by JohnSUN-Pensioner »

chetan3125 wrote:I am just stuck on step to change active file. I tried to the same thing in excel by clicking on open then new document and then pasting data in new document which gets captured automatically with clicks in excel macros. can Somebody guide me to do this in calc ?
I'm not sure that it would work, but you can try.
Each new version of OOo better understand the code of VBA.
Try updating your office suite to the latest version and try to run the macro, which you recorded in Excel.

Perhaps, you may not need to learn programming (Sorry, very sorry. I was wrong again)
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
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Calc copy data from one spreadsheet to another with macr

Post by Villeroy »

Just ask yourself how fast you can copy (Ctrl+C), switch window (Alt+Tab), and paste (Ctrl+V) in any application without having to write a separate program for each and every application you are using. Writing macros is highly inefficient. You will never use anything else but Excel if your spreadsheet experience depends on such VBA bullshit.
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
chetan3125
Posts: 17
Joined: Sat Dec 17, 2011 4:39 pm

Re: Calc copy data from one spreadsheet to another with macr

Post by chetan3125 »

Villeroy wrote:Just ask yourself how fast you can copy (Ctrl+C), switch window (Alt+Tab), and paste (Ctrl+V) in any application without having to write a separate program for each and every application you are using. Writing macros is highly inefficient. You will never use anything else but Excel if your spreadsheet experience depends on such VBA bullshit.
I know its very simple.
But It becomes a tedious task for me because each day i have to copy data from 20 sheets to one master sheet (every day). and i thought I can do this with help of macros because I have to copy cells of same range (A1:H34) from these 20 sheets.
Hope you understand my problem becoz this consumes significant time of mine at work.
Hope all you experts can give me the solution !
Thanks.
OpenOffice 3.1 on Windows 7
User avatar
JohnSUN-Pensioner
Volunteer
Posts: 876
Joined: Fri Jan 14, 2011 1:21 pm
Location: Kyiv, Ukraine

Re: Calc copy data from one spreadsheet to another with macr

Post by JohnSUN-Pensioner »

OK. You told half the problem. Do not hesitate to tell the rest. What are you going to do with the combined data?
Please do not be offended. Most of the newcomers on this forum are also trying to help us.
To do this, they do not describe the whole problem, but only a smaller part.
Perhaps worried that the difficult issue we are not going to answer? In fact, we get bored too easy to answer questions.

You have every day 20 new files with the original data. You combine these data into a single list. What's next?
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
chetan3125
Posts: 17
Joined: Sat Dec 17, 2011 4:39 pm

Re: Calc copy data from one spreadsheet to another with macr

Post by chetan3125 »

Thanks for understanding me John.
you said it correctly. I have to copy data from these 20 files ( actually these are records of 20 persons) on one master file and perform different operations on it like additions, averages and percentages collectively for all of them. Since this is a collective task; it becomes easy to do on master spreadsheet and if I have dat of all 20 persons on it.
Please don't hesitate to ask if you need any more info on this.
Happy to see that somebody is really interested to solve my problem.
May be you can give me an advantage at my workplace if this gets solved.
Thanks a lot.
OpenOffice 3.1 on Windows 7
User avatar
kingfisher
Volunteer
Posts: 2123
Joined: Tue Nov 20, 2007 10:53 am

Re: Calc copy data from one spreadsheet to another with macr

Post by kingfisher »

Is there a new master document each day? How is the data arranged in the master document? Is it on 20 sheets? Do you use database ranges or named ranges?

EDIT: If you have one sheet per person in the master file, you could simply use the menu selection Insert > Sheet from file.
Apache OpenOffice 4.1.9 on Linux
User avatar
chetan3125
Posts: 17
Joined: Sat Dec 17, 2011 4:39 pm

Re: Calc copy data from one spreadsheet to another with macr

Post by chetan3125 »

Yes. There is new master document every day.
Data is arranged on master on shhet 1 only
eg. data from sheet A will come on rows 1 to 34 data from sheet B will be on rows 35 to 68 and so on.
and, I am not using database or named ranges since I am not using master sheet as database. It's just a simple spreadsheet.
and I am just copy pasting the data.
OpenOffice 3.1 on Windows 7
User avatar
Hagar Delest
Moderator
Posts: 32627
Joined: Sun Oct 07, 2007 9:07 pm
Location: France

Re: Calc copy data from one spreadsheet to another with macr

Post by Hagar Delest »

A macro would be helpful if it can handle the opening of each of the 20 files one by ones and do the copy/paste task.

I move your topic to the macro forum.
Please post your code and check the material pointed out at the beginning to get code snippets.
The macro would have to:
- be launched from the master file
- open the first file
- copy the range
- close the file
- place cursor at beginning of the destination range (master file)
- paste the range
- open 2nd file
- ...
A list has to be provided for the list of the files and the destination range has to be changed at each new file.
LibreOffice 7.6.2.1 on Xubuntu 23.10 and 7.6.4.1 portable on Windows 10
rudolfo
Volunteer
Posts: 1488
Joined: Wed Mar 19, 2008 11:34 am
Location: Germany

Re: Calc copy data from one spreadsheet to another with macr

Post by rudolfo »

Okay, this is post number 16 of this thread, and we still working on the description of the case that should be solved with macros. Some things are clear now: always the same range, every range of a sheet will be copied in the same sheet with the next range below the previous, 20 sheets in 20 files to copy, .... I think the next step would be how to open those 20 files: Do they follow a certain naming convention? Or maybe they are the only files in a working directory? (Except for the final target file with the macro, of course)

But something is telling me that Villeroy might be right at the end of the day. If something is so difficult to describe, than it is not made for macro automation, because there are lots of parameters that you intuitively specify when selecting something manually, but
it will be a difficult task to specify this all as input variables for a macro operation. (I am talking of input in an abstract sense here, not explicit parameters of a macro function/procedure). But still 20 files is quite a lot and the would justify some effort put in programming a macro.
OpenOffice 3.1.1 (2.4.3 until October 2009) and LibreOffice 3.3.2 on Windows 2000, AOO 3.4.1 on Windows 7
There are several macro languages in OOo, but none of them is called Visual Basic or VB(A)! Please call it OOo Basic, Star Basic or simply Basic.
User avatar
chetan3125
Posts: 17
Joined: Sat Dec 17, 2011 4:39 pm

Re: Calc copy data from one spreadsheet to another with macr

Post by chetan3125 »

I agree. This looks quite tedious task.
But, if somebody can give me the code for just one operation for first file, I can try the rest as Hagar Delest rightly summed up.

The macro would have to:
- be launched from the master file
- open the first file
- copy the range
- close the file
- place cursor at beginning of the destination range (master file)
- paste the range

----------------------------------------------------------------------------------
Man ! I am disappointed a lot with calc. This task looked so simple in excel.
Here it seems to be a herculean task.
Alas !
OpenOffice 3.1 on Windows 7
User avatar
RoryOF
Moderator
Posts: 34586
Joined: Sat Jan 31, 2009 9:30 pm
Location: Ireland

Re: Calc copy data from one spreadsheet to another with macr

Post by RoryOF »

chetan3125 wrote: Man ! I am disappointed a lot with calc. This task looked so simple in excel.
Here it seems to be a herculean task.
Alas !
The quick answer then is "stick to Excel". Macro writing is _not_ a trivial task, as you are finding.
Apache OpenOffice 4.1.15 on Xubuntu 22.04.4 LTS
User avatar
chetan3125
Posts: 17
Joined: Sat Dec 17, 2011 4:39 pm

Re: Calc copy data from one spreadsheet to another with macr

Post by chetan3125 »

i dont have excel in office. tht's why this is all.
If you think this task is so simple; Why nobody could help me on this ?
OpenOffice 3.1 on Windows 7
User avatar
JohnSUN-Pensioner
Volunteer
Posts: 876
Joined: Fri Jan 14, 2011 1:21 pm
Location: Kyiv, Ukraine

Re: Calc copy data from one spreadsheet to another with macr

Post by JohnSUN-Pensioner »

chetan3125 wrote:- open the first file
Yes, of course. But HOW? By name, which is always the same? First found in the specified directory? Selected in a separate dialog?
chetan3125 wrote: - copy the range
- close the file
- place cursor at beginning of the destination range (master file)
- paste the range
I would suggest a slightly different sequence of actions:
- find last used cell at master file (addr1)
- find last used cell at input file (addr2)
- copy data from range A1:addr2 to A(RowAddr1+1):(size A1:addr2) without clipboard (do you remember "5.23.2. Copy Spreadsheet Cells Without The Clipboard"?)
- close the file
chetan3125 wrote:Man ! I am disappointed a lot with calc. This task looked so simple in excel.
Here it seems to be a herculean task.
Alas !
My friend! You're absolutely right! The problem is quite simple. But the weekend is so difficult to bring myself to write those 15 lines of code ... Do not wait until tomorrow?
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: 876
Joined: Fri Jan 14, 2011 1:21 pm
Location: Kyiv, Ukraine

Re: Calc copy data from one spreadsheet to another with macr

Post by JohnSUN-Pensioner »

chetan3125 wrote:Why nobody could help me on this ?
Once more: Because Sunday :o
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
chetan3125
Posts: 17
Joined: Sat Dec 17, 2011 4:39 pm

Re: Calc copy data from one spreadsheet to another with macr

Post by chetan3125 »

Oh, Thanks John.
I can surely wait. No problem at all.
Enjoy your weekend.
OpenOffice 3.1 on Windows 7
User avatar
JohnSUN-Pensioner
Volunteer
Posts: 876
Joined: Fri Jan 14, 2011 1:21 pm
Location: Kyiv, Ukraine

Re: Calc copy data from one spreadsheet to another with macr

Post by JohnSUN-Pensioner »

Thanks, chetan3125!
But all the same - to take all the files from the directory?
The order does not matter?
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
chetan3125
Posts: 17
Joined: Sat Dec 17, 2011 4:39 pm

Re: Calc copy data from one spreadsheet to another with macr

Post by chetan3125 »

No. order doesn't matter.
OpenOffice 3.1 on Windows 7
User avatar
JohnSUN-Pensioner
Volunteer
Posts: 876
Joined: Fri Jan 14, 2011 1:21 pm
Location: Kyiv, Ukraine

Re: Calc copy data from one spreadsheet to another with macr

Post by JohnSUN-Pensioner »

Thank you waited...
This version is only BETA(!), it has few errors and omissions
But it's a worked. (I hope :D )
Merry Christmas and Happy New Year!
Attachments
CollectData.ods
Beta-version
(25.68 KiB) Downloaded 1587 times
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
F3K Total
Volunteer
Posts: 1038
Joined: Fri Dec 16, 2011 8:20 pm

Re: Calc copy data from one spreadsheet to another with macr

Post by F3K Total »

Hi,
i've maybe a simple solution, one macro. Place C.ods in the same folder as A.ods and B.ods, press the button.
Greats R

edits in blue
Attachments
C.ods
(10.85 KiB) Downloaded 2107 times
  • MMove 1.0.6
  • Extension for easy, exact positioning of shapes, pictures, controls, frames ...
  • my current system
  • Windows 10 AOO, LOLinux Mint AOO, LO
User avatar
chetan3125
Posts: 17
Joined: Sat Dec 17, 2011 4:39 pm

Re: Calc copy data from one spreadsheet to another with macr

Post by chetan3125 »

JohnSUN-Pensioner wrote:Thank you waited...
This version is only BETA(!), it has few errors and omissions
But it's a worked. (I hope :D )
Merry Christmas and Happy New Year!
Hi John,
This is great!
My sincere thanks & gratitude for this.
I especially apreciate your valuable time and knowledge spent in writing this complex macro and being there till the end; when it seemed impossible.
Life and technology becomes easier due to people like you.
Happy new year :bravo: :bravo: :bravo: :bravo: :bravo:
Greetings From India!
Last edited by chetan3125 on Thu Dec 29, 2011 7:04 pm, edited 1 time in total.
OpenOffice 3.1 on Windows 7
User avatar
chetan3125
Posts: 17
Joined: Sat Dec 17, 2011 4:39 pm

Re: Calc copy data from one spreadsheet to another with macr

Post by chetan3125 »

F3K Total wrote:Hi,
i've maybe a simple solution, one macro. Place C.ods in the same folder as A.ods and B.ods, press the button.
Greats R

edits in blue
This was the simplest solution I could get.
Using this I wrote macros for other 20 files.
Many thanks for your time and guidance.
Please keep it up.
Thanks a ton.
Happy new year :D :D :D :D :D
OpenOffice 3.1 on Windows 7
User avatar
chetan3125
Posts: 17
Joined: Sat Dec 17, 2011 4:39 pm

Re: [Solved] Calc: copy data from a spreadsheet to another

Post by chetan3125 »

Hi ,
Though this thread is marked as solved , if anybody has a better or simple solution to this;
It's most welcome !
Thanks.
OpenOffice 3.1 on Windows 7
User avatar
Tiger42
Posts: 66
Joined: Tue Apr 28, 2015 12:34 pm
Location: India

Re: [Solved] Calc: copy data from a spreadsheet to another

Post by Tiger42 »

Hello Guys bother you but I need some assist on same
Simple question : How can I open 2 different sheets (from same place) using Macro ?

Easy : Run macro -> Open file1 in sheet1-> open file 2 in sheet 2 ->
Thats It and Next step I will do my self, Help would be appreciate

Thnks
Libre Office 5.0/ Open Office /MS Excel/ Windows 7-8
Post Reply