Human Resources often sends a list of employees to each manager, for example, to collect evaluations.

In doing so, we store files for each organization in a folder and ask them to input the information.

This “tool to collect files for each organization” is the tool we are introducing here.

 

Merge File Tool Overview

This tool allows you to consolidate files with the same layout into a single file.

 

The “ARASHI” files are as follows.

 

The “SMAP” files are as follows.

 

The “TOKIO” files are as follows.

 

As you can see, all files have the same layout. If you have several such files, you can use this tool.

 

The following screen shows the actual result of the execution. As you can see, multiple files can be collected on the “Merge” sheet. (Please adjust the column widths after importing.)

 

Download link

Please kindly use the file after you click the following download link.

 

How to use Merge file tool

1. Enter the “Target Folder Path” on the Setting sheet.

Enter the folder path where the files you want to collect are stored into the cell C3: Target Folder Path cell.

 

If the files exist separately in multiple folders, you can specify the folder path of the parent folder to collect files down to its subfolders.

 

Note that the folder path may or may not have a ¥” at the end.

2. Enter the “Target File Extension” on the Setting sheet.

In the C4: Target File Extension cell, enter the extension of the file to be collected.

3. Enter the “Data Start Row” on the Setting sheet.

In the C5: Data Start Row cell, enter the top line number where the data in the file to be collected is entered. This test file has a header of 3 rows, so the data starts on row 4.

 

Therefore, enter “4” in the data start line.

 

5. Enter the “Number of attempts” on the Setting sheet.

C6: Enter a number in the cell for the number of attempts and it will run for that number of times. When using this tool, it is expected that a large number of files will be collected.

To avoid rework, we recommend that you collect only a few files and check to see if the settings you have made so far are correct.

 

6. Enter the “Target Sheet Name” for the Setting sheet.

C8: If the target sheet name is fixed, enter data in this cell. If there is only one sheet, no special settings are required.

 

When there are multiple sheets in the same file with the same target sheet name

In Ver. 2.0, we added a new function, “If there are multiple sheets in the same file with the same target sheet name, the function to collect all sheets”.

Information” is entered in the target sheet name as follows.

 

If any of the sheets in the file to be collected match the “information” entered, all sheets will be collected.

In doing so, of course, the layout of the sheets must be the same.

(The sheet name should be a partial match with “information”. Let me know if you have a need for an exact match to be collected)

 

7. Enter “Target file name (partial match)” on the Setting sheet.

A new function was added in Ver. 1.2. Assuming a case where many files are stored, a function to collect files when some file names match has been added.

The search is performed with partial matches, so that only files that contain

Only file names that include Mixed “Laotzu” will be displayed, such as Mixed Laotzu_Personnel_Information.xlsx or Personnel_Mixed_Laotzu_Information.xlsx.

 


If blank, of course, all files will be collected. Please use differently depending on the situation.

 

8. Enter the “Password for sheet protection” and “Password for book protection” on the Setting sheet.

If the file itself has a password set, enter the password in cell C10.

If the sheet has a password for protection, enter the password in cell C9.

 

In most cases, there is no password set, so please consider this as an optional function.

 

9. Click the “File Collection Run” button.

The setting is completed up to 8.5.

Now, click the “File Collection Run” button. Execution will begin immediately.

 

While the file is running, the collection status is displayed in the lower left corner.

 

Check the “Result” and “Merge” sheets after the execution is completed.

When collection is complete, a message box will appear as shown below.

 

In this example, I placed the file collection tool in the same folder as the three files to be imported and executed. This tool, which is executing a macro, is excluded from the importing process as it is not a target of the macro.

 

Below is the contents of the “Result” sheet. You can see that the out-of-scope files are grayed out.

 

The explanation of each column is as follows.

 

  • Copy start row: Start row when transcribing to the “Merge” sheet
  • Rows collected: Number of rows copied from the source file
  • Source file copied: Displays the path of the file from which the data was collected (click to open that file)
  • Sheet name: Shows the name of the sheet taken from the source file copied.
  • Copy to (link to Merge sheet): Link to the first line pasted to the “Merge” sheet

 

After execution, it should go to the “Result” sheet.

Let’s go to the “Merge” sheet. You can see that some column widths have been reduced.

 

 

Column widths are not automatically changed when copied. Therefore, please adjust them manually.

 

Common Errors

If a file with the same name “Merge File Tool” is stored in the target folder for collection, the error is caused by the same file name not being openable, which is common in Excel.

 

In the end

If you use “Split file tool”, it is better to use this tool “Merge file tool”. Because, after you split the file, you must collect the devided files.

Please remember the file exists.

 

Update history

Version Contents Updated date
1.0 First eddition 2020/12/08
1.1 Changed alert to hidden because a message was being displayed when a file to be collected had an external link. 2021/03/17
1.2 Added option to search for partial file names in files to be collected. 2021/04/29
2.0 Changed to collect all sheets for a given sheet name if multiple sheet names in the collection file match. 2021/12/10
2.1 Fixed a bug that prevented importing hidden sheets. 2023/01/04
2.2

Fixed the error that worksheet which is to be divided makes error when the worksheet is protected without no having password.

2023/07/12