Combine Excel Files in Power Automate for Desktop

Intro

What you will learn

• Primarily:
  • Combine .xlsx or .csv files
  • Advanced Excel work
• Secondarily:
  • Work with files and folders

Client Name

Process Name

Description

• Download the example files here
• Dynamically can combine multiple Excel files into one
• Can work with both .xlsx and .csv files

Hints

• Create an Input variable with the path to the folder where your files are
  • %FilePath%
• Get files in folder
  • Retrieve all files from your chosen folder
    • File filter: Only look at e.g., .xlsx files
    • You can choose to include subfolders
• Get current date and time
  • To use it as dynamic folder labeling
• Convert datetime to text
• Create folder
  • To move the files into when done
  • %FilePath%%FormattedDateTime%
• Launch Excel
  • Create a new Excel instance for our final Excel sheet (with our merged sheets)
  • %ExcelInstanceResult%
• For each
  • Now we will iterate through each of our Excel files
  • CurrentFile in Files
• Get first free column/row from Excel worksheet
  • Refer to the ExcelResult sheet, we created to store our results in
  • At the first for each iteration, the result Excel sheet is blank, but as it gets filled up, we need to know where to start writing
• Launch Excel
  • Now it's time to start opening each of the Excel sheets we want to merge
  • %ExcelInstanceCurrent%
• Read from Excel worksheet
  • Pick All available values from worksheet in the drop-down
  • Read all the available values from the CurrentFile
  • %ExcelDataCurrent%
• Write to Excel worksheet
  • Write the %ExcelDataCurrent% to the %ExcelInstanceResult%
  • Write mode: On specified cell
  • Column: A
  • Row: %FirstFreeRowResult%
    • Now we write below our current data
• Close Excel
  • Close the CurrentFile
• Outside the For each, place another close Excel, that closes the result Excel sheet and choose to:
  • Save document as
  • Excel Workbook (.xlsx)
  • %FilePath%Result%FormattedDateTime%
• Get the first free column and first free row of the CurrentFile
  • %ExcenInstanceCurrent%
• Advanced
• Finally we need to add headers to the result
  • Before the For each place a Set variable
    • %FirstRun% = Yes
  • In the For each after the Launch Excel place a Get first free column/row from Excel worksheet, that gets the first free colum/row from the CurrentFile
  • Right after an If, where %FirstRun% = Yes
    • Copy cells from Excel worksheet
      • Start column: A
      • Start row: 1
      • End column: %FirstFreeColumnCurrent - 1%
      • End row: 1
    • Paste cells to Excel worksheet
      • Paste mode: On specified cell
      • Column: A
      • Row: 1
    • Set variable:
      • %FirstRun% = No