Answered

Need help writing a flow

(0) Share

Report

Posted on by 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!

Categories:

Power Automate Desktop

I have the same question (0)

All responses (18)

Answers (1)

Sort by

MichaelAnnis 5,727 Moderator on at

Like
a
(1)

Report
Copy link

Link copied!

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

Was this reply helpful? Yes No
jwr2454 20 on at

Like
a
(0)

Report
Copy link

Link copied!

Here is the flow

Was this reply helpful? Yes No
UK_Mike on at

Like
a
(0)

Report
Copy link

Link copied!

Id remove that Launch Excel from the loop, stick it above.
Not sure where your getting the first free row from ?

Was this reply helpful? Yes No
UK_Mike on at

Like
a
(0)

Report
Copy link

Link copied!

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

Was this reply helpful? Yes No
jwr2454 20 on at

Like
a
(0)

Report
Copy link

Link copied!

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"

Was this reply helpful? Yes No
MichaelAnnis 5,727 Moderator on at

Like
a
(0)

Report
Copy link

Link copied!

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.

Was this reply helpful? Yes No
jwr2454 20 on at

Like
a
(0)

Report
Copy link

Link copied!

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

Was this reply helpful? Yes No
UK_Mike on at

Like
a
(0)

Report
Copy link

Link copied!

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

Was this reply helpful? Yes No
jwr2454 20 on at

Like
a
(0)

Report
Copy link

Link copied!

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

Was this reply helpful? Yes No
MichaelAnnis 5,727 Moderator on at

Like
a
(0)

Report
Copy link

Link copied!

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

Was this reply helpful? Yes No