Excel automation, How to write multiple variable values into excel in PAD?

[Sanket1]

Hi Team , 

I wan to write multiple values into excel in power automate desktop.

Variables are: 

PartAFileExist = Yes

PartBFileExist= Yes

AnnexureExist = Yes

EmployeeName= ABS

Pan_Card = ABID1344P

My Excel Columns are

EmployeeName, Pan_Card, PartA, PartB, Annexture

[Deenuji]

@Sanket1 

Try the below approaches: 

Method1:

Method2:

Thanks,
Deenuji Loganathan 👩‍💻
Automation Evangelist 🤖
Follow me on LinkedIn 👥

-------------------------------------------------------------------------------------------------------------
If I've helped solve your query, kindly mark my response as the solution ✔ and give it a thumbs up!👍 Your feedback supports future seekers 🚀

[Deenuji · Accepted solution]

@Sanket1 

Apologies for the confusion!
First, use the "get free rows/column number" action to determine the available row number.

Next, use the "Write to Excel" action. Specify the column name and write each column one by one with the variable value like below(sample screenshot attached is with hardcoded value but you can replace with variable name).

I'm not certain if we can write all column values in one go using inbuilt PAD actions but again which can be achievable via scripting languages.

However, if you import your Excel data into a data table, you can write all variable values at once as mentioned above, and then write it back into Excel is another work around for your use case.

Thanks,
Deenuji Loganathan 👩‍💻
Automation Evangelist 🤖
Follow me on LinkedIn 👥

-------------------------------------------------------------------------------------------------------------
If I've helped solve your query, kindly mark my response as the solution ✔ and give it a thumbs up!👍 Your feedback supports future seekers 🚀

[Sanket1]

I thought about the DT, we store the all data in DT then at last we write to excel.

What if I have 100 records iterating it, bot failed at 10th record then how we can write into excel?
First we store all 100 records then at the last we write it into excel. 
Thats why I am writing into excel instead  using DT

[Deenuji]

@Sanket1 

Then you may follow the below approach.

First place "Write to Excel Worksheet" action and in value to write Mention your %DataTable.ColumnHeadersRow% then mention Column as A and Row mention as 1.

Next place same "Write to Excel Worksheet" action then value to write parameter, just mention your data table variable like %DataTable% then mention Column  as A and Row mention as 2.

Now it will convert entire data table into excel. Please try and let me know for any issues.

Thanks,
Deenuji Loganathan 👩‍💻
Automation Evangelist 🤖
Follow me on LinkedIn 👥

-------------------------------------------------------------------------------------------------------------
If I've helped solve your query, kindly mark my response as the solution ✔ and give it a thumbs up!👍 Your feedback supports future seekers 🚀

[Sanket1]

Hi @Deenuji  Apologies !

My work flow is like ,

InputExcelData

Iterating through excel data

     - Geting pan card number everytime 

     - then checking 3 files exist 

     - Merging it as pdf 

     - Then updating the status in excel file of current transaction

Loop end

What I am thinking that , i am updating each transaction record every time in excel.

if bot failed in between still I can get know that how many transaction has completed so far.

If I use DT , then bot failed in between then even bot will not write the transaction details into excel.

Correct me If I am wrong

[Deenuji]

@Sanket1 

I never come across some bot failed during for each loop but if you have concerns on the same then your above logic makes sense to me.

Thanks,
Deenuji Loganathan 👩‍💻
Automation Evangelist 🤖
Follow me on LinkedIn 👥

-------------------------------------------------------------------------------------------------------------
If I've helped solve your query, kindly mark my response as the solution ✔ and give it a thumbs up!👍 Your feedback supports future seekers 🚀