Tutorial: Adding Information About the Files in a Folder to an Excel File

In this tutorial, we will create an automation that retrieves the name and size of all the files in a folder and adds this information to an Excel file.

We will create a project and start by adding a Use Excel File activity to indicate the Excel file to use. We will then add a For Each File In Folder activity to indicate the folder from which to retrieve file information. Inside For Each File in Folder, we will add two Write Cell activities, one to copy the name, the other to copy the size of each file to two cells in the Project Notebook. We will finish by adding an Append Range activity that appends the two cells to a range in an Excel file after the cells are populated for each file.

Step 1: Set up a project and create an Excel file in the project folder.
1. Create a new blank project using the default settings.
2. Create an Excel file in the project folder and name it files.xlsx.
3. Open the newly created file in Excel, and enter the text File Name in cell A1 and the text File Size in cell B1.
4. Save and close files.xlsx.
Step 2: Add the Excel file to the project and indicate the folder whose files to iterate through.
1. In StudioX, in the Activities panel, select the Excel tab, and then double-click Use Excel File. A Use Excel File activity is added to the Designer panel.
2. In the Use Excel File activity:
  - Click Browse next to the Excel file field, and then browse to and select the file files.xlsx.
  - In the field Reference as enter FilesSizes.
3. In the Activities panel, select the File tab, and then drag the For Each File in Folder activity and drop it inside the Use Excel File activity.
4. In the For Each File in Folder activity, click Browse next to the Folder field, and then browse to and select a folder on your computer which contains files, for example the Desktop.
Step 3: Copy information about the files to the Excel file.
1. In the Activities panel, select the Excel tab, and then drag a Write Cell activity and drop it inside For Each File in Folder.
2. In the Write Cell activity:
  - Click Plus on the right side of the What to write field, and then select CurrentFile > Name to indicate that we want to copy the name of the current file in the iteration.
  - Click Plus on the right side of the Where to write field, and then select Project Notebook (Notes) > Indicate in Excel to open the Project Notebook. In the file, select the A1 cell in the Scratchpad sheet, and click Confirm in the UiPath tab in the Excel ribbon. You have indicated that you want to paste the name of the current file in the iteration to cell A1 in the Scratchpad sheet of the Project Notebook.
3. Add a second Write Cell activity below the first one.
4. In the Write Cell activity:
  - Click Plus on the right side of the What to write field, and then select CurrentFile > Size to indicate that we want to copy the size (in bytes) of the current file in the iteration.
  - Click Plus on the right side of the Where to write field, and then select Project Notebook (Notes) > Indicate in Excel to open the Project Notebook. In the file, select the B1 cell in the Scratchpad sheet, and click Confirm in the UiPath tab in the Excel ribbon. You have indicated that you want to paste the size of the current file in the iteration to cell B1 in the Scratchpad sheet of the FilesSizes Excel file.
5. To make the two Write Cell activities more easily identifiable, edit the name in the upper bar of each one. For example use Write Cell Name for the first activity, and Write Cell Size for the second one.
6. In the Activities panel, drag an Append Range activity and drop it inside For Each File in Folder below the second Write Cell activity.
7. In the Append Range activity:
  - Click Plus on the right side of the Append after range field and then select FilesSizes > Sheet1.
  - Click Plus on the right side of the What to append field, and then select Project Notebook (Notes) > Indicate in Excel to open the file in Excel. In the file, select the A1:B1 cells and click Confirm in the UiPath tab in the Excel ribbon. You have indicated that you want to add the information about the current file in the iteration to columns A and B in the FilesSizes file after the existing data in the file.
8. Click Save in the StudioX ribbon to save the automation, then click Run to execute the automation.
  
  The name and size of each file in the indicated folder are copied one by one to cells A1 and A2 in the Scratchpad sheet of the Project Notebook, and then appended to files.xlsx.
Download example