Answered

Copy Cells From Excel Worksheet not working as it should

(0) Share

Report

Posted on by heylookitsme

When I ran this process an error occurred stating it could not paste the data.

Reviewing the error I noticed that it somehow opened up 2 instances of Excel. Which means when it tries to paste the data into the other Excel sheet it does not have the same pasting options as it normally would if I opened to workbooks and pasted data from one to the other.

Funny thing is I have this process Get files from folder/Launch Excel and then a loop to open each excel file and on the first Excel file it works fine. On the second one is where I am having the issue.

Also, I noticed a significant bug in PAD (Which is related to the issue). If data is either stepped through or the routine is stopped before it completes, it leaves open strange instances of Excel processes. In order to try and run the process again I have to open up my Task Bar and within Background Processes end the task of those items first. Surely others have had this same issue.

I also tried using Read and Write data from Excel and had the same issue.

I can run the process one time and it open the main Excel doc just fine and then after closing the file, run it again and it shows as read-only can't save. This PAD is a nightmare and I don't trust it to perform the duties as it should.

When I first saw a video on this product I was excited. Now after using it for a simple task and it taking me hours to debug it, I think the product is HUGE let down. It shouldn't surprise me, but it does.

Categories:

Power Automate Desktop

I have the same question (0)

All responses (3)

Answers (1)

Sort by

Verified answer

MichaelAnnis 5,727 Moderator on at

Like
a
(0)

Report
Copy link

Link copied!

Instead of For Each on %Files% use:
Loop 0 to %Files.Count - 1% increment of 1 'Now %Files[LoopIndex]% will always be your current FilePath/Filename.ext

Instead of Launch Excel on the files you are looping through, use the UI elements to:
Click on File -> Open -> Browse
Wait for Filename: Text Field
Send Keys for %Files[LoopIndex].Directory%{Enter}
Wait 1
Send Keys for %Files[LoopIndex].Name%{Enter}
Attach to Excel %Files[LoopIndex].Name%

This should solve all your issues.

As for PAD leaving behind junk traces of Excel even if all are closed:
I use Terminate Process before any bot that uses Excel.
1) Make sure all your Excel Windows are closed prior to running the bot
2) Add a "Display Message" action to warn the user that you are about to kill all excel and to save and close any Excel work they have open.
3) Use "Terminate Process" Excel.exe after the display message, but before the rest of the bot.

Best of luck!

Was this reply helpful? Yes No
heylookitsme 49 on at

Like
a
(0)

Report
Copy link

Link copied!

Michael,
First off my hats off to you, thank you for responding with such great care and providing a resolution.
I think for now I will wait until when/if Microsoft fixes the modules so they work correctly before I look at this product again. It is very disappointing.
Do you have any other suggestions for automating a task like this?
Actually, I think I just thought of something just now. I could just use Power Query and have it import all files in folder.

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

Like
a
(0)

Report
Copy link

Link copied!

There are a lot of options, but they all have their flaws.

RoboCorp was a great system, easy to learn, and it is in active development/partnering mode, so they are making it better every day. It is Python based which means if you can't do something, you can always write the python code straight in. My issue was, I built 6 months of bots, they forced a lab update, and after 3 hours working directly with the developers, we were not able to fix the bots. I changed to Microsoft Power Automate Desktop and I've never looked back.

OpenBots seems promising. Also a young company, but they have 500 actions (PAD has about 460). They also have a document processer. I am trying to learn it right now. I have only spent a little over an hour on it, but my feel is that it is quite clunky. A lot of clicks to get around. All variables have to be made from the variable pane; you can't just enter in a variable code in the action you are creating. Also, I posted the UserCommunity question of "Can I filter 'get files' for a specific document type?" at about 7pm last night, and I still don't have a response. On the good side, they believe in zero-licensing design which means, just like PAD, you don't pay anything until you start using their online server/scheduler. You can build and run on your desktop for free.

PAD can be troublesome, but the benefits far outweigh the struggles. You have a heavy user community that can usually get you to where you need to be. Out of the 3 that I've used, it is by far the easiest to use, and to this day, I don't think I have ran into a situation where I've thrown my hands up and gone, "nope, not possible." With that being said, the Excel functionality is pretty bad, but I write Excel VBA for everything I need in Excel, and just have PAD run the Excel Macro.

Best of luck to you!

Was this reply helpful? Yes No