Merge sheets of spreadsheet documents

Creating Extension - Shared Libraries
Forum rules
For sharing working examples of macros / scripts. These can be in any script language supported by OpenOffice.org [Basic, Python, Netbean] or as source code files in Java or C# even - but requires the actual source code listing. This forum is not for asking questions about writing your own macros.

Merge sheets of spreadsheet documents

Postby Villeroy » Sat May 09, 2015 11:05 pm

The attached spreadsheet template with macros may be used as a permanent solution to merge incoming raw data from other spreadsheets or text files in a specified directory.

Open the template and save it as a regular document in a trusted directory where execution of document macros is allowed.
Fill out the setup options describing the source files.
Add your own sheets with formulas charts, pivot tables and anything you need. The import process inserts new cells for every import so all references to the target range will expand accordingly.

Import options on the setup sheet
1. Source folder
2. Shell pattern of source files
3. An index for the sheet to be imported (0 = import first sheet). It will import the used range of that sheet.
4. How many header rows to be skipped in the source files.
5. An option to insert data on top of the target list (latest data on top)
6. Filter options for text files and a macro to read the correct filter options from a manually imported text file.

2019-04-26: New features:
7. Specify a directory where to move merged files so you don't merge any file twice.

The merging macro document ignores itself. Now you may put all files in the same directory and leave out the source folder path. Default is the path of the merging macro document.
Added more error messages and a success message about how many files have been merged.

How to update an existing merger file you want to keep on using:
Replace the Basic library.
Add a named cell "MovePath" if you want to use this feature.
The path picker button changed. There are now two of them calling the same modified macro. Open toolbar "form design" or "form controls", turn on edit mode, copy the buttons one by one, turn off edit mode.


The data sheet
The target area is specified by a named cell "Target" in the freely editable header row of the target sheet. This is cell Data.C4 and can be changed by moving this named cell.
The macro will determine the current region of adjacent non-empty cells around this cell named "Target" as the target list to be expanded.
The macro will insert new cells, so any references to the list range will adjust to the new size. Formulas, charts, names and any other type of reference will be adjusted automatically to the new size of the growing list.
The first column of the list will always store the name of the source file, so you keep track of the origin of data.
The first row above the imported data (which is the row of the target cell) may be filled with arbitrary column labels.

The entire configuration is based on named cells. You can move the named cells anywhere in this document and you can rename all sheets to your liking. Without editing a single line of code your macro spreadsheet may look completely different than the one you downloaded as long as the named cells and the macros remain in the same file.

How to get the correct import option for text files (*.csv):
1) Open the macro document and one of your csv files manually with all import options properly set.
2) From the window of the opened csv file call Tools>Macros>Organize>Basic ... (NOT Tools>Macros>Run...)
3) Navigate to your macro document (e.g. SheetMerger.ods), library "SheetMerge", module "FilterOptions" and run macro "setFilterOptions" which will write the filter options of the active document into the named cell "FilterOptions" of the macro document.
Attachments
SheetMerger.ots
Configurable spreadsheet/csv merger in a template.
(32.13 KiB) Downloaded 20 times
Last edited by Villeroy on Sat Apr 27, 2019 10:24 am, edited 5 times in total.
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: 26731
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Merge sheets of spreadsheet documents

Postby guidox » Sun Nov 08, 2015 11:22 am

Hi,

Im so sorry to write that, but I don't know how tu input the pattern. I have a folder with some cal, named 1.ods, 2.ods.. but i don't know what pattern should i use...
libreoffice 5.0.3, Mac osx, el capitan
guidox
 
Posts: 3
Joined: Sun Nov 08, 2015 11:19 am

Re: Merge sheets of spreadsheet documents

Postby Villeroy » Sun Nov 08, 2015 3:39 pm

guidox wrote:Hi,

Im so sorry to write that, but I don't know how tu input the pattern. I have a folder with some cal, named 1.ods, 2.ods.. but i don't know what pattern should i use...

*.ods
starting anyhow, ending with .ods
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: 26731
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Merge sheets of spreadsheet documents

Postby guidox » Sun Nov 08, 2015 3:47 pm

Hi!

Thanks for your quick answer!! I tried that before an get this error:
Attachments
Captura de pantalla 2015-11-08 a las 14.42.41.jpg
libreoffice 5.0.3, Mac osx, el capitan
guidox
 
Posts: 3
Joined: Sun Nov 08, 2015 11:19 am

Re: Merge sheets of spreadsheet documents

Postby Villeroy » Sun Nov 08, 2015 4:17 pm

One of your ods files is not a spreadsheet document. It is the latest document that had been loaded in the code line before the error.
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: 26731
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Merge sheets of spreadsheet documents

Postby guidox » Sun Nov 08, 2015 4:36 pm

Don´t know why but i push again and it worked.. thank you!
libreoffice 5.0.3, Mac osx, el capitan
guidox
 
Posts: 3
Joined: Sun Nov 08, 2015 11:19 am

Re: Merge sheets of spreadsheet documents

Postby MistaX32 » Fri Apr 08, 2016 4:11 pm

Thank you so much for this! This is very helpful!

I realize this is an old post but I hope I can still get through.

I intend to use this for work to compile information from different sources.
I have half-decent knowledge on java programming and very basic c++. When I look to edit these macros using "basic" I really cannot follow.

I am trying to change one thing to it: when the information from a file is pasted, I would like it include format (but not formulas - so I'm glad that's not the case now).

Would it be possible to let me know what and where I should edit this?



As an additional point, I have a folder full of spreadsheet-shortcuts so that I may access the original (which is in a customer folder) and not work with differing copies.

Is there any way to apply these great macros you created to the targets of these shortcuts?
OpenOffice 4.1.2 on Windows 10
MistaX32
 
Posts: 1
Joined: Fri Apr 08, 2016 3:57 pm


Return to Code Snippets

Who is online

Users browsing this forum: No registered users and 1 guest