How to update the workbook path of "Read Range" activity each time we run the automation

[Lorenzo (Emre)]

Greetings,

I’ve created an automation which downloads the Excel file from the local ERP program of a company (the files are named as “Export_CompanyName*****” where all the "*"s are randomly generated numbers) then it reads the downloaded Excel file, and via Vlookup updates the local Excel file which I already have in my pc.

The question is, I’m doing this Vlookup process via Read Range activity but as I have already mentioned above each time I download the corresponding file the name changes, therefore the path is also changing according to the created name, how can I make sure that I’m dealing with the correct file? or is it even possible to do it this way?

Another question that is related with the first one is that for each Excel file total number of rows might change, so while reading the file with Read Range activity I can’t give a proper range like “A2:A1500” how can I include all of the variables that is present in the entire column?

[Srini84 (Srinivas)]

@Lorenzo

You can use Assign activity and write as below

FilesArray = Directory.GetFiles(FolderPath)

Above Function will Get the Files in the Folderpath and store to FilesArray Variable which is of type String

Once done you can use ForEach activity to loop into, So once your automation is completed you can move your downloaded file to other folder. So in the FolderPath there is always one File

Hope this may help you

Thanks

[Lorenzo (Emre)]

@Srini84 Thanks for the answer but I still couldn’t be able to fix my problem. The Problem is that the names of the files are constantly changing so I can’t do the vlookup via any read range or related activities, tried to rename the files to stable ones but that didn’t work either.

Since I’ll be having only one file in the folder path that is assigned to FilesArray is there a way to refer to that? I feel like I am missing something that is crucial here…

[Raluca_Laic]

Hi @Lorenzo ,

Wanted to check if you are initiating the download using Studio(X). If so, you can use a Wait for download activity that allows you to store the file as a variable.

Within the Use Excel File scope, you can extract the Downloaded file’s full name (including the path). For Excel Application Scope, you can leverage something similar.

Hope this helps!

Thanks,
Raluca