topic RE: VlookUp with Changing File Name in Product Forum

VlookUp with Changing File Name

AsitabhaDeb — Fri, 01 Jan 2021 06:03:00 GMT

Hi,

I would like to perform VLOOKUP in Blue Prism between two excel file. Out of 2 excel file , name of one file's name keep changing everyday with date.

1st file = Scrap Factor (Name will remain same)
2nd File = DID (Keep Changing every day with date) for e,g DID 31.12.2020, next day DID 1.1.2021.
I have written one syntax but giving me the error mssg.
Please take a look the attachment.

------------------------------
Asitabha Deb
------------------------------

RE: VlookUp with Changing File Name

ewilson — Fri, 01 Jan 2021 15:40:00 GMT

@Asitabha Deb,

The issue is your use a quotes in how you're trying to build your VLOOKUP statement. In Blue Prism, a double quote is treated as the beginning of a string expression. Everything up to the next double quote is part of that string. The second double quote terminates the string and is the end of the expression unless you use the ampersand (&) to concatenate something additional to the expression. Also, if you need to embed double quotes in the string expression itself you have to use the Chr(34) function.

Try the following expression:

"=VLOOKUP(F2,'[" & Chr(34) & "DID " & FormatDateTime(Today(),"dd.MM.yyyy") & ".xlsx" & Chr(34) & "]Sheet1'!$A$2:$D$22,4,0)"

Cheers,

------------------------------
Eric Wilson
Director, Partner Integrations for Digital Exchange
Blue Prism
------------------------------

RE: VlookUp with Changing File Name

AsitabhaDeb — Sun, 03 Jan 2021 04:30:00 GMT

Hi Eric,

Happy New year.
Thanks for your feedback. Its working now but in the destination column all the data are coming as a "#NA".
Can you please tell me why?
When I am doing it manually, its working fine but run through BP its giving me "#NA" result.
Attachment for your ref. You can see in DID column.
I have attached both the VlookUp file for your understanding.

BR/Asitabha

------------------------------
Asitabha Deb
------------------------------

RE: VlookUp with Changing File Name

ewilson — Mon, 04 Jan 2021 00:18:00 GMT

@Asitabha Deb without having access to the underlying Excel's and the BP process there's not a whole lot I can troubleshoot for you on this. This page has a decent explanation of what an #N/A error means in Excel though:

https://exceljet.net/formula/how-to-fix-the-na-error

In a nutshell, it means something is missing or not found.

FWIW - I don't see the value of cell F2 in the lookup table you've pictured or any of the other values in the F column either.

Cheers,

------------------------------
Eric Wilson
Director, Partner Integrations for Digital Exchange
Blue Prism
------------------------------