I’m trying to create a bot that extracts invoice data from an excel file and uses that data to create 7 different invoices in another excel file. The bot should be able to open the Excel file with the invoice data (InvoiceData.xlsx), review the data and then open the Excel invoice template file (MasterInvoice.xlsx). The bot should then create a unique invoice for each record in the invoice data and ten it should save the updated invoice template file for each customer as MasterInvoice_InvoiceNumber.xlsx.
In short, this is the Billing Case by Abigail Zhang (Rutgers University) if anyone is familiar with that case.
I can’t upload the excel files because I’m a new user but can anyone help me with this?
Use a excel application scope and pass the file path of InvoiceData.xlsx file as input
Inside the scope use a read range activity and get the output as dt
To review the data use a for each row activity and pass datatable dt as input and inside the loop use a IF activity to validate
Or use a FILTER DATATABLE activity for any filteration based on validation
Use same dt as a variable in output datatable property
We need your invoice number from that input data
If the datatable has single row with a header then use a simple ass Activity like this Str_invoicenumber = dt.Rows(0)(“yourcolumnname”).ToString
Where row(0) is first row after header and mention the columnname from where we can get the column name
But if you have all the invoice data in one file then use a FOR EACH ROW and pass dt as input and inside the loop use a assign activity like this str_invoicenumber = CurrentRow(“yourcolumnname”).ToString
Once after validating if the datatable has one row then directly use COPY FILE activity or use this same copy file activity inside a FOR EACH ROW
In that copy file activity pass From property - the filepath of MasterInvoice.xlsx as input and TO as ”your folder path”+”\”+”Master_”+str_InvoiceNumber.ToString+”.xlsx”
The above expression will create the excel file a copy with the above Filename
Then use a number of WRITE CELL activities and pass the value one by like
If the datatable has one row then mention like this dt.Rows(0)(“yourcolumnname”)
Use the same but just change the columnname alone where 0 remains same as it is of one record only
If there are multiple records and the ad said above use a for each row and inside that use assign to get the invoice number and then use a copy file activity and then use N number of write cell to write into the new file created where in input mention as CurrentRow(“yourcolumnname”).ToString
This will write all the values from input file to new file created