Copying values from excel to Word template

Help
1

I have an excel file with first name, D.O.B, last name and I have a word letter template, like so.

 Dear 
 Regards,
 rpa_monkey

I’m trying to insert the First name after the “Dear” Then insert the "D.O.B in the next line.
The following is the approach I’ve taken to solving this.

  • I read the excel file in
  • Add the information from excel to a data table.
  • Then iterate through the data table using a For each loop.
  • Get the Firstname and D.O.B
  • Then I open the word document in word application scope
  • Use replace to Replace “Dear” with "Dear " + FirstName &vbNewLine & D.O.B
  • Then export it as a .pdf

This works for the first iteration, but after that, the names get longer. ie.

It goes from Dear John → Dear John Sam → Dear John Sam Mary etc. etc. How can I make sure, it’s replacing The whole line and not just adding on to the existing one?

2

Make sure you reset the FirstName variable in your workflow before re-assigning it to the new name. A simple assign activity where you assign “” will do.

4

I did that though, but that didn’t fix the issue, I think what’s happening is, every time it replaces the text, the document is saved, next time it reads in the document, it has the name from previous iteration in there, so the new name gets added next to it.

5

I see! You should save each PDF with a unique name.

Alternatively, you could read all of the text from the word document and split it by a space or comma or something into an array. Then identify which part of the array contains the “Dear x” you would like to replace, and replace the text rather than inserting after a certain word.

6

The .pdf’s are saved with unique names, but since they are all generated from the same Wordfile, it doesn’t make any difference.

Alternatively, you could read all of the text from the word document and split it by a space or comma or something into an array. Then identify which part of the array contains the “Dear x” you would like to replace, and replace the text rather than inserting after a certain word.

Currently that’s the approach I’m doing, but it seems quite complex for something simple. Which is why I was wondering if there is another way to go about it.

7

Could you make a copy of the wordfile before you add in the details and then delete the copy after the PDF has been created?

1 Like
8

Since there isn’t an option to not save the file like there is with excel application scope, you could do a replace text activity instead.

In your template, have a standard or word that comes after “Dear”.

Then, create 2 string variables: oldName and newName or something similar

oldName = the standard word after “dear” in the template.

In a for each loop, go through all the names from the excel file and replace oldName with the excel file’s newName, then export as a .pdf

After the loop, change newName back to oldName, so the template will be ready again the next time the robot runs

9

I ended up taking this approach, I made a new folder called output and for each name I make a copy of the file, with the “overwrite” option enabled. So I get a new file for each of them. Add the details → export to pdf.

Thanks @KEntwistle :slight_smile:

10

can you help in similar issue i m facing problem while doing word application scope after doing excel application scope and getting machine read that data i m unable to publish in a format similar to u i.e unable to get data for particular person its publishing whole record help asap!