Here's a sample flow that would do this for you.
This part gets the data:
This part manipulates it to create a single data table:
Note that when you use Create new data table, it creates it with a single empty row by default, and thus using Delete row from data table is necessary to remove it.
And this part writes it all into a new Excel file:
This is assuming you don't have some template and just need a blank file opened, populated with data and saved.
PAD can write an entire data table via a single Write to Excel worksheet action. But the same also needs to be used to write the headers.
Here's a code snippet you can copy and paste to your PAD designer to get all of these actions:
SET FilePathEntities TO $'''Some file path'''
SET FilePathContracts TO $'''Some other file path'''
SET FilePathOutput TO $'''Where you want your upload file saved'''
Excel.LaunchExcel.LaunchAndOpenUnderExistingProcess Path: FilePathEntities Visible: True ReadOnly: False Instance=> ExcelInstanceEntities
Excel.GetFirstFreeColumnRow Instance: ExcelInstanceEntities FirstFreeColumn=> FirstFreeColumn FirstFreeRow=> FirstFreeRow
Excel.ReadFromExcel.ReadCells Instance: ExcelInstanceEntities StartColumn: $'''A''' StartRow: 1 EndColumn: FirstFreeColumn - 1 EndRow: FirstFreeRow - 1 ReadAsText: False FirstLineIsHeader: True RangeValue=> ExcelDataEntities
Excel.CloseExcel.Close Instance: ExcelInstanceEntities
Excel.LaunchExcel.LaunchAndOpenUnderExistingProcess Path: FilePathContracts Visible: True ReadOnly: False Instance=> ExcelInstanceContracts
Excel.GetFirstFreeColumnRow Instance: ExcelInstanceContracts FirstFreeColumn=> FirstFreeColumn FirstFreeRow=> FirstFreeRow
Excel.ReadFromExcel.ReadCells Instance: ExcelInstanceContracts StartColumn: $'''A''' StartRow: 1 EndColumn: FirstFreeColumn - 1 EndRow: FirstFreeRow - 1 ReadAsText: False FirstLineIsHeader: True RangeValue=> ExcelDataContracts
Excel.CloseExcel.Close Instance: ExcelInstanceContracts
Variables.CreateNewDatatable InputTable: { ^['Action', 'Entity Code', 'Contract'], [$'''''', $'''''', $''''''] } DataTable=> DataTable
Variables.DeleteRowFromDataTable DataTable: DataTable RowIndex: 0
LOOP FOREACH CurrentContract IN ExcelDataContracts
LOOP FOREACH CurrentEntity IN ExcelDataEntities
Variables.AddRowToDataTable.AppendRowToDataTable DataTable: DataTable RowToAdd: ['Add', CurrentEntity['Entity code'], CurrentContract['BID CONTRACT NUMBER']]
END
END
Excel.LaunchExcel.LaunchUnderExistingProcess Visible: True Instance=> ExcelInstanceOutput
Excel.WriteToExcel.WriteCell Instance: ExcelInstanceOutput Value: $'''Action''' Column: $'''A''' Row: 1
Excel.WriteToExcel.WriteCell Instance: ExcelInstanceOutput Value: $'''Entity code''' Column: $'''B''' Row: 1
Excel.WriteToExcel.WriteCell Instance: ExcelInstanceOutput Value: $'''Contract''' Column: $'''C''' Row: 1
Excel.WriteToExcel.WriteCell Instance: ExcelInstanceOutput Value: DataTable Column: $'''A''' Row: 2
Excel.SaveExcel.SaveAs Instance: ExcelInstanceOutput DocumentFormat: Excel.ExcelFormat.FromExtension DocumentPath: FilePathOutput
Excel.CloseExcel.Close Instance: ExcelInstanceOutput
You will obviously need to set actual file paths in the first three actions.
-------------------------------------------------------------------------
If I have answered your question, please mark it as the preferred solution. If you like my response, please give it a Thumbs Up.
I also provide paid consultancy and development services using Power Automate. If you're interested, DM me and we can discuss it.
