How do i update different Excel Sheet tab base on the file name?

[monicaong0609 (mxo)]

Main.xaml (20.2 KB)

hi everyone need some helps here :))
So basically i have some files i would like to update on the excel sheet shown above.
How do i make UiPath read the file name and go the the excel sheet accordingly to update??
The file name and excel tab might not match exactly so what methods i can use ?

[rajanrao1 (Rajan)]

HI
Are you trying to read the files from the directory and updating it one by one ?

[monicaong0609 (mxo)]

yeap tats right

[pratik.pandya]

@monicaong0609
Can you please, Explain What you Want to achieve in this automation?

[Umadevi_Sanjeevi]

Hi @monicaong0609

I have attached input files file1 and file2 . As per your requirement version value updated from last 3 character 1-A.

Find the Xmal.
Output Will be File1

Output Will be File2

UpdateVersion.zip (17.3 KB)
Thanks!

[monicaong0609 (mxo)]

Hiii thank you so much for your help but i can’t seems to open your file.

is it ok if you share screenshot of ur workflow thanks

[Srini84 (Srinivas Kadamati)]

@monicaong0609

I believe you are using older version where it is throwing errors while opening the xaml

Here is the flow you can try in your studio

1. Use Assign activity to get the files from the directory

Here GetFiles is an array variable and YourPath is the path of the folder where you need to read the files

2. Use For Each and Type Argument as String and pass the Get Files, Inside For Each
   Place the excel Application scope and declare a variable for workbook as wb for example, Inside that place For Each and write as wb.getsheets

inside the ForEach you place a IF Condition and write as item.ToString.Equals(“Payment”)
Inside IF Condition Place Read Range and pass the name as “Payment” in SheetName and declare a datatable variable

Now outside the IF Condition Place For Each Row activity and pass the datatable variable

Place the below activities

At last you can place the write range

The above is the whole what is in @Umadevi_Sanjeevi 's xaml has

Hope this will help you

Thanks,
Srini

[Umadevi_Sanjeevi]

Hi @monicaong0609

Find the screenshot

Overall Xaml

Thanks!

[monicaong0609 (mxo)]

hi thank you so much for ur help! but i do not want to create a new excel workbook to update instead i actually want to update on the excel sheet under column “name” base on the file name i downloaded.

[Umadevi_Sanjeevi]

Hi @monicaong0609 ,

You have to update file name and version in the existing excel. for this case find the below screenshots.

Folder:

ouput will be

Step 1 : Read update file. Which you want to update No. , Name, Ver in outputDT
Step 2 : getFiles = get file name with extension using assign assign activity.
Step 3 : Use for each activity
Step 4 : Use If activity to check file name contains “PACK” inside for each
Step 5 : Get sheet name
Step 6 : Get version and update No., Name, Ver to your expected Data table using add data row in outputDT.
Step 7 : Write excel file.

Layout for your referece:

UpdateVersion.zip (29.0 KB)

Thanks!

[monicaong0609 (mxo)]

Hi thank you so much for ur reply!!
But can you show me the code for this ?

[Umadevi_Sanjeevi]

Hi @monicaong0609 ,

Can you check below expression.

getVersion = (item.tostring.Split("-"c)(item.tostring.Split("-"c).Count-2)+"-"+item.tostring.Split("-"c)(item.tostring.Split("-"c).Count-1)).Split("."c)(0)

Add data row
{counter.ToString,item.ToString.Split("."c)(0),getVersion}

Thanks!