topic Re: Vlook up in Excel in Digital Exchange

Vlook up in Excel

poojagupta83 — Mon, 10 Nov 2025 16:48:16 GMT

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

Re: Vlook up in Excel

SayeedBinAbdullah — Mon, 10 Nov 2025 17:39:37 GMT

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

Re: Vlook up in Excel

poojagupta83 — Tue, 11 Nov 2025 15:05:46 GMT

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.

Re: Vlook up in Excel

SayeedBinAbdullah — Wed, 12 Nov 2025 06:45:56 GMT

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

Re: Vlook up in Excel

faheemsd — Wed, 12 Nov 2025 06:46:44 GMT

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

Re: Vlook up in Excel

poojagupta83 — Wed, 12 Nov 2025 11:48:27 GMT

Hi Faheem,

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

Re: Vlook up in Excel

SayeedBinAbdullah — Wed, 12 Nov 2025 11:55:34 GMT

@poojagupta83

Try the second approach which i have mentioned.

Best Regards,
Sayeed Bin Abdullah

Re: Vlook up in Excel

poojagupta83 — Fri, 14 Nov 2025 12:33:22 GMT

That seems to be working fine. Thanks.