StudioX User Guide

Last updated Nov 18, 2024

Tutorial: Working With Word Automation

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 a For Each Excel Row activity to iterate through the rows with employee data. Inside For Each Excel Row, we will add a Use Word File activity to add the template document to the automation, and then a series of Replace Text, Set Bookmark Content, and Insert DataTable in Document activities will populate each Word file with the necessary information taken for each employee from an Excel file and a text file. Finally, we will add a Save Word File As activity to save the template document as a separate file for each employee.

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.
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 For Each Excel 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 For Each Excel 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.
Add the template Word file to the automation.
1. Click Add Activity inside For Each Excel Row, 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.
2. In the Use Word File activity:
  - Click Browse next to the Word file field, and then browse to the Files folder and select the file PerformanceReviewDoc_template.docx.
  - Clear the Save changes check box. This ensures that the template file is not saved with the data added for each employee.
    
    You have indicated that you will work with the file PerformanceReviewDoc_template.docx.
Add information from the Excel file with employee data to the Word file.
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 the 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 file. 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
Add information from the Excel file with data about projects to the Word file.
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:
    1. Enter the text Files\.
    2. Click Plus on the right side of the Text Builder, and then select Employee > Full Name.
    3. Enter the text _Projects.xlsx.
      The text should look like this: Files\[Employee]FullName_Projects.xlsx.
    4. Click Save in the Text Builder.
  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 in Document 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 in Document 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.
Add information from the text files with manager feedback to the Word file, and save a separate file for each employee.
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 Filename field, and then select Text.
    
    In the Text Builder:
    1. Enter the text Files\.
    2. Click Plus on the right side of the Text Builder, and then select Employee > Full Name.
    3. Enter the text _ManagerFeedback.txt.
      The text should look like this: Files\[Employee]FullName_ManagerFeedback.txt.
    4. Click Save in the Text Builder.
  - In the Properties panel, click Plus on the right side of the Output to field, and then select Open in Advanced Editor. Enter the expression Saved.Values(Of String)("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 Add Activity below the Set Bookmark Content activity, and then find the Save Word File As activity in the search box at the top of the screen and select it. The activity is added to the Designer panel.
6. In the Save Word File As activity.
  - Click Plus on the right side of the Save as file field, and then select Text.
    
    In the Text Builder:
    1. Enter the text Output\.
    2. Click Plus on the right side of the Text Builder, and then select Employee > Full Name.
    3. Enter the text _PerformanceReview.docx.
      
      The text should look like this: Output\[Employee]FullName_PerformanceReview.docx.
    4. Click Save in the Text Builder.
  - Leave the default selections for the other options.
  You have indicated that you want to save the template file modified with the data of each employee to a new folder called Output. The full name of each employee will be added to the file name using the value in the Full Name column of the Employee Excel file.
7. 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