Combine Excel Files in Power Automate for Desktop

Intro

The finance department of Alfa Juliett needs your help combining multiple Excel files into one.

What you will learn

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

Client Name

Alfa Juliett (finance department)

Process Name

CombineExcel

Description

The finance department of Alfa Juliett wants a Power Automate for desktop flow, that:

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