Merge sheets of spreadsheet documents

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 section is not for asking questions about writing your own macros.
Post Reply
User avatar
Villeroy
Volunteer
Posts: 31264
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Merge sheets of spreadsheet documents

Post by Villeroy »

This code snippet deals with sheets to be merged from multiple documents into one sheet. Merge sheets of one spreadsheet document deals with a single document's sheets to be merged into one.

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 created with the version before 2019-04-26:
Replace the Basic library with the new one.
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.


How to transfer this template's functionality to your own template or document
1) Add a named cell "Target" marking the top-left corner in the header row of your import area. "Target" is mandatory.
2) Copy my Setup sheet to your document. Alternatively, create the named cells you need anywhere within your document. Sheet name does not matter. Missing named cells will be ignored.
3) Tools>Macros>Organize>Basic... button [Organize...], tab "Libraries", select your own document under "Location", click [Import...], point to my template file and import library "SheetMerge" from my file into your own.
4) Attach push buttons (as in my template) or use document specific toolbars, (context) menues.

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.

2021-08-18: Minor change. Cleared all setup values. They only represented my test scenario and prevent proper functioning elsewhere.
Attachments
SheetMerger.ots
Configurable spreadsheet/csv merger in a template.
(29.72 KiB) Downloaded 800 times
Last edited by Villeroy on Wed Sep 28, 2022 3:23 pm, edited 10 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 with LibreOffice 6.0, latest OpenOffice and LibreOffice
guidox
Posts: 3
Joined: Sun Nov 08, 2015 11:19 am

Re: Merge sheets of spreadsheet documents

Post by guidox »

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

Re: Merge sheets of spreadsheet documents

Post by Villeroy »

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 with LibreOffice 6.0, latest OpenOffice and LibreOffice
guidox
Posts: 3
Joined: Sun Nov 08, 2015 11:19 am

Re: Merge sheets of spreadsheet documents

Post by guidox »

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

Re: Merge sheets of spreadsheet documents

Post by Villeroy »

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 with LibreOffice 6.0, latest OpenOffice and LibreOffice
guidox
Posts: 3
Joined: Sun Nov 08, 2015 11:19 am

Re: Merge sheets of spreadsheet documents

Post by guidox »

Don´t know why but i push again and it worked.. thank you!
libreoffice 5.0.3, Mac osx, el capitan
Post Reply