How to pass vlookup formula's excel sheet name and spreadsheetname from config

Help Activities
, ,
1

Can anyone help me in writing the below formula from Uipath using config file
Here Input file path varies from the way excel reads the path. How can I resolve it?

Input File Path C:\UiPath_Project\capital-grant-automation-phase-9-controls\Data\Input\IL User inputes for Grant automation system Development.xlsx

Formula :-
“=IF(XLOOKUP(A115,‘C:\UiPath_Project\capital-grant-automation-phase-9-controls\Data\Input/[IL User inputes for Grant automation system Development.xlsx]Allowed Cubes’!$B$7:$B$21,‘C:\UiPath_Project\capital-grant-automation-phase-9-controls\Data\Input/[IL User inputes for Grant automation system Development.xlsx]Allowed Cubes’!$B$7:$B$21,”“Disallowed cube”“)=”“Disallowed cube”“, ““Disallowed cube””, ““Allow Cube””)”

1 Like
2

Hi @Shilpa_Mohanty ,

Just sharing my thought. Not sure whether will it work or not.

we cannot store the values in config XLS with equal to symbol since it will consider as formula in config itself. So we can use the entire value without equal to like below in one of the row in config value. Thanks.

ExcelFormulaKey →

IF(XLOOKUP(A115,‘C:\UiPath_Project\capital-grant-automation-phase-9-controls\Data\Input/[IL User inputes for Grant automation system Development.xlsx]Allowed Cubes’!$B$7:$B$21,‘C:\UiPath_Project\capital-grant-automation-phase-9-controls\Data\Input/[IL User inputes for Grant automation system Development.xlsx]Allowed Cubes’!$B$7:$B$21,”“Disallowed cube”“)=”“Disallowed cube”", ““Disallowed cube””, ““Allow Cube””)

To access this value in work flow should be

“=”+Config (“ExcelFormulaKey”).tostring

3

Hello @kirankumar.mahanthi1

Here A115, B7 and B21 are based on calculation
So i cant pass from config the whole formula

B7 and b21 are dt.rows.count and a115 is last row of datatable+5

4

Hi @Shilpa_Mohanty ,

In this case the best practice to keep the formula string in text file and use below steps to retrieve it as dynamic way to enter the data into excel spreadsheet.

  1. Place the below expression in a text file and place it under your data folder of the project.

=IF(XLOOKUP(A{0},‘{1}/[{2}]Allowed Cubes’!$B${3}:$B${4},‘{1}/[{2}]Allowed Cubes’!$B${3}:$B${4},”“Disallowed cube”“)=”“Disallowed cube”", ““Disallowed cube””, ““Allow Cube””)

For understanding:

{0} - last row of datatable+5 - eg: A115

{1} - Your folder path

{2} - Your excel file name

{3} - DT Row count 1 - eg: B7

{4} - DT Row count 2- eg: B21

  1. Use read text file activity and read the above expression into string variable.

  2. Use the below string format function to modify the formula with dynamic values like Data table row count, folder path, Excel file path and ast row of datatable+5

String.Format(Readtextoutput, LastrowofDT,YourFOlderPath,YourexcelFileName,DTrowcount1,DTrowcount2)

You can find out the excel file name from folder by using Directory.GetFiles(“YourfolderPath”,“YourSearchtext”)

Please try the above method and let us know. thanks.

5

This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.