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

Creating a macro - Writing a Script - Using the API

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

Postby chetan3125 » Sat Dec 17, 2011 5:00 pm

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 243 times
B.ods
(12.35 KiB) Downloaded 149 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 128 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
chetan3125
 
Posts: 17
Joined: Sat Dec 17, 2011 4:39 pm

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

Postby JohnSUN-Pensioner » Sat Dec 17, 2011 5:40 pm

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...
OpenOffice.org 3.3.0 OOO330m20(Build:9567),LibreOffice 3.5.2.2 on Windows XP.SP2
If you think that I did not answer your question, make allowances for my imperfect English
User avatar
JohnSUN-Pensioner
Volunteer
 
Posts: 568
Joined: Fri Jan 14, 2011 1:21 pm
Location: Kyiv, Ukraine

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

Postby chetan3125 » Sat Dec 17, 2011 6:37 pm

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
chetan3125
 
Posts: 17
Joined: Sat Dec 17, 2011 4:39 pm

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

Postby Hagar Delest » Sat Dec 17, 2011 6:42 pm

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.
AOO 4.0.1 on Windows 7 and Xubuntu 14.04 (Trusty Tahr)
User avatar
Hagar Delest
Moderator
 
Posts: 25106
Joined: Sun Oct 07, 2007 9:07 pm
Location: France

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

Postby Villeroy » Sat Dec 17, 2011 6:56 pm

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 12.04, Apache OpenOffice 4.x
User avatar
Villeroy
Volunteer
 
Posts: 17310
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

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

Postby chetan3125 » Sat Dec 17, 2011 7:00 pm

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

Postby chetan3125 » Sat Dec 17, 2011 7:03 pm

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
chetan3125
 
Posts: 17
Joined: Sat Dec 17, 2011 4:39 pm

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

Postby JohnSUN-Pensioner » Sat Dec 17, 2011 7:53 pm

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...
OpenOffice.org 3.3.0 OOO330m20(Build:9567),LibreOffice 3.5.2.2 on Windows XP.SP2
If you think that I did not answer your question, make allowances for my imperfect English
User avatar
JohnSUN-Pensioner
Volunteer
 
Posts: 568
Joined: Fri Jan 14, 2011 1:21 pm
Location: Kyiv, Ukraine

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

Postby Villeroy » Sat Dec 17, 2011 8:54 pm

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 12.04, Apache OpenOffice 4.x
User avatar
Villeroy
Volunteer
 
Posts: 17310
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

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

Postby chetan3125 » Sat Dec 17, 2011 9:19 pm

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
chetan3125
 
Posts: 17
Joined: Sat Dec 17, 2011 4:39 pm

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

Postby JohnSUN-Pensioner » Sat Dec 17, 2011 9:38 pm

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...
OpenOffice.org 3.3.0 OOO330m20(Build:9567),LibreOffice 3.5.2.2 on Windows XP.SP2
If you think that I did not answer your question, make allowances for my imperfect English
User avatar
JohnSUN-Pensioner
Volunteer
 
Posts: 568
Joined: Fri Jan 14, 2011 1:21 pm
Location: Kyiv, Ukraine

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

Postby chetan3125 » Sat Dec 17, 2011 10:19 pm

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
chetan3125
 
Posts: 17
Joined: Sat Dec 17, 2011 4:39 pm

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

Postby kingfisher » Sun Dec 18, 2011 5:46 am

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.
OpenOffice 3.3 on PCLinuxOS. There are 3 kinds of people: those who can count and those who can't.
User avatar
kingfisher
Volunteer
 
Posts: 1861
Joined: Tue Nov 20, 2007 10:53 am

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

Postby chetan3125 » Sun Dec 18, 2011 7:05 am

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
chetan3125
 
Posts: 17
Joined: Sat Dec 17, 2011 4:39 pm

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

Postby Hagar Delest » Sun Dec 18, 2011 11:48 am

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.
AOO 4.0.1 on Windows 7 and Xubuntu 14.04 (Trusty Tahr)
User avatar
Hagar Delest
Moderator
 
Posts: 25106
Joined: Sun Oct 07, 2007 9:07 pm
Location: France

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

Postby rudolfo » Sun Dec 18, 2011 3:54 pm

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.
rudolfo
Volunteer
 
Posts: 1415
Joined: Wed Mar 19, 2008 11:34 am
Location: Germany

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

Postby chetan3125 » Sun Dec 18, 2011 7:43 pm

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
chetan3125
 
Posts: 17
Joined: Sat Dec 17, 2011 4:39 pm

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

Postby RoryOF » Sun Dec 18, 2011 7:59 pm

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.0.1 on Xubuntu 14.04 and Win2K/XP
User avatar
RoryOF
Moderator
 
Posts: 14373
Joined: Sat Jan 31, 2009 9:30 pm
Location: Ireland

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

Postby chetan3125 » Sun Dec 18, 2011 8:09 pm

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
chetan3125
 
Posts: 17
Joined: Sat Dec 17, 2011 4:39 pm

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

Postby JohnSUN-Pensioner » Sun Dec 18, 2011 8:10 pm

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...
OpenOffice.org 3.3.0 OOO330m20(Build:9567),LibreOffice 3.5.2.2 on Windows XP.SP2
If you think that I did not answer your question, make allowances for my imperfect English
User avatar
JohnSUN-Pensioner
Volunteer
 
Posts: 568
Joined: Fri Jan 14, 2011 1:21 pm
Location: Kyiv, Ukraine

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

Postby JohnSUN-Pensioner » Sun Dec 18, 2011 8:12 pm

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...
OpenOffice.org 3.3.0 OOO330m20(Build:9567),LibreOffice 3.5.2.2 on Windows XP.SP2
If you think that I did not answer your question, make allowances for my imperfect English
User avatar
JohnSUN-Pensioner
Volunteer
 
Posts: 568
Joined: Fri Jan 14, 2011 1:21 pm
Location: Kyiv, Ukraine

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

Postby chetan3125 » Sun Dec 18, 2011 8:19 pm

Oh, Thanks John.
I can surely wait. No problem at all.
Enjoy your weekend.
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

Postby JohnSUN-Pensioner » Sun Dec 18, 2011 8:26 pm

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...
OpenOffice.org 3.3.0 OOO330m20(Build:9567),LibreOffice 3.5.2.2 on Windows XP.SP2
If you think that I did not answer your question, make allowances for my imperfect English
User avatar
JohnSUN-Pensioner
Volunteer
 
Posts: 568
Joined: Fri Jan 14, 2011 1:21 pm
Location: Kyiv, Ukraine

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

Postby chetan3125 » Sun Dec 18, 2011 8:34 pm

No. order doesn't matter.
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

Postby JohnSUN-Pensioner » Sun Dec 25, 2011 7:11 pm

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 407 times
I may not have a lot to give but what I got I'll give to you...
OpenOffice.org 3.3.0 OOO330m20(Build:9567),LibreOffice 3.5.2.2 on Windows XP.SP2
If you think that I did not answer your question, make allowances for my imperfect English
User avatar
JohnSUN-Pensioner
Volunteer
 
Posts: 568
Joined: Fri Jan 14, 2011 1:21 pm
Location: Kyiv, Ukraine

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

Postby F3K Total » Mon Dec 26, 2011 8:32 pm

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 766 times
    MMove 1.0.6
  • Extension for easy, exact positioning of shapes, pictures, controls, frames ...
    my current system
  • Windows 7 OOo 3.3.0, AOO 3.4.1, 4.0.1, LO 4.0.6, 4.1.5 Linux Mint AOO 3.4.1, 4.0.1, LO 4.0.6, 4.1.5
F3K Total
Volunteer
 
Posts: 403
Joined: Fri Dec 16, 2011 8:20 pm

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

Postby chetan3125 » Thu Dec 29, 2011 6:57 pm

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

Postby chetan3125 » Thu Dec 29, 2011 7:00 pm

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

Postby chetan3125 » Tue Jan 03, 2012 4:56 pm

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
chetan3125
 
Posts: 17
Joined: Sat Dec 17, 2011 4:39 pm


Return to Macros and UNO API

Who is online

Users browsing this forum: No registered users and 3 guests