[Solved] Macro to filter data in A.xls and insert into B.xls

Creating a macro - Writing a Script - Using the API

[Solved] Macro to filter data in A.xls and insert into B.xls

Postby croko » Tue Jun 18, 2019 12:49 pm

Lated edit: I manage to create the macro after some more research. Thanks.

Hello,

I'm new to macros world but I struggle to learn. I've did a lot of search on forum, Andrew's book, the macro documentation and Google but did not manage to find how to write a macro to run in LibreOffice macro for this case:

I have 2 different files (the real fiels have more than 10,000 rows with data) that I can open in LibreOffice (or OpenOffice is necessary):

Destination.xls = has up to 12 sheets, all sheets has 2 columns: "ProdID" and "Quantity", in all sheets column "ProdID" contain an unique number and column "Quantity" is empty.

Example of Destination.xls:
ProdID Quantity
1
3
5
9

Source.xls = has just one sheet with two colums "ProdID" and "Order", the "ProdID" column contain some of the unique ID numbers presented in Destination.xls as well and the column "Order" contain numbers.

Example of Source.xls:
ProdID Order
1 20
2 15
3 25
4 40
5 30

I need to open the Destination.xls with LibreOffice and run a macro to search each "ProdID" number from Destination.xls in Source.xls "ProdID" column and if it finds the "ProdID" it Source.xls to take the coresponding value from "Order" cell located in Source.xls file and populate it in the coresponding "Quantity" cell located in Destination.xls file.

Important to mention: the Destination.xls is a protected file where I can only write in Quantity column. I dont have acces to modify / import / change anything else in the Destination.xls file but if I open the file in LibreOffice I can run my owm macro to read data from file and write data in "Quantity" column. I need to fill just the column "Quantity" in all sheets of Destination.xls using the coresponding data from Source.xls without changing in any other way the Destination.xls file.

Any ideeas how can I do this? Many thanks in advance.

PS: I want to thank you very much guys for all the help and sharing that you give around here, I learned a lot from some of you in the past weeks! The learning process to create macros is much easier with some of your advices, info and code sharing. Many thanks!
Last edited by croko on Tue Jun 18, 2019 6:43 pm, edited 3 times in total.
LibreOffice 6.x on Windows 10
croko
 
Posts: 5
Joined: Tue Mar 12, 2019 10:09 pm

Re: Macro in LO/OO to filter data in A.xls and insert into B

Postby Villeroy » Tue Jun 18, 2019 12:53 pm

You can do it manually within seconds.
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.x
User avatar
Villeroy
Volunteer
 
Posts: 26865
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Macro in LO/OO to filter data in A.xls and insert into B

Postby croko » Tue Jun 18, 2019 12:57 pm

Villeroy wrote:You can do it manually within seconds.


The actual files have more than 10,000 rows. I do it manually every week and it takes 2 days of ineffective copy and paste :)
LibreOffice 6.x on Windows 10
croko
 
Posts: 5
Joined: Tue Mar 12, 2019 10:09 pm


Return to Macros and UNO API

Who is online

Users browsing this forum: No registered users and 4 guests