Billing process with Excel - Urgent

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?

1 Like

Hi

Welcome o UiPath forum

Hope the below steps would help you resolve this

  1. Use a excel application scope and pass the file path of InvoiceData.xlsx file as input

  2. Inside the scope use a read range activity and get the output as dt

  3. 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

  4. 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

  1. 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

  2. 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”

  3. The above expression will create the excel file a copy with the above Filename

  4. 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

Cheers @Jonni

could you share .xaml file??

can you share the xaml file fore this case ?