@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 🚀
