I need to grab values from each file in a folder and paste them into a single excel workbook. I've tried to write a flow for that gets the files in the folder and the runs a for each loop to copy and paste the values. The flow runs through all of the files but for some reason the variables aren't updating for each file, so the flow just writes the same value for each file. Please let me know if anyone can help with this. I am very new to power automate and I can't figure out how to make this work.
Thanks in advance!
Solved! Go to Solution.
Paste the loop part of your flow so we can see where it is repeating itself.
My guess is you are trying to reference %Files% instead of %CurrentItem% or something along those lines.
So there actually is 2 Excel instances.
The first one at the top is "ExcelInstance" - this is the master file I need to past the data into.
Then the loop is set up with the following steps for each file in the folder:
-Renames "CurrentItem" from %Files% to "Extract"
-launches the file named "Extract" - this is "ExcelInstance2"
-reads the data from "ExcelInstance2" - stores it as "NJClass12020"
-gets the first available row from "ExcelInstance"'
-writes the data stored in "NJClass12020" to the firstavailable row in "ExcelInstance"
My guess is your rename looks like this and it is not overwriting Extract.xlsx when you try to rename the second one. If that's the case, you need to delete Extract.xlsx each time you are done with the file.
now I am running into another issue... It can't rename the files now because one of the files already renamed "Extract" is "locked for editing" by me, but I do not have the file open. It won't let me delete it either... any idea why it is locked by me even though the file is not open?
Yeah, that's what I was thinking...usually, you have close Excel completely, before all your locks are released.
If you are up for it, we can do this a completely different way:
NOTE: make sure your History file isn't in %Files%. If it is, use *.xlsx instead of just *, that way your History.xlsm will not get sucked up into %Files%
For each %CurrentItem% in Files
Launch Excel with %CurrentItem% to %ExcelInstance2%
-all the same
Close Excel %ExcelInstance2%
End (For Each)
Totally misread that, my bad 🙄
I see your issue was an easy fix in the end.
Initially when I started learning PAD I also was forgetting to put in a delay when saving excel.
For me it got worse as all my Excel files are on OneDrive ( personal ) which hasnt the fastest upload / download speeds so constantly saving / reopening the same workbook causing the workbook to lock.
Theres more, 10% of time my personal macro workbook would also get disabled without me knowing, causing other flows with a macro to fail. Now I ensure all my save Excel have a 5 second wait.
Be nice to have a global setting / handler to incorporate this delay setting.
The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.
Announcing a new way to share your feedback with the Power Automate Team.
Learn to digitize and optimize business processes and connect all your applications to share data in real time.
Join Priya Kodukula and the licensing team, super users and MVPs to find answers to your questions on Power Automate licensing.