cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
ABillsy
Frequent Visitor

PAD automation actions with Excel not working

Hi 

 

I’m new to power automate desktop and this community. Having trouble trying to run desktop automation actions with excel… for example… I tried using desktop recorder, then after hitting record button, in my open excel file, clicking formulas tab then Name manager button. When I try to run the flow, it just sits at the click window tab (formula) action and does nothing… it’s like it hasn’t recognised the Excel menu “formulas” tab hi element? I’ve also tried to use the sendkeys action but didn’t appear to work either… what am I missing?

 

cheers,

ABillsy

3 ACCEPTED SOLUTIONS

Accepted Solutions

Hi @Anonymous ,

 

Thank you for your feedback.  Adding the focus window action seem to resolve my issue.  Without it, the send keys action was not working.

View solution in original post

If New and Close are the only 2 UI buttons that you are trying to use, try a Send Keys {Alt}()n and Send Keys {Alt}()c. 

Note that in Excel, to use the ribbon, the Alt key is pressed once, not held down, so hot keys appear outside the parenthesis. Another version for File -> New is {Control}(n) and another version for Close is {Alt}({F4}).

 

This next piece should have nothing to do with your UI selectors, but if you want to manipulate the Excel with PAD (Save, Close, Read, Write, etc), you must “Attach to running Excel instance” first.

 

Best of Luck!

----
If my post has answered your question, please thumbs up and mark this post as a solution.

I also offer paid consulting services at www.peakdigitran.com/appointments and you can find me on LinkedIn at https://www.linkedin.com/in/michael-annis-80903/,

View solution in original post

Anonymous
Not applicable

Hello again @ABillsy 

 

Why don't you use window instance %ExcelInstance% to be focused? You can easily identify the window of currently opened Excel instance.

 

shindomo_0-1632944552681.png


I have created a sample "For each" loop as following, and it's working for me.

 

Folder.GetFiles Folder: $'''C:\\Users\\User01\\Documents''' FileFilter: $'''*.csv''' IncludeSubfolders: False FailOnAccessDenied: True SortBy1: Folder.SortBy.NoSort SortDescending1: False SortBy2: Folder.SortBy.NoSort SortDescending2: False SortBy3: Folder.SortBy.NoSort SortDescending3: False Files=> Files
LOOP FOREACH CurrentItem IN Files
    Excel.LaunchAndOpen Path: CurrentItem Visible: True ReadOnly: False LoadAddInsAndMacros: False Instance=> ExcelInstance
    Excel.Advanced.ActivateCell Instance: ExcelInstance Column: $'''B''' Row: 2
    UIAutomation.Windows.FocusByInstanceOrHandle WindowInstance: ExcelInstance
    MouseAndKeyboard.SendKeys TextToSend: $'''{Control}({Shift}({Down}))''' DelayBetweenKeystrokes: 10 SendTextAsHardwareKeys: False
    MouseAndKeyboard.SendKeys TextToSend: $'''{Control}({F3})''' DelayBetweenKeystrokes: 10 SendTextAsHardwareKeys: False
    MouseAndKeyboard.SendKeys TextToSend: $'''{Return}''' DelayBetweenKeystrokes: 10 SendTextAsHardwareKeys: False
    MouseAndKeyboard.SendKeys TextToSend: $'''{Return}''' DelayBetweenKeystrokes: 10 SendTextAsHardwareKeys: False
    MouseAndKeyboard.SendKeys TextToSend: $'''{Alt}({F4})''' DelayBetweenKeystrokes: 10 SendTextAsHardwareKeys: False
    Excel.CloseAndSaveAs Instance: ExcelInstance DocumentFormat: Excel.ExcelFormat.OpenXmlWorkbook DocumentPath: CurrentItem.NameWithoutExtension
END

 

shindomo_1-1632944828152.png

 

Please try it.

Thank you.

View solution in original post

15 REPLIES 15
Highboy
Super User
Super User

Did you remember to click on record in the Desktop recorder? (Might be a stupid question)

Highboy_0-1632484135501.png

When you have clicked record you should see something like this (When you point on Formula)

Highboy_1-1632484193407.png

 

Yes, definitely clicked record and saw the red frame highlights as I clicked on menu item and clicked menu button.

Anonymous
Not applicable

Hello @ABillsy 

 

You can also use keyboard shortcut to navigate through the ribbon menu.

To open Name Manager, enter "Alt - M - N".

 

shindomo_0-1632484709320.png

 

Excel.Launch Visible: True LoadAddInsAndMacros: False Instance=> ExcelInstance
UIAutomation.Windows.FocusByInstanceOrHandle WindowInstance: ExcelInstance
MouseAndKeyboard.SendKeys TextToSend: $'''{Alt}(){M}{N}''' DelayBetweenKeystrokes: 10 SendTextAsHardwareKeys: False

 

VJR
Multi Super User
Multi Super User

Hi @ABillsy 

 

So as to see if there could be an easier way could you please clarify what exactly are you trying to achieve by using the Formula menu?

 






-------------------------------------------
Help fellow beings find relevant posts.
Upvote to a supporting response.
Mark as solution to an answer.

 

Hi @Anonymous ,

 

Thank you for your feedback.  Adding the focus window action seem to resolve my issue.  Without it, the send keys action was not working.

Hi @VJR ,

 

Thank you for your reply.  I was trying to use the send keys action to open the name manager in excel.  @Anonymous 's reply pointed me to the solution which was to use the Focus window action first.

 

I now have a new problem though... I am opening all excel csv files in a particular folder using the for each loop, and then for each file I want to use the send keys action.  When i use the "Focus window" action I now need the UI selector to dynamically update the name property to the excel filename... I have clicked edit selector and then New, then turned toggle on custom off then changed the name property from:

ABillsy_0-1632793362128.png

To:

 

ABillsy_1-1632793444363.png

 

Finally turning off Custom toggle...

 

ABillsy_2-1632793489717.png

 

However, when I add and use this custom dynamic UI selector with the Focus Window action, it doesn't recognise the window.  I don't get an error... it just sits on the Focus window action runnning infintely.

 

Any ideas?

Just uncheck NAME from the window (header) UI selector.  Then, it will work for the Excel in focus rather than being for a specific Excel window.

----
If my post has answered your question, please thumbs up and mark this post as a solution.

I also offer paid consulting services at www.peakdigitran.com/appointments and you can find me on LinkedIn at https://www.linkedin.com/in/michael-annis-80903/,

Thanks for the feedback @MichaelAnnis ..  I unchecked NAME from the window (header) UI selector as you suggested, but still looks like it doesn't execute the action (just sits idle ono it and does not move to next action "Send keys" which I put a stopper on)... refer my pics below in case I have missed something? (Last pic shows it idle on Focus window action, with excel file open, where i had unchecked NAME i the UI selector updated in previous pic)

 

Feedback1.PNGFeedback2.PNGFeedback3.PNGFeedback4.PNG

 

VJR
Multi Super User
Multi Super User

Hi @ABillsy 

 

Thanks for responding back with the details of the problem.

The original reason why I asked what are you doing with the Name manager is that spying the fields via UI automation directly on the Excel application is going to cause a hell lots of problems. One thing will work, then the next wont and again the same. And might work once but not the next time and so on.

 

The best option is to write a macro in an Excel and call that from the PAD's "Run Excel macro" action.

Excel's Object Model Library will be easily available to you while writing the macro and therefore would make things way easy.

 

This is an easier approach until Microsoft develops a way of writing Code stages within PAD itself just like the other RPA tools. But if you are unable to use an Excel for writing a macro due to "project reasons" then you will have to stick with trying the screen scraping and spying approach that you are already doing.

 

Thanks.

VJ

Thanks @VJR ,

 

Unfortunately in this instance, I don't have the luxury of leveraging the excel macro ☹️..  However, definitely agree that your proposed approach would be far better.  I've noticed the built in Excel RPA actions appear to work pretty well too, but may be a while before we see some added excel actions to accommodate wider automation functionality to the office365 suite.

 

For now, the focus action and send keys appear to be working quite well, except I can't get the UI selector to dynamically focus the new window (ignoring the NAME header) as @MichaelAnnis  had suggested... any ideas?

 

kind regards,

 

ABills

If New and Close are the only 2 UI buttons that you are trying to use, try a Send Keys {Alt}()n and Send Keys {Alt}()c. 

Note that in Excel, to use the ribbon, the Alt key is pressed once, not held down, so hot keys appear outside the parenthesis. Another version for File -> New is {Control}(n) and another version for Close is {Alt}({F4}).

 

This next piece should have nothing to do with your UI selectors, but if you want to manipulate the Excel with PAD (Save, Close, Read, Write, etc), you must “Attach to running Excel instance” first.

 

Best of Luck!

----
If my post has answered your question, please thumbs up and mark this post as a solution.

I also offer paid consulting services at www.peakdigitran.com/appointments and you can find me on LinkedIn at https://www.linkedin.com/in/michael-annis-80903/,
Anonymous
Not applicable

Hello @ABillsy 

 

You should set the break point (red circle) at action No. 12 "Wait 2 seconds" instead of at action No. 11 "Send keys", if you want No. 11 to be executed.

 

Or you can hit F10 key to run next action (step by step).

 

Thank you.

Anonymous
Not applicable

Hello again @ABillsy 

 

Why don't you use window instance %ExcelInstance% to be focused? You can easily identify the window of currently opened Excel instance.

 

shindomo_0-1632944552681.png


I have created a sample "For each" loop as following, and it's working for me.

 

Folder.GetFiles Folder: $'''C:\\Users\\User01\\Documents''' FileFilter: $'''*.csv''' IncludeSubfolders: False FailOnAccessDenied: True SortBy1: Folder.SortBy.NoSort SortDescending1: False SortBy2: Folder.SortBy.NoSort SortDescending2: False SortBy3: Folder.SortBy.NoSort SortDescending3: False Files=> Files
LOOP FOREACH CurrentItem IN Files
    Excel.LaunchAndOpen Path: CurrentItem Visible: True ReadOnly: False LoadAddInsAndMacros: False Instance=> ExcelInstance
    Excel.Advanced.ActivateCell Instance: ExcelInstance Column: $'''B''' Row: 2
    UIAutomation.Windows.FocusByInstanceOrHandle WindowInstance: ExcelInstance
    MouseAndKeyboard.SendKeys TextToSend: $'''{Control}({Shift}({Down}))''' DelayBetweenKeystrokes: 10 SendTextAsHardwareKeys: False
    MouseAndKeyboard.SendKeys TextToSend: $'''{Control}({F3})''' DelayBetweenKeystrokes: 10 SendTextAsHardwareKeys: False
    MouseAndKeyboard.SendKeys TextToSend: $'''{Return}''' DelayBetweenKeystrokes: 10 SendTextAsHardwareKeys: False
    MouseAndKeyboard.SendKeys TextToSend: $'''{Return}''' DelayBetweenKeystrokes: 10 SendTextAsHardwareKeys: False
    MouseAndKeyboard.SendKeys TextToSend: $'''{Alt}({F4})''' DelayBetweenKeystrokes: 10 SendTextAsHardwareKeys: False
    Excel.CloseAndSaveAs Instance: ExcelInstance DocumentFormat: Excel.ExcelFormat.OpenXmlWorkbook DocumentPath: CurrentItem.NameWithoutExtension
END

 

shindomo_1-1632944828152.png

 

Please try it.

Thank you.

Thankyou @Anonymous ,

 

Your proposed solution to use variable  %ExcelInstance% instead of UI selector worked perfectly! 😊

I feel silly for not thinking to try that instead.  Once again, thank you.

 

kind regards, ABillsy

 

Hi @MichaelAnnis ,

 

Thank you for the feedback.  I have implemented your suggestion to get rid of the new & close buttons thereby removing the need for any UI selectors.  Together with @Anonymous suggestion to use the variable %ExcelInstance% instead for the Focus window action has now resulted in the flow working perfectly. 

 

Once again, thank you both for your help 👍

Helpful resources

Announcements

Community will be READ ONLY July 16th, 5p PDT -July 22nd

Dear Community Members,   We'd like to let you know of an upcoming change to the community platform: starting July 16th, the platform will transition to a READ ONLY mode until July 22nd.   During this period, members will not be able to Kudo, Comment, or Reply to any posts.   On July 22nd, please be on the lookout for a message sent to the email address registered on your community profile. This email is crucial as it will contain your unique code and link to register for the new platform encompassing all of the communities.   What to Expect in the New Community: A more unified experience where all products, including Power Apps, Power Automate, Copilot Studio, and Power Pages, will be accessible from one community.Community Blogs that you can syndicate and link to for automatic updates. We appreciate your understanding and cooperation during this transition. Stay tuned for the exciting new features and a seamless community experience ahead!

Summer of Solutions | Week 4 Results | Results posted on July 24th

We are excited to announce the Summer of Solutions Challenge!    This challenge is kicking off on Monday, June 17th and will run for (4) weeks.  The challenge is open to all Power Platform (Power Apps, Power Automate, Copilot Studio & Power Pages) community members. We invite you to participate in a quest to provide solutions to as many questions as you can. Answers can be provided in all the communities.    Entry Period: This Challenge will consist of four weekly Entry Periods as follows (each an “Entry Period”)   - 12:00 a.m. PT on June 17, 2024 – 11:59 p.m. PT on June 23, 2024 - 12:00 a.m. PT on June 24, 2024 – 11:59 p.m. PT on June 30, 2024 - 12:00 a.m. PT on July 1, 2024 – 11:59 p.m. PT on July 7, 2024 - 12:00 a.m. PT on July 8, 2024 – 11:59 p.m. PT on July 14, 2024   Entries will be eligible for the Entry Period in which they are received and will not carryover to subsequent weekly entry periods.  You must enter into each weekly Entry Period separately.   How to Enter: We invite you to participate in a quest to provide "Accepted Solutions" to as many questions as you can. Answers can be provided in all the communities. Users must provide a solution which can be an “Accepted Solution” in the Forums in all of the communities and there are no limits to the number of “Accepted Solutions” that a member can provide for entries in this challenge, but each entry must be substantially unique and different.    Winner Selection and Prizes: At the end of each week, we will list the top ten (10) Community users which will consist of: 5 Community Members & 5 Super Users and they will advance to the final drawing. We will post each week in the News & Announcements the top 10 Solution providers.  At the end of the challenge, we will add all of the top 10 weekly names and enter them into a random drawing.  Then we will randomly select ten (10) winners (5 Community Members & 5 Super Users) from among all eligible entrants received across all weekly Entry Periods to receive the prize listed below. If a winner declines, we will draw again at random for the next winner.  A user will only be able to win once overall. If they are drawn multiple times, another user will be drawn at random.  Individuals will be contacted before the announcement with the opportunity to claim or deny the prize.  Once all of the winners have been notified, we will post in the News & Announcements of each community with the list of winners.   Each winner will receive one (1) Pass to the Power Platform Conference in Las Vegas, Sep. 18-20, 2024 ($1800 value). NOTE: Prize is for conference attendance only and any other costs such as airfare, lodging, transportation, and food are the sole responsibility of the winner. Tickets are not transferable to any other party or to next year’s event.   ** PLEASE SEE THE ATTACHED RULES for this CHALLENGE**   Week 1 Results: Congratulations to the Week 1 qualifiers, you are being entered in the random drawing that will take place at the end of the challenge.   Community MembersNumber SolutionsSuper UsersNumber Solutions Deenuji 9 @NathanAlvares24  17 @Anil_g  7 @ManishSolanki  13 @eetuRobo  5 @David_MA  10 @VishnuReddy1997  5 @SpongYe  9JhonatanOB19932 (tie) @Nived_Nambiar  8 @maltie  2 (tie)   @PA-Noob  2 (tie)   @LukeMcG  2 (tie)   @tgut03  2 (tie)       Week 2 Results: Congratulations to the Week 2 qualifiers, you are being entered in the random drawing that will take place at the end of the challenge. Week 2: Community MembersSolutionsSuper UsersSolutionsPower Automate  @Deenuji  12@ManishSolanki 19 @Anil_g  10 @NathanAlvares24  17 @VishnuReddy1997  6 @Expiscornovus  10 @Tjan  5 @Nived_Nambiar  10 @eetuRobo  3 @SudeepGhatakNZ 8     Week 3 Results: Congratulations to the Week 3 qualifiers, you are being entered in the random drawing that will take place at the end of the challenge. Week 3:Community MembersSolutionsSuper UsersSolutionsPower Automate Deenuji32ManishSolanki55VishnuReddy199724NathanAlvares2444Anil_g22SudeepGhatakNZ40eetuRobo18Nived_Nambiar28Tjan8David_MA22   Week 4 Results: Congratulations to the Week 4 qualifiers, you are being entered in the random drawing that will take place at the end of the challenge. Week 4:Community MembersSolutionsSuper UsersSolutionsPower Automate Deenuji11FLMike31Sayan11ManishSolanki16VishnuReddy199710creativeopinion14Akshansh-Sharma3SudeepGhatakNZ7claudiovc2CFernandes5 misc2Nived_Nambiar5 Usernametwice232rzaneti5 eetuRobo2   Anil_g2   SharonS2  

Check Out | 2024 Release Wave 2 Plans for Microsoft Dynamics 365 and Microsoft Power Platform

On July 16, 2024, we published the 2024 release wave 2 plans for Microsoft Dynamics 365 and Microsoft Power Platform. These plans are a compilation of the new capabilities planned to be released between October 2024 to March 2025. This release introduces a wealth of new features designed to enhance customer understanding and improve overall user experience, showcasing our dedication to driving digital transformation for our customers and partners.    The upcoming wave is centered around utilizing advanced AI and Microsoft Copilot technologies to enhance user productivity and streamline operations across diverse business applications. These enhancements include intelligent automation, AI-powered insights, and immersive user experiences that are designed to break down barriers between data, insights, and individuals. Watch a summary of the release highlights.    Discover the latest features that empower organizations to operate more efficiently and adaptively. From AI-driven sales insights and customer service enhancements to predictive analytics in supply chain management and autonomous financial processes, the new capabilities enable businesses to proactively address challenges and capitalize on opportunities.    

Updates to Transitions in the Power Platform Communities

We're embarking on a journey to enhance your experience by transitioning to a new community platform. Our team has been diligently working to create a fresh community site, leveraging the very Dynamics 365 and Power Platform tools our community advocates for.  We started this journey with transitioning Copilot Studio forums and blogs in June. The move marks the beginning of a new chapter, and we're eager for you to be a part of it. The rest of the Power Platform product sites will be moving over this summer.   Stay tuned for more updates as we get closer to the launch. We can't wait to welcome you to our new community space, designed with you in mind. Let's connect, learn, and grow together.   Here's to new beginnings and endless possibilities!   If you have any questions, observations or concerns throughout this process please go to https://aka.ms/PPCommSupport.   To stay up to date on the latest details of this migration and other important Community updates subscribe to our News and Announcements forums: Copilot Studio, Power Apps, Power Automate, Power Pages

Users online (2,582)