Excel Automation in Power Automate Desktop | Write to Excel worksheet in Power Automate Desktop #03

hi guys welcome back to the session today i am going to explain multiple scenarios to write data to excel file using power automate desktop so let's get started let's begin with a very basic scenario where we will take a user input and write that user input in an excel file so let's create this flow in power automate desktop so for that i'll create a new flow let's give a flow name write to excel 1 and let's hit on create now first of all here we will take a user input for which i'm going to use the action input dialog so this display input dialog i am going to use to take a user input so let's drag and drop it to the main section let's provide a dialog title that is par automate desktop message and let's provide our input dialog message provide a value to write to excel now this action produces two variables that is user input and button pressed this user input variable will hold the text if entered by the user otherwise it will hold the default value if you provide here next is the button pressed variable which holds the text of the button to be pressed the value would either be ok or cancel so that is about the input dialog action let's hit save and accordingly you see the two variables got listed over here now to write this user input data into a excel file first of all excel need to be launched so for that i am going to use the excel actions from here so if you expand this excel here we have multiple of excel actions present first let's use this launch excel we will launch the excel with a blank document and this action also produces excel instance variable which will be used in the later excel actions let's hit save and now we are good to write this variable to the excel file for which i am going to use this write to excel worksheet action so let's add it here here it took the variable excel instance now value which we wish to write will provide here so from this select variable option i am going to use this variable user input which will hold the text value provided by the user so let's double click and select this one next we'll have to choose the right mode so here we have two options on a specific cell or on a currently active cell so let's go with the specific cell and in what column and row we wish to write let's take the column as a and row as one so the value will be written in column a row one all the details has been provided here let's hit save now once we write the data to the excel file we'll have to save and close the excel as well both these actions could be performed in a single action that is close excel so let me add this action as well and here also it took the excel instance variable next we have the option to save the document so if you expand this one here we have the option to save document as so yes since we have written something in the excel file that file need to be saved as well so that it can be looked on later on as well so let's choose this option save document as and here let's select the document format i'll go with the format dot xlsx and here we'll provide the document path so from here let's select the file and here i'll provide the file name i want to store the excel in this location so let's provide a file name here right to excel demo and let's hit on open so all the details has been provided to save the excel and to close the excel let's hit save and with this our first basic flow is ready let me save this one and let's hit on ok let's quickly run this flow to see how it works and here we get the input dialog where we will provide a user input here it is asking please provide a value to write to excel so let me provide a value here the value which we will provide here will be written to the excel file let's provide value as demo on excel and let's hit on ok and the flow is over let's quickly move to the folder to see the excel file and the value present there so here you see under this folder this write to excel demo file was not present earlier it got created when we ran our flow so let's open this excel file as well to see the value which got written and here you see the user input which we provided that is demo on excel got written in this excel file in column a and row one that is a one cell in the a1 cell the value got written so in this way we created the flow for our scenario one and it worked successfully now let's move to our scenario 2 where i have an excel file i have another excel file with some employee data now for each of these rows one by one i want to check the value of salary and if the value of salary is greater than 10 000 i want to write here group 1 and if the value of salary is less than or equal to 10 000 i want to write here group 2 and i want to do this group 1 and group 2 grouping for all of these rows from this excel file so let's create this new flow let's create a new flow for this requirement so let's move to par automate desktop again let me close this flow and let's create a new flow from here right to excel 2 and let's create this flow now in this flow first of all let's read this complete data from this excel file and store it in a data table variable so for this also first of all we will launch the excel file so expand this excel and add this launch excel action here let's launch the excel with a specific document so let's go with the option open the following document and select the file i'll choose this file test data pid let's hit on open and this action produces this variable excel instance let's hit on save next we will use the action get first free column and row to get the first free column and row from this excel file so let me take this action so this action takes the excel instance variable and it produces first free column and first free row in our case the first free column will be this one and first free row will be this one i have already discussed about this first free column and row action in detail in our previous session in case you have missed that video please check out that video as well for a quick reference let's hit on save and now i'm going to use the action read from excel worksheet to read the data and here it took the excel instance variable which was created in the launch excel action next we have the options to retrieve the data since we wish to retrieve the data from range of cells let's go with this option that is value from range of cells next we'll have to provide the start and end row and column values so we wish to start reading from first column and first row so let's provide the value here start column as one start row as one next we'll have to provide the in column and in row so in our case as of now it is visible that end column is 5 and in row is 7 but this number of rows and columns could increase in future that's why to make this dynamic we have used the action get first free row and column which produce two variables which we are going to use here so from this select variable let's select this first free column and in our case the first free column will be this one that is the sixth column and since we wish to read six minus one that is still fifth column so here we'll provide first free column minus one similar for the end row i'm going to take the variable first free row minus 1 since here the first free row will be 8 and we wish to read till 8 minus 1 that is 7. so this first free row and column variables are very helpful in case the number of rows and columns are dynamic in nature now if you open this advanced option here it has the option to turn this on if the first line of range contains the column names so yes in our excel file the first line contains the column names these all are the column names and not the data so we'll go with this option as turned on our first line of the excel file contains the column names and now this x this action also produces variable that is excel data which stores this complete data this complete data it will store in n variable which is excel data of type data table so let's hit save and accordingly all the variables gets listed here this excel data contains this complete data now for each of these rows one by one will have to iterate each of these rows one by one to check the value of salary and to iterate the rows i am going to use for each row action so let me add this for each row action here and let's select the variable to iterate from here and we wish to iterate this excel data of type data table variable so let's select this one and this action produces the variable current item which is a data row variable this current item variable will hold the value of these rows one by one as the loop it rates so let's hit save and next we will check the value of salary on the basis of condition so i'm going to use this if action from here let me add this if action and here will provide the condition based on operand and operators so operand i am going to take first operand i am going to take as a variable so from the select variable i am going to take this current item variable of type data row and here we'll have to provide the column name as well so inside the brackets inside the quotes here i will provide the column name since we wish to work on the salary column so let me copy this salary from here and let's provide the salary column here operator i'm going to choose as greater than and here the second operand i'm going to choose as 10 000. so here we have created a condition which says salary greater than salary greater than 10 000. let's hit save and if this condition is true then we wish to write in our file as if the salary is greater than 10 000 we will write here group 1. so the right part comes now so here i'm going to use the right action from this excel package so here we have the option write to excel worksheet so let me add it here so here i took the excel instance variable and what value we wish to write we wish to write group 1 next let's choose the right mode we wish to write on a specific cell not on any active cell so let's choose this specific cell and here we'll have to provide the column and row value on which we wish to write now if you notice here for the first row we'll write in this cell that is column e column e and row 2. next we will write in column e and row 3. next we will write in column e and row 4. now if you notice here this column value is fixed that is e but the value of row is changing every time it is getting incremented by 1 every time so we can provide here the value of column as e we can provide here the value of column as e but to provide the value of row we'll have to create a variable which will get incremented by 1 every time and it will hold the value of row so for that we'll have to create a variable so as of now let's provide two here let's hit save and let's create a variable so for that i'm going to use the action set variable so from this variables let's use this set variable and i will add it before for each so this action produces a variable say new where and i'll provide the first value as 2 since we wish to start from row 2. so let's create this variable hit save and also at the end of for each action we'll have to increment this newer value by 1. so for which i am going to use the action let me show you so from this variables i am going to use this increase variable at before ending the for each action so let me add it here let's state a variable as this new var variable and increase its value by 1 let's hit c now we can use this new var variable as the column value so from in this write to excel worksheet instead of this hard coded row let's move to select variables and from here let's choose this new var variable which will hold the value of row and it will get incremented by one every time as the loop iterates so let's select this one all the details has been provided let's hit save so we provided the condition to write data to this excel file if the salary is greater than ten thousand but what if the salary is great less than or equal to ten thousand for that we wish to write here group 2 so here i'm going to use another action that is else if so let me add this elsif here let's choose the first operand from this variable let's select this current item and inside the brackets and inside the quotes let's provide the column name which will be nothing but the salary which we used earlier as if well salary is less than or equal to 10 000 let's hit save so we provided this condition if the salary is less than equal to 10 000 then we want to write group 2 so for that again we'll use this write to excel worksheet here we wish to write the value as group 2 write on specific cell column will be e and row will be let's from the variables let's select this new variable which we just created so again it will be writing in this e column and the rows will be getting incremented by plus 1 as the root bit rates let's hit on save and all the conditions we have provided that is greater than 10 000 and less than equal to 10 000. now at the end we'll have to close the excel file we'll have to save and close the excel file so once this loop is over we can close the excel so let me add this close that excel action here and since we are writing something in this excel file that need to be saved as well so from here before closing the excel i'm going to save let's take this save document and let's hit on save so with this our flow is ready let me show you a recap what all we are doing first of all we'll launch the excel and we'll get the first free column and row then we'll read the complete data we'll read this complete data from this excel and store it inside a data table variable that is excel data let me add a message box as well just to show you the flow it is not required so let me add it here display message let's provide the message box title as pad message and let's provide a message here data read from excel file successfully and let's hit save let's take the message box icon as information and let's hit save now once we have this complete data inside this excel data variable which is a data table we will iterate through this excel data variable which will provide the new variable that is current item which is a data row variable and accordingly we'll check the value of salary if the value of salary is greater than 10 000 then we'll write group 1 and if the value of salary is less than or equal to 10 000 we'll write the value as group 2 in column e and the row variable will get incremented by plus 1 every time as the loop iterates and at the end we are closing the excel file we are saving the excel and closing the excel file so i'll quickly run this flow to see how it works let me save this flow and let's close these files it will be launched by the power automate desktop itself so let's close this one and let's quickly run this flow and execution is in progress so excel got launched and the data got read and accordingly the message got displayed data read from excel file successfully now since the data got read and stored in the data table variable it will be iterated and accordingly the values will be written here so let's click on ok and here you see the values are written here group 1 and group 2 on the basis of salary so the flow is over let's move to the excel file to see the results so if i open this one so here you see the data got written as group 1 and group 2 and along with that one extra group 2 got written here so let's look take a look into the flow what we have missed here so if i scroll a bit up and if i look at this read from excel worksheet so here the in row we have taken first free row only so we'll have to take this one as first free row minus one since the first free row is eight and we wish to read till eight minus one that is seven so that was the mistake in our flow we'll have to take first free row minus one and first free column minus one so let's hit on save i'll run this flow one more time to see the results this time this group two should not be present so let's delete this one and let's and let's run this flow and the execution is in progress excel got launched and data got written accordingly we have the message data read from excel successfully let's hit ok and accordingly the data is getting written to go to this excel file as group 1 and group 2 and our execution is over now let's move to the file again and if i open this one now you see the grouping is done correctly as group 1 and group 2 based on the salary we don't have any extra groupings present here as it was present earlier so in this way we wrote the data to this excel file successfully based on the value of salary so in this way you can write data to excel file in a power automated desktop and that's all for this session guys i hope you enjoyed this video and if you did give it a like and share with your friends hit the bell button to get the updates on the latest videos if you have not yet subscribed to our channel please subscribe to our channel as well and i'll see you soon in the next one bye

Channel: Quality Assurance Lab

Views: 26,006

Keywords: power automate excel tutorial, power automate desktop excel, microsoft power automate excel, excel automation power automate desktop, how to work with excel in power automate desktop, excel power automate desktop, read data from excel in power automate desktop, power automate desktop flow tutorial, power automate desktop flow, power automate desktop flow examples, power automate desktop write to excel, write to excel worksheet power automate desktop

Id: r7RwdpkN-Fg

Length: 22min 7sec (1327 seconds)

Published: Wed Mar 24 2021