2020.12.24
SampleScenarios
Integrate all sheets of multiple Excel files into one file

This is a sample scenario that integrates all sheets of all Excel files in the folder specified by the full path into the new file specified by the full path.
*Please see this precautions upon downloading this sample scenario.

Feature

This sample scenario executes following process.

  • For all Excel files in the specified folder, all sheets are merged into one Excel file and saved as a new file.

WinActor(OR-5200) Modules to use

In this sample scenario, the following WinActor(OR-5200) modules are used.

  • FileList_GetFileList.ums6
  • FileList_CountFiles.ums6
  • Excel_CreateNewFile.ums6
  • Excel_CloseWithoutSaving.ums6
  • Excel_SaveFile.ums6
  • Excel_CountSheets.ums6
  • Excel_SelectSheet.ums6
  • Excel_DuplicateSheetInAnotherBook.ums6

Operating Environment

The operation of this sample scenario works in the following environment.

  • Windows 10
  • WinActor 6.1.0
  • Microsoft Excel 2016

How to use

The usage of this sample scenario is as follows.

Step 1. Load the sample scenario

Extract the downloaded ZIP file and load this sample scenario included in the extracted folder with WinActor.

Step 2. Set the variables required for operation

Update the values ​​required to execute this sample scenario in the variable list.

The following two variables need to be set in advance when executing this sample scenario.

variableList.png

Variable display screen

  • Folder path for files to be integrated

The folder name of the folder that contains the Excel files to be integrated. Specify with the full path.

Example) C: \ winactor \ data

  • File path for an integrated file

File name when saving the integrated Excel file. Specify with the full path.

Example) C: \ winactor \ abc.xlsx

Step 3. Execute the sample scenario

Start executing this sample scenario with WinActor.

This sample scenario ends when the integration of all sheets of all Excel files is completed.

Step 4. After completing the sample scenario

After the execution of this sample scenario is completed, a file is generated in the integrated file path. In the case of the example with variable setting value described in Step 2, "C: \ winactor \ abc.xlsx" is generated.

Trouble shooting

In this sample scenario, an error message will be displayed if a variable is specified incorrectly.

See the error message list for more information.

Explanation

This section describes the implementation techniques used in this sample scenario.

Operating all files in a folder

This sample scenario implements the process to operate all files in the specified folder.

copy_all_file.png

Corresponding part

This sample scenario proceeds as follows.

Item 2 to item 5 are loop processing.

1. Get the number of files in the folder.

2. Create a list of files in the folder.

3. Operate the n-th file in the file list.

4. Count and increase n by one.

5. Loop the processing of item 2 to item 4 for the number of files acquired in item 1.

Therefore, if you change the file in the folder (create new file, delete file, move file, rename file) in the middle of the loop, the scenario will not work as intended. For example, if you need to move a file because you want to move the processed file so that you can see the progress, you can operate all the files in the folder by changing the process as follows.

1. Get the number of files in the folder.

2. Operate the first file in the folder.

3. Move the processed file to another folder.

4. Loop the processing of item 2 to 3 for the number of files acquired in item 1.

In this way, the part of the file operation loop needs to be modified according to the intended use and conditions, so we would appreciate it if you could modify it according to the intended use.

Operating all sheets in an Excel file

This sample scenario implements the process for all sheets in an Excel file.

copy_all_sheet.png

Corresponding part

This sample scenario proceeds as follows.

1. Get the number of sheets in the file.

2. Activate the n-th sheet in the file.

3. Process the cell activated in No. 2.

4. Count and increase n by one.

5. Loop the processing of item 2 to item 4 for the number of files acquired in item 1.

The current WinActor has no library that processes the n-th sheet. It is the way that it activates the n-th sheet at item 2 and operates on the active sheet at item 3 to implement the process to the n-th sheet.

Track changes

VersionDateDetails
1.0 10/27/2020 First edition