Skip to main content

Notifications

Power Automate - Building Flows
Suggested answer

Help with Copying All Rows from All Worksheets in and Excel Workbook to the 1st Sheet

(0) ShareShare
ReportReport
Posted on by
Hello,
 
I have been working on trying to automate copying all rows from all sheets in my Excel Workbook to one sheet (ActiveSheet). I was able to get the flow to copy the data but it would only copy it to one row in ActiveSheet and then overwrite it with the next data copied over.  
 
I want the data to be copied to Row 2 (LastRow) on ActiveSheet and then after that copy is completed I want to add 1 to LastRow (making it 3) so that the next row does not overwrite to the previous.
 
This is the flow I have, it ran for 12 hours, I was so hopeful but then there was nothing copied into my ActiveSheet.  All of my Variables are being set and it looks like it is progressing correctly but I am not sure what I am missing to get the data to copy over.
 
Any help or advice would be wonderful! Thank you in advance
 
 
  • Suggested answer
    Nived_Nambiar Profile Picture
    Nived_Nambiar 17,059 on at
    Help with Copying All Rows from All Worksheets in and Excel Workbook to the 1st Sheet
    Hi @CU17011515-0​​​​​​​
     
     
    Try this flow - seems like your flow has some issues 
     
    I have done in this way - merge all rows from all sheets except for final sheet and then append to excel file.
     
    SET InputFilePath TO $'''%''%'''
    SET index TO 0
    Excel.LaunchExcel.LaunchAndOpenUnderExistingProcess Path: InputFilePath Visible: False ReadOnly: False Instance=> ExcelInstance
    Excel.GetAllWorksheets Instance: ExcelInstance Worksheets=> SheetNames
    LOOP FOREACH sheet IN SheetNames
        IF sheet <> SheetName THEN
            Excel.SetActiveWorksheet.ActivateWorksheetByName Instance: ExcelInstance Name: sheet
            Excel.ReadFromExcel.ReadAllCells Instance: ExcelInstance ReadAsText: False FirstLineIsHeader: True RangeValue=> ExcelData
        END
        IF index = 0 THEN
            SET dtFinal TO { ^ExcelData.Columns }
        END
        Variables.MergeDataTables FirstDataTable: dtFinal SecondDataTable: ExcelData MergeMode: Variables.MergeMode.AddExtraColumns
        Variables.IncreaseVariable Value: index IncrementValue: 1
    END
    Excel.SetActiveWorksheet.ActivateWorksheetByName Instance: ExcelInstance Name: SheetName
    Excel.ReadFromExcel.ReadAllCells Instance: ExcelInstance ReadAsText: False FirstLineIsHeader: True RangeValue=> ExcelData
    Excel.WriteToExcel.WriteCell Instance: ExcelInstance Value: dtFinal Column: $'''A''' Row: ExcelData.RowsCount + 2
    Excel.CloseExcel.Close Instance: ExcelInstance
    
     
    here there is a sheetname variable - where we have to pass the sheet to which data need to added.
     

    Thanks & Regards,
    Nived N

    Stay connected:
    LinkedIn | YouTube | Blogs

    Was this answer helpful?
    If yes, please mark it as the solution by selecting the checkbox in the discussion thread.
    Your feedback motivates me to keep contributing. Thank you!

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

Microsoft Kickstarter Events…

Register for Microsoft Kickstarter Events…

Announcing Our 2025 Season 1 Super Users!

A new season of Super Users has arrived, and we are so grateful for the daily…

Announcing Forum Attachment Improvements!

We're excited to announce that attachments for replies in forums and improved…

Leaderboard

#1
WarrenBelz Profile Picture

WarrenBelz 145,475

#2
RandyHayes Profile Picture

RandyHayes 76,287

#3
Pstork1 Profile Picture

Pstork1 64,767

Leaderboard