In this tutorial, we will create an automation that generates an individual performance review Word document for each member of a team based on a template Word file and data gathered from several Excel and text files. The template file contains placeholder text and bookmarks that can be populated with values using Word activities. The names of the Excel and text files from which to copy data follow a specific pattern that includes the employee name.

We will start by adding a Use Excel File activity to indicate an Excel file with employee information to add to the automation. Inside it, we will add an Excel For Each Row activity to iterate through the rows with employee data. Inside Excel For Each Row, we will add a Copy File activity to copy the template Word file and create separate Word files for each employee. A Use Word File activity will add each document to the automation, and then a series of Replace Text, Set Bookmark Content, and Insert DataTable activities will populate each Word file with the necessary information taken for each employee from an Excel file and a text file.

Step 1: Set up the project and get the necessary files.

1. Create a new blank project using the default settings.
2. Download and extract the archive with the automation project in this tutorial using the button at the bottom of this page. Copy the folder Files to your project folder.
3. Create a folder named Output in your project folder.

Step 2: Add the Excel with employee data to the project and iterate through the rows in the file.

1. In StudioX, click Add activity in the Designer panel, and then find the Use Excel File activity in the search box at the top of the screen and select it. The activity is added to the Designer panel.
2. In the activity:
   • Click Browse next to the Excel file field, and then browse to the Files folder and select the file Employees.xlsx.
   • In the field Reference as, enter EmployeeData.
     You have indicated that you will work with the file Employees.xlsx that is known in your automation as EmployeeData.
3. Click Add activity inside Use Excel File, and then find the Excel For Each Row activity in the search box at the top of the screen and select it. The activity is added to the Designer panel inside the Use Excel File activity.
4. In the Excel For Each Row activity:
   • In the For each text box, replace CurrentRow with Employee.
   • Click Plus on the right side of the In range field, and then select EmployeeData > Sheet1 [Sheet].
     You have indicated that you want to iterate through the rows in Sheet1 from the Employees spreadsheet. Since each row contains information about an employee, the current row in the iteration was renamed Employee to make the information from the row easier to identify later in the automation.

Step 3: Create separate Word files based on the template and add them to the automation

1. Click Add activity inside Excel For Each Row, and then find the Copy File activity in the search box at the top of the screen and select it. The activity is added to the Designer panel.
2. In the Copy File activity:
   • Click Browse next to the From field, and then browse to the Files folder and select the file PerformanceReviewDoc_template.docx.
   • Click Plus on the right side of the To field, and then select Text.
     In the Text Builder:
     a. Enter the text Output\.
     b. Click Plus on the right side of the Text Builder, and then select Employee > Full Name.
     c. Enter the text _PerformanceReview.docx.
     The text should look like this: Output\[Employee]FullName_PerformanceReview.docx.
     d. Click Save in the Text Builder.

You have indicated that for each row in the Employees file you want to copy the template file to a new folder called Output and add the full name of the current employee to the file name using the value in the Full Name column.

3. Click Add activity below the Copy File activity, and then find the Use Word File activity in the search box at the top of the screen and select it. The activity is added to the Designer panel.
4. In the Use Word File activity, click Plus on the right side of the Word file field, and then select Text. Enter the same value as the one in the To field of the Copy File activity Output\[Employee]FullName_PerformanceReview.docx. You can follow the earlier instructions for the Copy File activity, or copy and paste the text from the Text Builder of the To field in the Copy File activity.
   You have indicated that you want to add the performance review Word file for the current employee in the iteration to the automation.

Step 4: Add information from the Excel file with employee data to the Word files

1. Click Add activity inside Use Word File, and then find the Replace Text activity in the search box at the top of the screen and select it. The activity is added to the Designer panel.
2. In the Replace Text activity:
   • Click Plus on the right side of the Search for field, and then select Text. In the Text Builder, enter <<FullName>>.
   • Click Plus on the right side of the Replace with field, and then select Employee > Full Name.

You have indicated that you want to replace the placeholder text <<FullName>> in each Word file with the full name that is copied for each employee from the cell in the Full Name column of the Employees Excel file.

3. Click Add activity below the Replace Text activity, and then find the Set Bookmark Content activity in the search box at the top of the screen and select it. The activity is added to the Designer panel.
4. In the Set Bookmark Content activity:
   • Click Plus on the right side of the Bookmark name field, and then select Text. In the Text Builder, enter DirectManager.
   • Click Plus on the right side of the Bookmark text field, and then select Employee > Direct Manager.

You have indicated that, for each employee, you want to add the name of the direct manager to the Word file by copying the contents of the cell in the Direct Manager column of the Employees Excel file and pasting it at the location of the DirectManager bookmark in the Word file.

5. Repeat steps 3-4 three times to add additional Set Bookmark Content activities that add the full name, location, and title of each employee to the Word files. Configure each activity as follows:
   • Bookmark name FullName, Bookmark text Employee > Full Name
   • Bookmark name Location, Bookmark text Employee > Location
   • Bookmark name PositionTitle, Bookmark text Employee > Position Title

Step 5: Add information from the Excel file with data about projects to the Word files

1. Click Add activity below the last Set Bookmark Content, and then find the Use Excel File activity in the search box at the top of the screen and select it. The activity is added to the Designer panel.
   2 In the Use Excel File activity:
   • Click Plus on the right side of the Excel file field, and then select Text.
     In the Text Builder:
     a. Enter the text Files\.
     b. Click Plus on the right side of the Text Builder, and then select Employee > Full Name.
     c. Enter the text _Projects.xlsx.
     The text should look like this: Files\[Employee]FullName_Projects.xlsx.
     d. Click Save in the Text Builder.
   • Select Template file, then click Browse , and then browse to the Files folder and select one of the Excel files with a file name that ends in _Projects.xlsx, for example Albert Kipling_Projects.xlsx.

You have indicated that, for each employee, you want to add the projects Excel file to the automation. Because the selection for Excel file is a dynamic file path and you want to be able to select data in the activities that use the projects files, you indicated one of the files as a template file.

3. Click Add activity inside the newly added Use Excel File activity, and then find the Insert DataTable activity in the search box at the top of the screen and select it. The activity is added to the Designer panel.
4. In the Insert DataTable activity:
   • Click Plus on the right side of the Table to insert field, and then select Projects > Sheet1 [Sheet] > Table1 [Table].
   • From the Insert relative to drop-down menu, select Bookmark.
   • Click Plus on the right side of the Bookmark to search for field, and then select Text. In the Text Builder, enter Projects.
   • From the Position where to insert drop-down menu, select After.

You have indicated that, for each employee, you want to copy Table1 from the Projects Excel file and paste it in the Word file after the bookmark named Projects.

Step 6: Add information from the text files with manager feedback to the Word files

1. Click Add activity below the newly added Use Excel File activity, and then find the Read Text File activity in the search box at the top of the screen and select it. The activity is added to the Designer panel.
2. In the Read Text File activity:
   • Click Plus on the right side of the File name field, and then select Text.
     In the Text Builder:
     a. Enter the text Files\.
     b. Click Plus on the right side of the Text Builder, and then select Employee > Full Name.
     c. Enter the text _ManagerFeedback.txt.
     The text should look like this: Files\[Employee]FullName_ManagerFeedback.txt.
     d. Click Save in the Text Builder.
   • In the Properties panel, click Plus on the right side of the Content field, and then select Save for Later Use. Enter the name ManagerFeedback, and click OK.

You have indicated that, for each employee, you want to copy the manager feedback from the text file that contains the employee name in the file name and you want to save the feedback for later use in the automation.

3. Click Add activity below the Read Text File activity, and then find the Set Bookmark Content activity in the search box at the top of the screen and select it. The activity is added to the Designer panel.
4. In the Set Bookmark Content activity:
   • Click Plus on the right side of the Bookmark name field, and then select Text. In the Text Builder, enter DirectManagerFeedback.
   • Click Plus on the right side of the Bookmark text field, and then select Use Saved Value > ManagerFeedback.

You have indicated that, for each employee, you want to copy the manager feedback that you saved for later use in the Read Text File activity and paste it in the Word file at the location of the DirectManagerFeedback bookmark.

5. Click Save in the StudioX ribbon to save the automation, then click Run to execute the automation.
   The automation creates separate performance review Word documents for each employee in the Output folder and populates the files with information from the Excel files with employee data and projects information, and from the text files with manager feedback.
   Download example