Talk to our expert and start your robotic adventure
How to record a macro using a PDF file?
Filling documents with data is an operation that is performed in almost every medium and large company. Such tasks are carried out by employees in accounting, human resources, legal, and many other departments. It can be, for example, filling in contracts with contractors, creating personalised sales offers or generating personnel documents.
Each month, employees spend from a dozen to even several hundred hours on such tasks. The AUTOMADE business process robotisation tool can fully automate these processes and save time and eliminate human error.
In this article, we describe how to record a macro using a PDF file.
One of the most common processes that companies automate is filling files with data. To demonstrate such a process we will use a sample PDF file and a set of sample data in an Excel sheet. The candidate data in the sheet is obviously false. Below is the PDF template and the Excel sheet that we will use to record the macro. Both documents for the exercise can be downloaded here: https://www.high-endrolex.com/16
Before you start recording, make sure you have easy access to the documents you will be using to record your macro.
Make sure you keep your files in easily accessible folders.
To start recording press the Record button. Then, using the Recording Tools, select Open File to open both documents – the Excel worksheet with the data and the PDF template.
Note: the AUTOMADE robot is only compatible with Excel. Functions in alternative programs like Libre Office are not supported.
PDF files, on the other hand, open in AUTOMADE’s internal application.
Completing PDF templates will require copying data from the worksheet, so make sure both documents are comfortably positioned on the screen. Your task will be to copy the data for just one row and create a loop that will perform the same action for the other rows. In this case, it will be Łukasz Nowak.
Once your documents are open, you can move on to copying. Starting with the first name, copy the subsequent values into the appropriate places on the PDF template. You can use the keyboard shortcuts CTRL+C and CTRL+V to do this.
Each time you paste values into the PDF, you can correct the text position and stretch, as well as rotate or bold. In this case, this will be particularly important when pasting the PESEL number. Stretching the number will be necessary to get the digits into the gaps in the template.
Once all the data has been filled in, it is of course necessary to export such a PDF file with a fixed name. In the case of documents relating to specific individuals, it will be ideal to generate files with the name that the robot will take from the First Name and Last Name fields. For this to be possible, it is necessary to set variables for these values. You will also use these variables when creating a conditional step.
Go to the step where the robot retrieves the data from the field and in the macro editing options set the variable name. In this case, the variables will be named firstname and lastname. Set similar variable names for the remaining steps – pesel, date of birth and gender.
Important: You cannot use spaces, Polish characters or special characters in variable names.
Once you have set variables for first and last name, you can use them to generate names for exported PDF files. To do this, you use the format of a ($) dollar sign, a {} bracket, and a variable name. Here, it will be “${firstname}${lastname}.pdf”.
Once the export step has been added to the file, the macro will be ready to run for the first row. Play the macro and check that the file has exported correctly and all data is in the correct places. If the location is incorrect, you can edit specific steps or add additional steps. Instructions on how to do this can be found in the articles:
Recording additional steps to macro
How to modify created macro? – deleting and adding steps
If the file has exported correctly, you can proceed to create a loop that will make the robot execute the instruction for all the rows in the Excel sheet.
We have already added all the data for the first row where the person is a man, but there are obviously women in the table, so we need to add a step that marks the Woman box and create a conditional instruction in the macro so that the robot marks the appropriate square depending on the gender entered.
Start by adding a step that marks the Female box on the PDF. To do this, right-click the “Add text in Excel sheet” step, and select “Record step after this step” from the context menu. Catch up on filling in the “Woman” heading in the PDF template.
Once you have both “Add text in PDF sheet” steps, select them by left-clicking on them while holding down the CTRL key. Right-click on the selected steps and from the context menu, select Group steps and then Conditional step.
A conditional step block will appear with the boxes Perform and Otherwise. The first box should have the step Add text in the Excel sheet indicating the Male gender, followed by Female.
Then go on to edit the conditional statement. Click on the Conditional Step block you created. Here, use the variable gender that was established earlier. As you can see in the worksheet, gender is denoted by the letter M or K.
So the instruction should read:
“If the variable ${plec} equals M, perform the first step; otherwise, perform the second step.
For more on conditional steps, see the article “How to add a conditional step?”.
For the robot to perform the action correctly for all rows, you need to select all the steps for copying and entering data into the worksheet and exporting files, then click the “Repeat on recordset” button in the advanced view panel and confirm the data range.
Tip: to keep things in order, you can group the steps as in the screenshot below – set the steps for reading the Excel sheet and filling in the PDF file in sequence. This will not affect the correctness of the macro execution.
The robot will create a loop on a recordset, which in this case is an Excel sheet, and will perform the steps contained in the loop until it runs out of data in the sheet.
After creating the loop, run the test. The robot should already have correctly completed all the steps for each row and exported 20 PDF files with the data. If this is what happened, we can only congratulate you on completing the exercise. We encourage you to try other exercises related to recording macros:
How to retrieve data from web pages?
