It truly blows me away on how much time gets spent on doing a thing over, and over, and over, and over. And over. One of the highest offenders in this space is Excel operations. Individuals are always going through the painful labor of manipulating sheets over and over again in the same fashion as the day or hour before. Please, let’s stop this. With Power Automate, creating a template, and empowering users to move swiftly though repetitive tasks is ridiculous easy…maybe so easy this article may be over doing it. None the less, Let’s Get Automating!
Hop over to Power Automate Desktop and lets discuss a few things. When we talk about Excel operations we either have a batch of documents or a single document and those locations can either been static or variable.
For the very first part of this lets examine the Get Files in folder
Above we can see that we are getting all the files in the static location specified and hard coded in the flow, but what if it changes or what if we want to define the pathway or even give users the ability to define the pathway without getting into the guts. Easy. To do this lets create an input variable and use teams as a conduit for this communication.
After we set up the input variable(s), head on over to Cloud Flows. We have a few options here to provide a spot for the user to enter this data, but I like the look and feel of the adaptive card. Its also a good time to mention a few other avenues here as well. We can start out the flow by using the For a Selected Message action. This will give us the ability to utilize our right-click flow tools See below.
We can trigger this on-demand which is super great for the end-user! Empowering!
The adaptive card editing is pretty self explanatory. Just give it and ID that you can recognize as we will pipe the response to the desktop flow we have started
So now that we have our variable that will allow the user to specify the location and we can head back over to our desktop flow. Now the static flow can be a bit more dynamic if need be. Notice that we have a match defined as well. I am targeting .csv documents, but we could in fact have this be another variable if the file type was variable.
We can follow this up for a For Each action if we are still wanting to go down the route of addressing more than one file. Even if we have one file this should suffice and do no harm. So the prep work is as follows: Get the files, do a for each on the files, launch the thing to do the work on the file based off of the name variable. The name variable as well as other properties will be pulled in from the Get Files in Folder action. Very handy.
So now we are free to perform operations on our sheet. We can do this through the Excel actions or Through Macros. I stay far away from macros like the band Journey. Probably around the same age.
If you must use a macro, you can call the macro from a variable and ask for that via the adaptive card. Alternately we can still give the user a card with choices and pipe that through the flow, but the piping would map to conditional sub flows.
When a new sheet type is needed a simple sub-flow and condition can just be added. Pretty low maintenance and probably easier than creating a macro for each. I’m going to skip over the bulk of actions because they are pretty self-explanatory, but I did want to highlight something to watch out for when using the Delete Row from Excel. When you read values from excel that response wont dynamically change, so if you are assessing something and doing an action keep in mind the value wont update. We can however move the counter appropriately based off of an IF statement counter. Keeping the number in check is important if removing or adding rows.
After the flow is complete we have some options. Save out the file, send it via email, create a teams message link to the location etc. Hopefully, at the very least this gives you some ideas on how to provide your users with the ability to to perform on-demand Excel operations. Keep in mind this is just one way to do it. We could create a listener and check for the file to exist, but what I really like about this solution is the perception it creates for the end-user and with automation, perception is king. Right-clicking within Teams and giving the users the ability to type or chose the pathway or Macro feels really good and empowering.
If you’re walking down the right path and you’re willing to keep walking, eventually you’ll make progress.
–Barack Obama
