Power Platform Community Forum Thread Details

Hello,

I am hoping someone can help me!

I am trying to copy data from one table to a pallet label using PAD. I have managed to create a flow but it does not work if the ID (same as the Pallet Number) is the same as previous so I somehow need to create it so it runs as the below:

- Copy row from instance1, paste into cells on pallet label (instance2) - (complete)

- Create new worksheet, copy and paste the next row. (complete)

- IF row ID is the same as ID on previous row, paste on row below on the pallet sheet (instance2) - (not complete)

- Then create new worksheet and continue (not complete)

Columns A, B, C have to paste in cells D3, D4, D5 in instance2, so that is another complication whilst trying to manage the above rows.

As you can see on the screenshots, some pallets will have more than 1 item, so it needs to go down a row if the ID is the same. (ID is just the Pallet Number without the 'of 4' count).

Any ideas if this is possible??

All help greatly appreciated!!!

Categories:

Power Automate Desktop

Hey Hi,

Please find the solution.

Code:

Please copy this code to your flow.

Excel.LaunchExcel.LaunchAndOpenUnderExistingProcess Path: $'''C:\\Users\\Desktop\\Power Automate Desktop\\Practice\\Input.xlsx''' Visible: True ReadOnly: False Instance=> Input_ExcelInstance
Excel.ReadFromExcel.ReadAllCells Instance: Input_ExcelInstance ReadAsText: False FirstLineIsHeader: True RangeValue=> ExcelData
Excel.LaunchExcel.LaunchAndOpenUnderExistingProcess Path: $'''C:\\Users\\Desktop\\Power Automate Desktop\\Practice\\Template.xlsx''' Visible: True ReadOnly: False Instance=> Template_ExcelInstance
Excel.LaunchExcel.LaunchAndOpenUnderExistingProcess Path: $'''C:\\Users\\Desktop\\Power Automate Desktop\\Practice\\Output.xlsx''' Visible: True ReadOnly: False Instance=> Output_ExcelInstance
SET Count TO 1
LOOP FOREACH CurrentItem IN ExcelData
    Excel.CopyCellsFromExcel.CopyAllCells Instance: Template_ExcelInstance
    IF Count = 1 THEN
        Excel.PasteCellsToExcel.PasteAt Instance: Output_ExcelInstance Column: $'''A''' Row: 1
        Excel.WriteToExcel.WriteCell Instance: Output_ExcelInstance Value: CurrentItem[2] Column: $'''B''' Row: 3
        Excel.WriteToExcel.WriteCell Instance: Output_ExcelInstance Value: CurrentItem[0] Column: $'''B''' Row: 4
        Excel.WriteToExcel.WriteCell Instance: Output_ExcelInstance Value: CurrentItem[1] Column: $'''B''' Row: 5
        Excel.WriteToExcel.WriteCell Instance: Output_ExcelInstance Value: CurrentItem[4] Column: $'''A''' Row: 7
        Excel.WriteToExcel.WriteCell Instance: Output_ExcelInstance Value: CurrentItem[5] Column: $'''B''' Row: 7
        Excel.WriteToExcel.WriteCell Instance: Output_ExcelInstance Value: CurrentItem[6] Column: $'''C''' Row: 7
        Excel.WriteToExcel.WriteCell Instance: Output_ExcelInstance Value: CurrentItem[7] Column: $'''D''' Row: 7
        Excel.WriteToExcel.WriteCell Instance: Output_ExcelInstance Value: CurrentItem[8] Column: $'''E''' Row: 7
    ELSE
        IF CurrentItem[3] = Check THEN
            Excel.GetFirstFreeColumnRow Instance: Output_ExcelInstance FirstFreeColumn=> FirstFreeColumn FirstFreeRow=> FirstFreeRow
            Excel.WriteToExcel.WriteCell Instance: Output_ExcelInstance Value: CurrentItem[4] Column: $'''A''' Row: FirstFreeRow
            Excel.WriteToExcel.WriteCell Instance: Output_ExcelInstance Value: CurrentItem[5] Column: $'''B''' Row: FirstFreeRow
            Excel.WriteToExcel.WriteCell Instance: Output_ExcelInstance Value: CurrentItem[6] Column: $'''C''' Row: FirstFreeRow
            Excel.WriteToExcel.WriteCell Instance: Output_ExcelInstance Value: CurrentItem[7] Column: $'''D''' Row: FirstFreeRow
            Excel.WriteToExcel.WriteCell Instance: Output_ExcelInstance Value: CurrentItem[8] Column: $'''E''' Row: FirstFreeRow
        ELSE
            Excel.CopyCellsFromExcel.CopyAllCells Instance: Template_ExcelInstance
            Excel.AddWorksheet Instance: Output_ExcelInstance Name: $'''Sheet%Count%''' WorksheetPosition: Excel.WorksheetPosition.Last
            Excel.SetActiveWorksheet.ActivateWorksheetByName Instance: Output_ExcelInstance Name: $'''Sheet%Count%'''
            Excel.PasteCellsToExcel.PasteAt Instance: Output_ExcelInstance Column: $'''A''' Row: 1
            Excel.WriteToExcel.WriteCell Instance: Output_ExcelInstance Value: CurrentItem[2] Column: $'''B''' Row: 3
            Excel.WriteToExcel.WriteCell Instance: Output_ExcelInstance Value: CurrentItem[0] Column: $'''B''' Row: 4
            Excel.WriteToExcel.WriteCell Instance: Output_ExcelInstance Value: CurrentItem[1] Column: $'''B''' Row: 5
            Excel.WriteToExcel.WriteCell Instance: Output_ExcelInstance Value: CurrentItem[4] Column: $'''A''' Row: 7
            Excel.WriteToExcel.WriteCell Instance: Output_ExcelInstance Value: CurrentItem[5] Column: $'''B''' Row: 7
            Excel.WriteToExcel.WriteCell Instance: Output_ExcelInstance Value: CurrentItem[6] Column: $'''C''' Row: 7
            Excel.WriteToExcel.WriteCell Instance: Output_ExcelInstance Value: CurrentItem[7] Column: $'''D''' Row: 7
            Excel.WriteToExcel.WriteCell Instance: Output_ExcelInstance Value: CurrentItem[8] Column: $'''E''' Row: 7
        END
    END
    SET Check TO CurrentItem[3]
    Variables.IncreaseVariable Value: Count IncrementValue: 1
END
Excel.CloseExcel.Close Instance: Input_ExcelInstance
Excel.CloseExcel.Close Instance: Template_ExcelInstance
Excel.CloseExcel.CloseAndSaveAs Instance: Output_ExcelInstance DocumentFormat: Excel.ExcelFormat.FromExtension DocumentPath: $'''C:\\Users\\Desktop\\Power Automate Desktop\\Practice\\Output_%Count%'''

(Note:- if you got your solution you can mark as solution and gives kudos)

Thanks & Regards

Vishnu Reddy