Update Excel Rows

[DN1]

Hi all,

I am new to PAD. Please help me update the Excel sheet. For example, the sheet contains employee names and employee numbers. The correct format for employee numbers should be 'ID93434343', where it starts with 'ID9' followed by a 7-digit number. Some rows are correct, but others do not start with 'ID9' or 'ID'. Please guide me on how to resolve this issue.

[NathanAlvares24 · Accepted solution]

Hi @DN1 !

Please try out this flow.

Output:

Before:

After:

Code (just copy paste into your flow):

Excel.LaunchExcel.LaunchAndOpenUnderExistingProcess Path: $'''C:\\Users\\Username\\Desktop\\EmpDetails.xlsx''' Visible: True ReadOnly: False Instance=> ExcelInstance
Excel.ReadFromExcel.ReadAllCells Instance: ExcelInstance ReadAsText: False FirstLineIsHeader: True RangeValue=> ExcelData
SET nCounter TO 2
LOOP FOREACH CurrentItem IN ExcelData
    IF (NotStartsWith(CurrentItem[1], 'ID9', True) AND CurrentItem[1].Length = 7) = $'''True''' THEN
        Text.Pad Text: CurrentItem[1] PadPosition: Text.PadPosition.Left PaddingText: $'''ID9''' TotalLength: 10 PaddedText=> PaddedText
        Excel.WriteToExcel.WriteCell Instance: ExcelInstance Value: PaddedText Column: $'''B''' Row: nCounter
        Variables.IncreaseVariable Value: nCounter IncrementValue: 1
    ELSE IF CurrentItem[1].Length = 8 THEN
        Text.Pad Text: CurrentItem[1] PadPosition: Text.PadPosition.Left PaddingText: $'''ID''' TotalLength: 10 PaddedText=> PaddedText
        Excel.WriteToExcel.WriteCell Instance: ExcelInstance Value: PaddedText Column: $'''B''' Row: nCounter
        Variables.IncreaseVariable Value: nCounter IncrementValue: 1
    ELSE
        Variables.IncreaseVariable Value: nCounter IncrementValue: 1
    END
END
Excel.CloseExcel.CloseAndSave Instance: ExcelInstance

I hope this helps.

[Deenuji]

@DN1 

Please follow the below approach:

Please modify excel path as per your file and folder name.

1. **Launch Excel**:
- Launch and open the Excel file `C:\\Boot\\EmpDetails (1).xlsx` with visibility enabled.

2. **Read Excel Data**:
- Read all cells from the Excel file into a data table, using the first row as headers.

3. **Initialize Counter**:
- Set a counter variable to `0` for tracking row indices.

4. **Loop Through Data Table**:
- Loop through each row in the data table.

5. **Modify Employee Number (7 characters)**:
- If the employee number is 7 characters long and does not start with "ID", prepend "ID9".

6. **Modify Employee Number (8 characters)**:
- If the employee number is 8 characters long and does not start with "ID", prepend "ID".

7. **Increment Counter**:
- Increment the counter variable at the end of each loop iteration.

8. **Launch New Excel Instance**:
- Launch a new Excel instance with visibility enabled.

9. **Write Modified Data**:
- Write the modified data table to the new Excel instance, including column headers.

10. **Save Updated Excel**:
- Save the updated Excel file as `C:\\Boot\\UpdatedExcel.xlsx`.

Code:

Excel.LaunchExcel.LaunchAndOpenUnderExistingProcess Path: $'''C:\\Boot\\EmpDetails (1).xlsx''' Visible: True ReadOnly: False Instance=> ExcelInstance
Excel.ReadFromExcel.ReadAllCells Instance: ExcelInstance ReadAsText: False FirstLineIsHeader: True RangeValue=> EmployeeExcel
SET Counter TO 0
LOOP FOREACH CurrentEmployee IN EmployeeExcel
    IF (NotStartsWith(CurrentEmployee['Employee Number'], $'''ID''', True) AND CurrentEmployee['Employee Number'].Length = 7) = True THEN
        Variables.ModifyDataTableItem DataTable: EmployeeExcel ColumnNameOrIndex: 1 RowIndex: Counter Value: $'''ID9%CurrentEmployee['Employee Number']%'''
    END
    IF (NotStartsWith(CurrentEmployee['Employee Number'], $'''ID''', True) AND CurrentEmployee['Employee Number'].Length = 😎 = True THEN
        Variables.ModifyDataTableItem DataTable: EmployeeExcel ColumnNameOrIndex: 1 RowIndex: Counter Value: $'''ID%CurrentEmployee['Employee Number']%'''
    END
    SET Counter TO Counter + 1
END
Excel.LaunchExcel.LaunchUnderExistingProcess Visible: True Instance=> ExcelInstance1
Excel.WriteToExcel.WriteCell Instance: ExcelInstance1 Value: EmployeeExcel.ColumnHeadersRow Column: $'''A''' Row: 0
Excel.WriteToExcel.WriteCell Instance: ExcelInstance1 Value: EmployeeExcel Column: $'''A''' Row: 1
Excel.SaveExcel.SaveAs Instance: ExcelInstance1 DocumentFormat: Excel.ExcelFormat.FromExtension DocumentPath: $'''C:\\Boot\\UpdatedExcel.xlsx'''

How to copy/paste above code into your Power automate desktop flow?

Thanks,
Deenuji Loganathan 👩‍💻
Automation Evangelist 🤖
Follow me on LinkedIn 👥

-------------------------------------------------------------------------------------------------------------
If I've helped solve your query, kindly mark my response as the solution ✔ and give it a thumbs up!👍 Your feedback supports future seekers 🚀

[DN1]

Hi @NathanAlvares24 ,Thank you for your response. It is really helpful. In my folder i have multiple pdf files.  The PAD is extracting the pdf file data and writing the data into excel sheet and reads the excel sheet and manipulating the data's (adding the prefix). could you please guide me how to create a separate excel sheet for each pdf file data's and save the excel sheet based on the pdf file name which is present in the folder.