Need help writing a flow

[jwr2454]

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!

[MichaelAnnis]

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.

Thanks,

Mike

[jwr2454]

Here is the flow 

[UK_Mike]

Id remove that Launch Excel from the loop, stick it above.

Not sure where your getting the first free row from ?

[UK_Mike]

Also, I would have 2 separate Excel instances rather than attach, I find it a bit flaky.

[jwr2454]

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"

-Closes "ExcelInstance2"

[MichaelAnnis]

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.

[jwr2454]

It actually is set to overwrite. When the loop is done running, there is only 1 file left in the folder named "Extract"

[UK_Mike]

But with Launch Excel within the loop it keeps getting launched...

[jwr2454]

right, but since each file renamed as "Extract" before it launches, it should be opening a new file from the folder each time right?

[MichaelAnnis]

It could be a number of issues...can I see the double down on the "Rename File" please.

[jwr2454]

[MichaelAnnis · Accepted solution]

This is going to sounds stupid, but try giving it a "Wait (1)" command after "Rename file" and after the last "Close Excel"

[UK_Mike]

I just noticed your first Excel launch, its using an existing, is there another Excel not in the screenshot ?

[jwr2454]

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?

[UK_Mike]

Task manager, close all Excel instances.

If none found, open any Excel and close from task manager...

[jwr2454]

beautiful! I think the flow is working properly now with the wait commands in there... thank you! 

[MichaelAnnis]

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)

Delete %Files%

[UK_Mike]

Dohhhhhhhhhhhhhhh !!!!

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.