topic RE: Vlookup Involving two Excels in Product Forum

Vlookup Involving two Excels

RahulRamesh — Thu, 20 May 2021 08:32:00 GMT

Hi,
Iam having two Excels Countries.xlsx and Capitals.xlsx
I wanted to apply Vlookup formula in countries excel inorder to get capitals in a new column.
Formula applied : " =VLOOKUP(Table1[@Country],[capitals.xlsx]Sheet1!$A$1:$B$201,2,0) "

When run logic in Blueprism Iam getting a file selection page( Attached as image) to select the excel required for the Vlookup.

When navigated and selected the excel manually the Bot is continuing to apply the formula and getting ended.

Please let me know is there any other way of doing this process!

Thanks in Advance..

------------------------------
Rahul Ramesh
------------------------------

RE: Vlookup Involving two Excels

michaeloneil — Thu, 20 May 2021 09:44:00 GMT

Hi Rahul

This is due to excel trying to auto update links when you apply a new link to an external file. Before inserting your new link turn off auto links function, this will require a new vb action in your excel vbo. Below is the code you need to add in, Auto_Updates, Handle and Workbook will be your input parameters and success and message output. You might also need to turn off auto calculate as well but try this first.

Dim wb As Object
Try

wb = GetWorkbook(Handle, Workbook)
wb.Activate()

wb.UpdateRemoteReferences = Auto_Updates

Success = True

Catch e As Exception
Success = False
Message = e.Message
Finally
wb = Nothing
End Try

------------------------------
Michael ONeil
Technical Lead developer
Everis Consultancy
Europe/London
------------------------------

RE: Vlookup Involving two Excels

RahulRamesh — Thu, 20 May 2021 13:23:00 GMT

hello @Michael ONeil
Attaching the process flow iam using for Vlookup.
create instance>open workbook>show>activate the sheet>setting the vlookup formula at the cell...
So at which point I should be adding this action you shared? Will it be affecting the Countries Excel in which I need to write the vlookup?

Regards,

------------------------------
Rahul Ramesh
------------------------------

RE: Vlookup Involving two Excels

michaeloneil — Thu, 20 May 2021 13:33:00 GMT

Hi

You can add this action anywhere in your flow before you insert the new vlookup formula. Once you add the action to your excel vbo you can reuse this for any excel workbook you want.

------------------------------
Michael ONeil
Technical Lead developer
Everis Consultancy
Europe/London
------------------------------

RE: Vlookup Involving two Excels

EmersonF — Thu, 20 May 2021 13:44:00 GMT

Make sure the files are in the same directory, if not, add the respective paths before referencing the table

------------------------------
Emerson Ferreira
Sr Business Analyst
Avanade Brasil
+55 (081) 98886-9544
------------------------------

RE: Vlookup Involving two Excels

RahulRamesh — Thu, 20 May 2021 14:22:00 GMT

Hello,
Thanks for your suggestions. The issue has been resolved. Have added two open excel action. Then it worked!.

Thank you.

------------------------------
Rahul Ramesh
------------------------------

RE: Vlookup Involving two Excels

EmersonF — Thu, 20 May 2021 14:34:00 GMT

Perfect! Good Development!

------------------------------
Emerson Ferreira
Sr Business Analyst
Avanade Brasil
+55 (081) 98886-9544
------------------------------