Replace Values in Word using values from Excel.

[CharlieAndres]

Hi All,

I have a word document (picture 1) and I am trying to replace the highlighted text with the cell value from the excel.

We were able to pull the excel in as a data collection but we are having trouble using the Microsoft Word VBO in order to take specific cell values and replace them in the word. Essentially the full words document is 72 pages of both structured (tables) and unstructured words and we are looking to find and replace with values in the excel for some of the words.

Please let me know if you have any suggestions!

 

[LeoLugo]

I had the file open so I just attached to the open instance of Word. I did the following:
MS Word VBO->Find Text - Searched for "[Friend]" which highlighted the found text.
MS Word VBO->Type Text - Replaced the found text with the friends name.
See screenshots. Not sure if this will be the best way to solve your problem since I don't know the full scope of your problem but this is a suggestion.

 

[Radiopolaco]

Hi LeoLugo, how about replacing a text inside a text box? Using the find action, does not work :/

 

[swapnalimehetre]

Hi,I am facing issue for replacing the words in drafted letter format using blue prism.Please find the attached excel and MS word letter format.
Please let me know the solution for it and how we can use replace function in this scenario.

Thanks
Swapnali

 

[LeoLugo]

If your document is just a regular document the Find action should work I have no idea why it wouldn't work for you.

 

[Radiopolaco]

I fixed the issue of the text box, by creating a new code stage to select the text box first

 

[Pete_L]

Hi,I am facing issue for replacing the words in drafted letter format using blue prism.Please find the attached excel and MS word letter format.
Please let me know the solution for it and how we can use replace function in this scenario.

Thanks
Swapnali

Find and Replace in Word, as suggested by others in this thread, should work, but there is a more elegant option that I will describe for you that uses built-in Word functionality. Once set up, this option is much faster than opening the letter multiple times and doing Find/Replace on each one.

Your screen shots reveal that your Excel file contains the data for each of the fields that you want to replace in the MS Word letter template. These are exactly what you need to run a Mail Merge in Word. Do some research on Mail Merge, as I've found that not many people are aware of it. I use it in 2 automations quite successfully, generating thousands of letters weekly. I will give you an overview of the Mail Merge process and then you can try it after doing your research on the functionality.

Mail Merge takes a "Recipient List" containing data for all of the "Merge Fields" found in the letter template, and replaces those Merge Fields with the data found in each row of the Recipient List. Each row in the Recipient List (Excel file) must contain the replacement data for each of the Merge Fields in the letter. You will need to first define the Merge Fields in the Word letter template. They should be the exact names of the columns in the Excel file. Word Mail Merge help will show you how to define Merge Fields. Once you have all this in place (and it looks like you're very close to that state), have your automation open Word and do the following:

1. Open the letter template document containing the Merge Fields.
2. Click the Mailings button on the Ribbon
3. Click the Start Mail Merge button on the Ribbon
4. Select Letters from the dropdown
5. Click the Select Recipients button on the Ribbon
6. Select Use an Existing List from the dropdown. Navigate to the folder containing your Excel file, select the file, and click Open.
7. In the Select Table dialog box, tick the worksheet tab that contains the input data.
8. Click the Finish & Merge button on the Ribbon. You will be able to select a printer at this point. One letter will be produced for each row in the Recipients List Excel file.
   1. In my automations, I select the Adobe PDF printer and enter the output filename and path. I get 1 PDF file containing a separate page for each letter generated. That way, the entire batch of letters can be sent to the printer in one go.

This is quite a bit to set up, both in terms of BP process and object as well as the Merge document, but once done it can be used as a model for future automations where you have to do mail merges. It is faster than Find/Replace because you only have to open the letter template document once, click some buttons, and let it run. It will generate all the letters in one step.

I hope this helps. Good luck!

 

[swapnalimehetre]

Find and Replace in Word, as suggested by others in this thread, should work, but there is a more elegant option that I will describe for you that uses built-in Word functionality. Once set up, this option is much faster than opening the letter multiple times and doing Find/Replace on each one.

Your screen shots reveal that your Excel file contains the data for each of the fields that you want to replace in the MS Word letter template. These are exactly what you need to run a Mail Merge in Word. Do some research on Mail Merge, as I've found that not many people are aware of it. I use it in 2 automations quite successfully, generating thousands of letters weekly. I will give you an overview of the Mail Merge process and then you can try it after doing your research on the functionality.

Mail Merge takes a "Recipient List" containing data for all of the "Merge Fields" found in the letter template, and replaces those Merge Fields with the data found in each row of the Recipient List. Each row in the Recipient List (Excel file) must contain the replacement data for each of the Merge Fields in the letter. You will need to first define the Merge Fields in the Word letter template. They should be the exact names of the columns in the Excel file. Word Mail Merge help will show you how to define Merge Fields. Once you have all this in place (and it looks like you're very close to that state), have your automation open Word and do the following:

1. Open the letter template document containing the Merge Fields.
2. Click the Mailings button on the Ribbon
3. Click the Start Mail Merge button on the Ribbon
4. Select Letters from the dropdown
5. Click the Select Recipients button on the Ribbon
6. Select Use an Existing List from the dropdown. Navigate to the folder containing your Excel file, select the file, and click Open.
7. In the Select Table dialog box, tick the worksheet tab that contains the input data.
8. Click the Finish & Merge button on the Ribbon. You will be able to select a printer at this point. One letter will be produced for each row in the Recipients List Excel file.
   1. In my automations, I select the Adobe PDF printer and enter the output filename and path. I get 1 PDF file containing a separate page for each letter generated. That way, the entire batch of letters can be sent to the printer in one go.

This is quite a bit to set up, both in terms of BP process and object as well as the Merge document, but once done it can be used as a model for future automations where you have to do mail merges. It is faster than Find/Replace because you only have to open the letter template document once, click some buttons, and let it run. It will generate all the letters in one step.

I hope this helps. Good luck!

Thanks Pete for solution.Definatly i will try this one.

 

[swapnalimehetre]

I am able to generate the employees letter in MS word but not able to do formatting of letter like font,alignment ,Date need to be at right hand side of letter,underline some text.Some text should be in middle of letter.Please suggest me the way to do it.

Thanks
Swapnali

 

[Pete_L]

Formatting of the text should be done in the Word letter template file (and the file should be saved) prior to running. If you've used the Mail Merge functionality that I described above, you should be able to format the merge fields as well. Once you format the template once, it will stay formatted unless you change the formatting again at a later date.