Power Platform Community Forum Thread Details

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

Categories:

Building flows

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!