We provide the solution to this challenge using:
- Power Automate
Need a program? Click here.
You’ve been working as the IT specialist for all the accountants, and, boy, are they always grateful for your help! You’ve managed to save them over 300 hours every year! While walking around for everyone else to acknowledge your greatness, you overhear someone exclaim, “this is so mind-numbing!” Looking over their shoulder, you see that they are tasked with combining .csv files into the same .xlsx file and having to rename each file after copying the worksheet. You instantly know there is a much faster way to get this done, so you set to work to save the company some more time. You gather a handful of old files from a random company’s financials and general information in 2015. You are now ready to automate the process of constructing a new excel file out of these 8 files!
Create a flow that can open an excel file, and copy the contents of another excel file to a NEW worksheet on the original file. Rename the worksheet according to the name of the file and then, when all the pages have been combined, save your excel file as “2015CombinedBooks” (be sure to save it as a .xlsx file, not a .csv file!).
Remember to unzip the contents of this zipped folder. Referencing a zipped folder can cause problems when writing a bot.
Suggestions and Hints
- Use an action that creates a list of items to store the individual .csv files.
- Start off by selecting one file and test it out until it can run with just 2 excel files. After you figure it out for 2 files, loop it for the other files in the folder.
- When you are trying to rename the worksheets to the name of the Excel files, be sure that you are referencing the correct property from the item in the list. In this case, you want to rename the excel worksheet using the property “.NameWithoutExtension” to isolate the list item’s file name.