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 document with macros may be used as a template or document to merge raw data of other spreadsheets in a specified directory.

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.

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 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.

 Edit: I apologise to the first four downloaders. The csv part was not fully functional.
How to get the correct import option with the new attachment:
Open the macro document and one of your csv files manually with all import options properly set.
From the window of the opened csv file call Tools>Macros>Organize>Basic ... (NOT Tools>Macros>Run...)
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. 

Update 2015-05-23: Improved the file picker to avoid unhandled error when the path cell contains an invalid path.
Changed document type to spreadsheet template (*.ots).
"Install" this template by ignoring any macro warning and then menu:File>Templates>Save...
Attachments
SheetMerger.ots
Configurable spreadsheet/csv merger in a template.
(21.63 KiB) Downloaded 443 times
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 16.04, OpenOffice 4.x & LibreOffice 5.x
User avatar
Villeroy
Volunteer
 
Posts: 24652
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 16.04, OpenOffice 4.x & LibreOffice 5.x
User avatar
Villeroy
Volunteer
 
Posts: 24652
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 16.04, OpenOffice 4.x & LibreOffice 5.x
User avatar
Villeroy
Volunteer
 
Posts: 24652
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