How do i update data on Excel based on file name in folder?

Help Studio
1

image
image1291×608 65.7 KB

Hi , i need help on this one.

How do i update the data in Excel accordingly most likely only change the last alphabet letter?

Step 1: UiPath to read the sheet of the Excel according to the file name. For e.g. PACK–> Packing
WIND–>Winder

Step 2: UiPath to update the version of the file by changing the last alphabet of the file name

2

Hi @monicaong0609

Use a For Each Row activity to loop through each row of the DataTable,inside the loop, use an If activity to check if the file name matches the desired name. If the file name matches the desired name, use the Assign activity to update the file name by changing the last alphabet letter.

Example-

string fileName = row["FileName"].ToString();
if (fileName.Contains("PACK"))
{
    fileName = fileName.Substring(0, fileName.Length - 1) + "Q";
}
else if (fileName.Contains("WIND"))
{
    fileName = fileName.Substring(0, fileName.Length - 1) + "X";
}
row["FileName"] = fileName;

}

Thanks!!

3

Hi thanks so much for you speedy reply! but i dont think i understand
image
image

For StrOfFIles is the location of files in the folder how do i add in to the code u jus gave?

4

Try this approach, it is more simpler.

  1. Use the “Excel Application Scope” activity to open the Excel file and use the “Read Range” activity to read the sheet.

  2. To rename the sheet according to the file name, you can use the “Invoke Method” activity and select “worksheet” as the target object. Then choose the method “Rename” and pass the new sheet name as an argument.

worksheet.InvokeMethod(“Rename”, {newSheetName})

To update file name-

Try this-

  1. Use assign activity and take a variable “fileName” of type string and provide value.
    fileName= Path.GetFileNameWithoutExtension(filePath)

  2. Use assign activity and take a new variable “newFileName” of type string and provide value

newFileName As String = fileName.Substring(0, fileName.Length - 1) & newChar & “.xlsx”

File.Move(filePath, Path.Combine(Path.GetDirectoryName(filePath), newFileName))

Thanks!