Page 1 of 1

Merge sheets of spreadsheet documents

PostPosted: Sat May 09, 2015 11:05 pm
by Villeroy
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.

Re: Merge sheets of spreadsheet documents

PostPosted: Sun Nov 08, 2015 11:22 am
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...

Re: Merge sheets of spreadsheet documents

PostPosted: Sun Nov 08, 2015 3:39 pm
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

Re: Merge sheets of spreadsheet documents

PostPosted: Sun Nov 08, 2015 3:47 pm
by guidox
Hi!

Thanks for your quick answer!! I tried that before an get this error:

Re: Merge sheets of spreadsheet documents

PostPosted: Sun Nov 08, 2015 4:17 pm
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.

Re: Merge sheets of spreadsheet documents

PostPosted: Sun Nov 08, 2015 4:36 pm
by guidox
DonĀ“t know why but i push again and it worked.. thank you!

Re: Merge sheets of spreadsheet documents

PostPosted: Fri Apr 08, 2016 4:11 pm
by MistaX32
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?

Re: Merge sheets of spreadsheet documents

PostPosted: Sat Jun 22, 2019 12:31 pm
by Villeroy
MistaX32 wrote:Is there any way to apply these great macros you created to the targets of these shortcuts?

1. I did not notice this question in 2 years because you did not post it to the right forum. See red box on top of this page.
2. Regarding the question: This works for me on a user friendly operating system. I don't have WIndows at hand yet, but I know that all its link files end with name extension .lnk and Windows hides this suffix from the user even if you tell it to not hide any name extensions.