Vlook up in Excel

[poojagupta83]

I am using "Set Cell Value" to send the Vlook up Formula to excel file. When I use the below command, it works.

=VLOOKUP($B3,'\\USAZGEMS1\BluePrism\Dev\Processes\ESC\GPC Payroll Manager Approval\Case Work\Input\[Workday Report.xlsx]Sheet1'!$A$2:$F$32681,3,FALSE)

But I need to provide have a dynamic value for file path which is getting calculated within the process. That's why I need to pass a data item here. But when I do that I get "0x800a03ec excel error". I understand Blue Prism is unable to convert that data item to a value while writing as a formula. Need help to find a way to use dynamic file path here.

"=VLOOKUP($B3,'[Workday File Path]Sheet1'!$A$2:$F$[Workday Row Count],3,FALSE)"

[SayeedBinAbdullah · Accepted solution]

Hi @poojagupta83 

There will be a slight correction in the value which you are passing,

As you are not using "&" in your expression the data item is being considered as text, try with below expression.

"=VLOOKUP($B3,'"&[Workday File Path]&"Sheet1'!$A$2:$F$[Workday Row Count],3,FALSE)"

Best Regards,
Sayeed Bin Abdullah

[poojagupta83]

Hi Sayeed,

Thanks for providing response. It fixed the formula issue for me.

But I am getting another issue now. It seems that BOT is trying to open the file from window explorer while writing the formula. This is causing BOT going to wait forever. I have attached process flow for your reference.

[SayeedBinAbdullah]

Hi Pooja,

This issue occurs because the Excel file located in the shared folder is not open. When you apply a VLOOKUP that references this file, Windows Explorer attempts to open it automatically.

If both Excel files are already open and you’re still encountering the issue, try copying the required worksheet from the shared location workbook into the Excel file where you’re applying the formula. This way, all the data will be in a single workbook, and the problem will no longer occur.

Best Regards,
Sayeed Bin Abdullah

[faheemsd]

@poojagupta83 

Could you please try below approach?

Open the source workbook before inserting the formula

Before you write the formula:

Use the MS Excel VBO → Open Workbook action.

Open the file referenced by [Workday File Path].

Then write your formula to the target cell.

(Optional) You can close the source workbook after formula evaluation.

[poojagupta83]

Hi Faheem,

Thats the approach I have adopted. But still getting error. Please have a look at below process flow.

[SayeedBinAbdullah · Accepted solution]

@poojagupta83 

Try the second approach which i have mentioned.

Best Regards,
Sayeed Bin Abdullah

[poojagupta83]

That seems to be working fine. Thanks.