How to create the new excel file by using the Original file with format

Hello everyone
I have a question about my automation, let me explain to you roughly what problem that I stuck as below,

  1. I have an original pdf file to express the 3 data out by using the Regex (Serial No., Customer Shop name, Customer name), I have already completed this automation.
  2. Next after I received them, I ran into the company website then Login-> Enter Serial No. → Copy Password->Paste Into the original formatted Excel file (Application Format.xlsx)
  3. I created the for each loop to run the project by reading and collecting data in the type of Array, Now I can store the (Serial No., Customer Shop name, and Customer name) with all of my PDF files in the folder (App1, App2, App3, and App4)

So as I explained, I put the Excel application scope into the For each loop, and the UI automation just copy and paste the Data from (Serial No., Customer Shop name, Customer name) and Password Just only the last file due to the Loop overwrite it in this time.

I want to know the Idea that how can I create the loop to copy the (Application Format.xlsx) and create the new one within format(Application Format-App1.xlsx), (Application Format-App2.xlsx)…by including automation to collect the data from (App1, App2, App3, and App4) and Password from website

The below image should be before running the UI path

And, This one below is my expected

Many thanks in advance

Regards

Hello @poomnattawat ,

For Write Range give the File name as :
"Application Format - “+PDF_FileName+”.xlsx

Note : Here PDF_FileName is name of your PDF file over which you are looping (Ex :App1, App2,…)

This way for each item in loop a separate excel file will be generated as required.

Regards,
Rohith

1 Like

Many Thanks to you Rohith
I would like to try it first
Regards

Hello @rohith.prabhu


This is the original one before I started the automation of the excel file (Application Format.xlsx) was an original formatted, I don’t know how to create the expression

“C:\Users\nattawsi\Desktop\BPR & RPA\UI path\LSS password\New folder\Application Format.xlsx”

This is my expression when I use the excel application scope what should I do with my file name
can I input like;
“C:\Users\nattawsi\Desktop\BPR & RPA\UI path\LSS password\New folder\Application Format - +PDF_FileName+.xlsx”

I think it will not run out due to it not having any file name like that one before, or maybe It’s my misunderstanding please explain :smiling_face: Thanks

Regard

Hello @poomnattawat ,

You are using for loop to loop through the PDF files and extract the data from it right ?
To read PDF File you are passing the PDF File Path right?

Perform the following steps inside the for loop.

  1. Create a Variable Called PDF_FileName (Type String) Use Assign activity
    Assign : PDF_FileName = Path.GetFileNameWithoutExtension(PDF File Path)
    this will store the PDF File name.

  2. As you are having the Template file (Application Format.xlsx) hence you need to create copy of the template before writing the data to it.
    Use Copy File Activity
    In From field : “C:\Users\nattawsi\Desktop\BPR & RPA\UI path\LSS password\New folder\Application Format.xlsx”
    In To Field : “C:\Users\nattawsi\Desktop\BPR & RPA\UI path\LSS password\New folder\Application Format - +PDF_FileName+.xlsx”

  3. After this use Write Range to Write the data to this Excel Path : “C:\Users\nattawsi\Desktop\BPR & RPA\UI path\LSS password\New folder\Application Format - +PDF_FileName+.xlsx”

Hope it is clear now!

Regards,
Rohith

2 Likes

Hello @poomnattawat

First, get the file name of the pdf. You can keep the pdf files in a folder and you can use Directory.GetFiles(“Folder path”)
Then use a for loop.
You can use copy file activity to copy the template. Then in the To path give the name as below:

“Application Format - “+pdfname+”.xlsx”

pdfname is the variable that holds the name of pdf inside the for a loop.

Thanks

1 Like

Dear @rohith.prabhu thanks a lot for your explanation
I will try this step
Regards

Hello @rohith.prabhu
I have tried your step, It can run out smoothly, but the output is failed

  1. The UI path can copy the original file and create the new file including the PDF file name as you explained
  2. The new file create into the 2 files with the same file name, the first file was copied and pasted but it did not include any data ( same as the original file), and the second was created new without the format but it stored the data

All of the loops is in the for loop

image

Regards

@poomnattawat - Can you send SS of the Folder where files are located.
Also try to comment the copy the file activity and run again.

Hi @rohith.prabhu

This one is an original located file
“C:\Users\nattawsi\Desktop\BPR & RPA\UI path\LSS password\Application from Customer\Application Format for LSS OFF SWITCH 30 days.xlsx”

This one is a copied file location:
“C:\Users\nattawsi\Desktop\BPR & RPA\UI path\LSS password\Application from Customer\Application Format for LSS OFF SWITCH 30 days - “+strPDF_FileName+”.xlsx”

This one is in the excel application scope :
“C:\Users\nattawsi\Desktop\BPR & RPA\UI path\LSS password\Application from Customer\Application Format for LSS OFF SWITCH 30 days -”+strPDF_FileName+“.xlsx”

image

Regards

@poomnattawat

Provided Path and everything else looks good here. Kindly debug and check why you are getting a blank file.

I think has something wrong with received data from regex pdf file… so it has not anything to write and the file is empty.

You can add writeline or messagebox activity at each step when regex data to check correctly of data.

Hello @Mr.H
I already used the write line and it showed the correct data from the PDF file,
Before using copy and paste function, It was worked well
Regards

1 Like

You can run for one by one pdf file to check the result… and then run about 2-3 pdf files to check.

Hello @Mr.H
Thanks for your advice,
I will try to run as per your comment, but first I create the copy and excel application scope, It worked well without double file copied.
Regards

Hello @rohith.prabhu
I have found my super mistake LOL,

This one is a copied file location:
“C:\Users\nattawsi\Desktop\BPR & RPA\UI path\LSS password\Application from Customer\Application Format for LSS OFF SWITCH 30 days - “+strPDF_FileName+”.xlsx”
This one has a spacebar after (-)

This one is in the excel application scope :
“C:\Users\nattawsi\Desktop\BPR & RPA\UI path\LSS password\Application from Customer\Application Format for LSS OFF SWITCH 30 days -”+strPDF_FileName+“.xlsx”
This one does not has a spacebar after (-)

The loop runs completely without 2 files separate

Thank you very much

Regards

1 Like

This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.