Vlookup Involving two Excels

[RahulRamesh]

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
------------------------------

[michaeloneil]

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
------------------------------

[RahulRamesh]

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
------------------------------

[michaeloneil]

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
------------------------------

[EmersonF]

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
------------------------------

[RahulRamesh · Accepted solution]

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

Thank you.

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

[EmersonF]

Perfect! Good Development!

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