How to process multiple Excel files

Hi Team,

I have an excel application scope that contain the invoice number, I created separate excel file for each invoices…Now were I am struggling is how to open those excel file and paste some data close it and simultaneously.

Please help

@jimmy_jos1

You can use Excel application scope for invoice number, inside this scope you can open one more excel application scope and iterate a loop for individual invoices.

@lukas_krishnan — So what should be the syntax or file workbook path i have to mention in order to open the workbook do necessary changes -> close it and simultaneously till the end of invoices in first excel application scope??

Please advise or share some subjective.

@jimmy_jos1

Can you explain the business logic which you required with dummy data?

Step 1:I have list of invoices in a file…
Step 2: I have to a create separate workbook for each of the invoices using step 1
Step 3: Now I have to open those invoices workbook one at a time paste the application screenshot and close it…I have to perform this Step 3 for all the invoices present in step 1

Step 2 is done not sure how to do step 3

I cannot use a workbook path because the file will keep on changing as per the invoices in step 1.

@jimmy_jos1

Suggestion:
If its a series of action,then you can iterate through loop.

For parallel action, try VB.NET code for writing the value.

For dynamic excel path, you can use variable for path in excel app scope and pass address to that variable

@lukas_krishnan Can you elaborate little more…

@jimmy_jos1

As per my understanding,
You have Master excel file with invoice numbers.
Lets take invoice number are 1001,1002,1003 in Master excel.

You will be creating new excel based for on invoice number. Now there are 3 invoice excel(1001.xlsx,1002.xlsx,1003.xlsx)
is it right?

correct

@lukas_Krishnan

Correct

@jimmy_jos1

You can read the Master Excel invoice numbers in datatable.

1)Then, iterate a loop for that datatable
2)Inside the loop use one excel application scope.
3) Workpath in Excel appliaction scope–> FileDirectory+InvoiceNumber
4) Business logic inside excel scope

So, it will attach your screenshot in each invoice excels

@Lukas_krishnan

FileDirectory+InvoiceNumber ( You mean to say File Directory path + Column name) ???

I can share my xaml file …If you could help me on that…

1 Like

@jimmy_jos1

share your xaml fie.

Sample_File.xaml (17.9 KB)

For my reference if you could show an example were its Open the invoices workbook paste a data from notepad and save it and simultaneously .

LayoutExcel.xaml (7.5 KB)

Just refer the layout for your requirement

@lukas_krishnan

I tried the above layout but its copying same data from notepad to all the invoices but my requirement is to copy the information from notepad related to the master file invoices…
i mean to say 1234.txt data to be pasted in 1234.xls , 1235 .txt data to be pasted in 1235.txt

@jimmy_jos1
LayoutExcel.xaml (8.2 KB)

Changed workflow based on your requirement.Refer it

Cheers,
Lukas

@lukas_krishnan

Its not working…
image LayoutExcel (1).xaml (9.3 KB)

@jimmy_jos1

Mentioned error may occurred due to file is not closed before running the Robot.
Close all files before running the robot.
Change the NotepadPath value based on the logic.

Hi @lukas_krishnan

One complex scenario—

Attached screenshot image

We have two different Application from were bot has to extract the copies.

will this be possible for bot to first read the file-> check the ERP column -> open that application and pull the invoices…

How to create a decision activity for bot to first check the ERP column and then open the application