Power Automate Desktop : Module 3 : Excel Automation - 1 (Launch Excel, Read from Excel,Close Excel)

hello techies welcome to microsoft power automate desktop tutorials in the upcoming sessions we will learn how to automate an excel by using power automate desktop in this session we will learn few of the actions which are going to be involved in excel automation all right so we will start this session with a simple use case let's assume we are having employee details in an excel and then we want to write the data into the text format how can we go ahead and we can do that by using power automate desktop all right now i will show you a few simple steps how to do by using power automate so that you will try to you know you will get a full understanding how we are going to do by using power automate all right first of all we'll see how can we automate this excel automation by using power automate desktop with few simple steps first thing is that we want to launch an excel so whenever we have launched this excel will get all the properties or the parameters written related to the excel next one once we have launched the excel then we are going to read the data from the excel worksheet third one once we have read the data from the excel worksheet we are going to store in the data table the data we are going to write in the text format by using write text to a file an action and finally once the data has been written successfully then we are going to close an excel which has opened as a part of first step these things will call it as actions in the power automate desktop i hope you understand what are the steps or involving from excel to text format let me switch to microsoft power at my desktop now i'm going to create a new flow by clicking on new flow and here i'm going to give the flow name as excel automation because in the upcoming sessions as i said i'm going to work on excel automation so i'm going to give the flow name as excel automation click on create now once the flow has been created it will take a little bit time to open the flow now if you see over here we got the power at my desktop over here now at the first step what we are going to do our use case is that we have to write the data from excel to text file right so that i am going to use the first step as an excel if you see under the actions that i am having in excel just expand that and there you will find launch excel action so drag and drop this launch excel action onto the workspace just drag and drop and if you see there are a lot of parameters which we have now i will explain you one by one about this parameters okay if you see for the launch parameter first thing is that launch excel again there are two options that we have one is with a blank document or else we have to open already existing that is open the following document now what we are going to do we are going to read the document which is already existing right so that i will select this open the following document but when we can select with a blank document this will be selected whenever you are going to write the data into the blank document or existing document also in that case that we'll use with a blank document now what i'm going to do i'm going to open the following document which is already available in my desktop okay now let me show you how it looks like the excel this is the excel of the employee details feature we have you can see i'm having lot of details over here this entire data i'm going to be reading by using excel automation and then i'm going to write the data into the text file this is what we are going to do all right now let me go back to my power automate desktop there what i'm going to do i'm going to select we open the following document once you have selected open the following document it will ask for the path where you are hanging that document now i'm having the document in the desktop let me go for the desktop by clicking over here on this image over here if you see over here what it will say select a file from here and you can see i'm going to the desktop and then i'm having in the power automate desktop i'm having employee excel let me open this and if you see the path over here whatever the path i have given it will be getting all the password the path you will get it over here now this is about the first parameter which we are having for the launch excel if you see the second parameter make instance visible what is this mean if you are going to select this one if you see over here specify whether to make the excel window visible or hide it when you are going to select this one a new window will comes in the task manager or taskbar you will get it and then it will opens the excel if your once you are going to unselect this one what will happen it won't show in the taskbar all right now what i'm going to make it i'm going to make instance visible over here by selecting this option that is my second parameter now password let's assume my excel having password protected in that case what i'm going to do i'm going to give the password to open this excel as of now i don't have any password over here which i have not used any password to open this employee dot excel sx file so that i'm not going to provide any password over here all right now let me go to the another parameter that is open as read only let's assume i don't want to change or i don't want to make any changes in my original excel file in that case i want to make it only read only in that case what i will do i will use this proper this parameter that is open as read only then it won't change anything over here in the particular excel right now i don't i have i don't have any confidential or i don't have any kind of such a kind of data in my excel so that i'm not going to use this option all right now we will talk about the load add-ins and macros this parameter we will use whenever we're going to work with macros right now this is not required don't worry about that for the current situation or for the current use case all right now if you see over here there are the variables produced in there if you see under variable produce i'm having excel instance what is this instance this launch excel having an instance to the specific excel instance for the use for the later excel actions that you are going to use this excel instance so that you know this allows to user to specify which of probably several excel spreads to access in the next steps we are going to use the same excel instance to close an excel and also to read the data from the excel so for that being i'm going to use this excel instance as a variable all right now if you see there's on error rate there if you have found any errors if you got any errors then it's a kind of try catch mechanism that would we have we want to continue to run the flow or we have to throw an error so what kind of rules we can specify it all these things you can specify over here but right now i'm not going to get any errors i'm just going to be in the positive scenario i'm going to get all the excel all right let me go back to return to parameters i hope i have explained you each and every parameter in the launch excel now let me go ahead let me save this action by clicking on save now if you see on the variables that you got flow variables excel instance one of the variable has been created now this is my entire first step which you have seen as part of the excel automation now i have launched an excel now i want to read the data from the excel so how can i go ahead and do that from the excel actions itself that you will find read from excel worksheet that is the second action which we have let me drag and drop this read from excel worksheet to the workspace now if you see over here again i'm having some of the parameters that what we have you can see over here excel instance so i have already in my first step launch excel i have stored a variable that is excel instance that the same excel instance i'm going to use it over here to open the to read from the excel worksheet all right now this is the my first parameter now we'll talk about the retrieve now i want to retrieve the data from my excel all right let me open excel once again if you see over here i'm having name email id status and date of joining so the candidates who are selected that you know i'm going to give them as selected who are rejected i'm just going to give the status as rejected and you know the people who have selected i'm going to give the joining dates over here this is my entire data but starting cell is a1 and the ending cell is d6 this is the entire data i want to read it from the excel how i'm going to define that from over here if you see over here single sales value that is retrieving how we have to select you can see over here i'm having different options over here like select single cell values values from a range of cells values from selection so what i'm going to do i want to read from the particular range that is range a1 cell to d6 as i have shown you in the excel that is the range which i want to select all right now what i'm going to do i'm going to select this option values from the from a range of cells and then i'm going to give the start column start row what is the start column that is a and the row is 1 i'm going to define over here the starting column as a and the starting row is first row that is one index which i'm going to give it and the end column is d and the end row is 6. i'm going to get all the details over here all right now once you have given the range over here now you can see get cell contents as text so what i have to do i want to get it in text format or whatever the format is required as a text format if you required then you are going to do as true that the mean what will happen this entire data will comes as a text or if you don't want as a text then you're going to unselect this this will comes as a data table now if you see over here we are having one more parameter that is first line of range contains the column names if you see over here in my excel these are all the columns which i have right the first row is an excel column that's why i will say first line of the range contains column names true i'm going to make it as true and then if you see over here the entire data values variables produce this whatever the data you have read it by using red from excel worksheet that will stores in the excel data format that is variable it will be creating one more variable automatically that what it will say is the value of the range of the cells as stored as a data table all right now this whatever the data i have read it i want to store it in the data table format now what i'm going to do i'm going to save this action by clicking on save now if you see under variable that you got it another variable with the name of excel data all right now we have covered two actions as part of excel automation now the third step is once you have launched the excel you have read the data into the excel data and then whatever the data you have stored in the excel data you have to write it into the text format all right that is my third step so as a third step what i'm going to do i'm going to write into the file so i'm going to use file actions under the actions and then i'm going to use write text to file let me drag and drop this action onto the workspace and if you see over here it will also few of the parameters where you want to store this text where you are having that text file now what i'm going to do i'm going to select once again the file and now let me go back to the desktop and then i'm going to give power out my desktop over here and now what i'm going to say i don't have any file over here but simply what i'm going to do employee dot txt file that i'm going to uh creating it automatically by giving this value now let me click on open what will happen this file is not exist so that it will create that file and then it will write the file to the particular employee.txt file all right now we'll talk about the another part text to right what text i want to write it to this employee dot text file so we are having the data in the m excel data variable right that is the data table variable which we have that data i want to write it over here so that what i'm going to do i'm going to click on the function symbol over here and then you can see all the variables whatever the variable you want to write it you can go ahead and you can write it now what i'm going to do i'm going to write the data which are stored in the excel data so that i am going to select that excel data data table type let me select that now if you see percentage excel data has came and if you see append new line for every new line that i want to add a new line in that case that i am going to use this open new line feature as true now if you see over here there are two more properties two more parameters are there one is a file exist so if already with the name of employee.txt exists then what you have to do you are having a lot of features over here you can see overwrite existing content or append to the existing content so overwrite in the sense if you have already a file there so that you know it will delete and recreate it in such a way overrating the existing content so append content is nothing but if you're already having a number of lines in the particular text file so that it will add as a 11th line over here by adding app and content over there but right now what i'm going to use i'm going to use forwrite existing content and if you see the encoding we're having a lot of encodings are there ascii and c is their unicode unicode big end and utf-8 all there but i'm going to use the default one unicode and then i'm going to save this entire action with the parameters let me go ahead let me click on save now almost our flow is ready but we have already launched our excel we need to close that excel how can we close as a final step let me go back to excel once again and if you see there is a one more action to close the excel that is close excel let me drag and drop this action onto the workspace now if you see automatically what it will do the excel instance which instance you want to close it already which you have been opened as part of launch excel that the excel instance that you are going to be closing over here and if you see over here before closing an excel if you want to save any data or save document or save document as if you want to do anything so that you can go ahead and you can do that but i'm not going to be modifying anything over there in the particular excel so that i don't want to save the document so that what i'm going to use do not save the document option that i'm going to select it and then i'm going to save this action all right i hope you have understand all the actions as a part of excel automation in the particular use case now let me go ahead let me save this flow by clicking on save you can see flow excel automation saved successfully let me click on ok now what i'm going to do i'm going to run this flow to to check it out it is executing successfully or not before that let me go to the path where we are having the excel file if you see over here i am having only employee excel file i am having i don't have any text file once i run the flow it will create employee text and then it is having the data inside let me go back to flow now what i'm going to do i'm going to run this flow by clicking on run over here let me start by clicking on run if you see over here it will run from every step by step action first it will goes to the launch excel and then it will open in excel and then if you see over here excel has been open and then you can see it has been read the content write file and closed also successfully now let me go back to the path and we will open the employee text by clicking on open now if you see the data i'm having the entire data over here what we had in the employee excel that all the data had been written in the text format right you can see amir bashar automate 86 selected and you can see all the date of joining and the status over here but if you observe one thing over here i don't have the column sort here what is the columns over here why i have missed out we have not missed it out the thing is that so there is one of the property we have given in the read from excel let me double click on the read from excel worksheet and if you see the first line of the range contains the column right now what i'm going to do i'm going to unselect this one so i'm going to make it as a false over here and then i'm going to save this now let me close once again this employee text now let me run once again the entire flow now the flow started execution and you can see excel has been opened and then it has been closed successfully now let me go back to the folder where we are having let me open by double clicking on that now if you see i got the name and that is the columns i got it that is name email status date of joining and what not all the details have been returned successfully to employee text file i hope you understand how to launch an excel and how to read the data from the read from excel worksheet and also we have seen how to write the data to the text file and also we have seen how to close an excel by using multiple actions in this particular module thank you for watching microsoft power automate desktop tutorials if you have any queries related to this concept please post them in the comment section i will see you in the next session till then bye bye have a nice day

Channel: Learning RPA Technologies

Views: 74,466

Keywords: Power automate desktop, power automate, microsoft power automate desktop, microsoft power automate, Microsoft Power Automate, microsoft power automate tutorial, power automate tutorials, Power automate desktop app, Power automate desktop automation, Power automate desktop UI Flows, power automate tutorial, power automate microsoft, power automate desktop automation, Launch Excel, Read from Excel Work Sheet, Write text to a file, Close Excel, power automate excel automation

Id: OyfRLYdmbEc

Length: 20min 41sec (1241 seconds)

Published: Tue Sep 29 2020