cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
jwr2454
New Member

Need help writing a flow

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!

1 ACCEPTED SOLUTION

Accepted Solutions

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

View solution in original post

18 REPLIES 18
MichaelAnnis
Super User
Super User

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
New Member

jwr2454_0-1644602428465.png

Here is the flow 

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

Not sure where your getting the first free row from ?

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

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_0-1644603951449.png

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.

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

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

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

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

jwr2454_0-1644604469908.png

 

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

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

jwr2454
New Member

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?

Task manager, close all Excel instances.

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

jwr2454
New Member

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

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%

 

 

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.

Helpful resources

Announcements
Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

New Ideas Forum MPA.jpg

A new place to submit your Ideas for Power Automate

Announcing a new way to share your feedback with the Power Automate Team.

MPA Virtual Workshop Carousel 768x460.png

Register for a Free Workshop

Learn to digitize and optimize business processes and connect all your applications to share data in real time.

MPA Licensing.jpg

Ask your licensing questions at the Power Automate AMA!

Join Priya Kodukula and the licensing team, super users and MVPs to find answers to your questions on Power Automate licensing.

Top Kudoed Authors
Users online (2,734)