Tutorial: Filling Out PDF Forms in Acrobat Reader With Data From Excel

In this tutorial, we will create an automation that creates an equipment use agreement PDF form for each new employee at a company. Starting from a template form, the automation creates a form for each employee and populates it with data from an Excel file with information about the equipment received by each employee.

We will start by adding a Create Folder activity to create a folder where to save the PDF files. We will then add a Use Application/Browser activity with a Use Excel File activity inside it. These add the template file opened in Acrobat Reader and the Excel file with equipment data to the automation. In Use Excel File, we will add a For Each Excel Row activity to process the file row by row. In For Each Excel Row, a series of Type Into activities copy the data from cells in each row to the PDF form, and then Keyboard Shortcuts, Click, and Type Into activities perform the tasks required to create a separate PDF in Acrobat Reader for each employee.

1. Step 1: Set up the project and download 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 files EquipmentAgreement.pdf and Employees.xlsx to your project folder, and double-click the PDF file to open it in Acrobat Reader.
   3. In Acrobat Reader, set the Page Layout to Single Page and the Zoom to Default. Do this to ensure that when you run the automation, the entire form is visible on the screen so that the Robot can fill it out. In Acrobat Reader DC, you can change these setting by selecting Edit > Preferences > Page Display.
      Note: Note: If you encounter problems indicating elements in Acrobat Reader, update accessibility options for the document as described in Identifying UI Elements in PDF With Accessibility Options in the Studio Guide.
2. Step 2: Create a folder and add the files to the project.
   1. Click Add activity in the Designer panel, and then find the Create Folder 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 Create Folder activity, click Plus on the right side of the Folder name field, and then select Text. In the Text Builder, enter EquipmentAgreements.

      You have indicated that you want to create a folder named EquipmentAgreements in the project folder.

   3. Click Add activity below Create Folder, and then find the Use Application/Browser 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 Application/Browser activity:

      • Click Indicate Application, and then move the mouse pointer to the Acrobat window where the PDF is opened. When the window is highlighted in blue, click anywhere inside it.

        The Use Application/Browser activity is updated, a screenshot of the window appears inside the activity, and the path to the executable file appears in the Application Path field.

      • Click Plus on the right side of the Application Arguments field, and then select Text. In the Text Builder, enter EquipmentAgreement.pdf.

        You have indicated that you want the automation to open the file EquipmentAgreement.pdf in Adobe Acrobat.

   5. Click Add activity inside Use Application/Browser, 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.
   6. In the Use Excel File activity:
      • Click Browse next to the Excel file field, and then browse to 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. Step 3: Add activities to repeat for each row in the Excel file.
   1. 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.
   2. In the For Each Excel Row activity:
      • Click Plus on the right side of the In range field, and then select EmployeeData > Sheet1 [Sheet]
      • In the field For Each, enter Employee.

        You have indicated that you want to repeat activities for each row in Sheet1 of the EmployeeData file. Because each row contains data about an employee, it will be known in the automation by the name Employee to make it easier to select data from it later in the automation.

   3. Click Add activity inside For Each Excel Row, and then find the Type Into 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 Type Into activity:
      • Click Indicate target on screen, then move the mouse pointer on the First Name field in the EquipmentAgreement.pdf file and, when it is highlighted, click the field. The text field is identified as the target and the First Name label next to it is identified as an anchor. Click Confirm in the Selection Options window to save the selection. An image of the target and anchor is displayed in the Type Into activity.

      • Click Plus on the right side of the Type this field, and then select Employee > First Name.

        You have indicated that for each employee you want to copy the value from the First Name column and paste it in the First Name field in the PDF form.

   5. Add four more Type Into activities below the previous Type Into activity. Using the instructions in steps 3-4, configure the activities to copy the Last Name, Role in Company, Equipment Model, and Equipment Serial No for each employee, and paste them in the corresponding fields in the PDF form.
   6. Add a sixth Type Into activity inside For Each Excel Row after the last Type Into activity.
   7. In the Type Into activity:
      • Click Indicate target on screen, then move the mouse pointer on the Full Name field in the EquipmentAgreement.pdf file and, when it is highlighted, click the field. The text field is identified as the target and the Full Name label next to it is identified as an anchor. Click Confirm in the Selection Options window to save the selection. An image of the target and anchor is displayed in the Type Into activity.

      • Click Plus on the right side of the Type this field, and then select Text.

        In the Text Builder, compose a text that contains the first and last name of each employee from the Excel file separated by a space:

        1. Click Plus on the right side, and then select Employee > First Name.
        2. Add a space.
        3. Click Plus on the right side, and then select Employee > Last Name.
        4. Click Save.

      You have indicated that for each employee you want to copy the values from the First Name and Last Name columns, and paste them in the Full Name field in the PDF form.

      8. Add a seventh Type Into activity inside For Each Excel Row after the last Type Into activity.

   8. Add a seventh Type Into activity inside For Each Excel Row after the last Type Into activity.
   9. In the Type Into activity:
      • Click Indicate target on screen, then move the mouse pointer on the Date field in the EquipmentAgreement.pdf file and, when it is highlighted, click the field. The text field is identified as the target and the Date label next to it is identified as an anchor. Click Confirm in the Selection Options window to save the selection. An image of the target and anchor is displayed in the Type Into activity.

      • Click Plus on the right side of the Type this field, and then select Project Notebook (Notes) > Date [Sheet] > Today [Cell].

      You have indicated that for each employee you want to copy today's date from a cell in the Project Notebook and paste it in the Date field in the PDF form.

   10. Click Add activity below the last Type Into activity inside For Each Excel Row and then find the Keyboard Shortcuts activity in the search box at the top of the screen and select it. The activity is added to the Designer panel.
   11. In the Keyboard Shortcuts activity, click Record Shortcut, and then press the following key combination on your keyboard: CTRL SHIFT S.
       You have indicated that you want to save the PDF file by pressing CTRL+SHIFT+S. When the automation is executed, this combination opens the Save as dialog in Acrobat Reader.
   12. In the Acrobat Reader window where the file is opened, press CTRL SHIFT S on your keyboard to open the Save as dialog.
   13. In StudioX, click Add activity below the Keyboard Shortcuts activity inside For Each Excel Row, and then find the Click activity in the search box at the top of the screen and select it. The activity is added to the Designer panel.
   14. In the Click activity, click Indicate target on screen, then move the mouse pointer on the Choose a different folder button in the Save as dialog and, when it is highlighted, click the button. The button is identified as the target and the Cancel button next to it is identified as an anchor. Click Confirm in the Selection Options window to save the selection. An image of the target and anchor is displayed in the Click activity.
       You have indicated that you want to click Choose a different folder to open the Save as dialog in the file explorer. 15. In the Acrobat Reader window where the file is opened, click Choose a different folder in the Save as dialog.
   15. In the StudioX ribbon, select Project > Open Project Folder to open the project folder in the file explorer, and then copy the full path to the folder from the address (location) bar.
   16. In StudioX, click Add activity below the Click activity inside For Each Excel Row, and then find the Use Application/Browser activity in the search box at the top of the screen and select it. The activity is added to the Designer panel.
   17. In the Use Application/Browser activity, click Indicate Application, and then move the mouse pointer to the Save as dialog in Acrobat. When the dialog box is highlighted in blue, click anywhere inside it.
       The Use Application/Browser activity is updated, a screenshot of the window appears inside the activity, and the path to the executable file appears in the Application path field.
   18. Click Add activity inside the newly added Use Application/Browser, and then find the Type Into activity in the search box at the top of the screen and select it. The activity is added to the Designer panel.

       In the Type Into activity:

       • Click Indicate target on screen, then move the mouse pointer on the File name field in the Save as dialog and, when it is highlighted, click the field. The field is identified as the target and the File name label next to it is identified as an anchor. Click Confirm in the Selection Options window to save the selection. An image of the target and anchor is displayed in the Type Into activity.

       • Click Plus on the right side of the Type this field, and then select Text.

         In the Text Builder:

         1. Paste the path copied in step 16.
         2. Enter \EquipmentAgreements\EquipmentAgreement_.
         3. Click Plus on the right side, and then select Employee > First Name.
         4. Click Plus on the right side, and then select Employee > Last Name.
         5. Click Plus on the right side, and then select Special Keys > Enter.
         6. Click Save.

         The text should look as in the following image:

         
         

         

         
         

       You have indicated that you want to save each PDF file in the EquipmentAgreements folder with a file name that includes the employee name.

       The For Each Excel Row activity should look like in the following image (for a larger view, right-click the image and save it):

       
       

       

       
       
   19. Click Save in the StudioX ribbon to save the automation.
   20. To avoid any issues during execution, make sure no PDF files are open in Adobe Acrobat. and then click Run to execute the automation.

   The values are copied from the Excel file to the PDF form for each employee and each PDF is saved with the employee name in the newly created folder.

   Download example