Jump to content

Automatic Excel Ribbon Button Click - Help


Recommended Posts

Hello. Very new with AutoIT. Completed one nice FileZilla automated scripted and think this AutoIT stuff is pretty cool.  But now I'm stuck with my next script.

In my Excel 2010 app that is already open, I have to go through a bunch of sequences involving clicking on a button on the ribbon that runs some VBA code.  The VBA code is 3rd party, so tinkering with it is out of the question.

I started the Record Macro and then went to click on one of the ribbon buttons to see what kind of VBA code it would present me, but it turned out blank.

How would I go about automating the clicking of a custom ribbon button in AutoIT?

My script so far looks like this:

 

#include <Excel.au3>
; Script Start - Add your code below here

Local $oExcel = _Excel_Open()
Local $oWorkbook = _Excel_BookOpen($oExcel, "C:\Users\Rick\Desktop\HURST.xltm")

With the Hurst workbook open, there is a button on the ribbon under the Chart Tools[Designer] tab with the title RUN HURST.

I usually click on this button to bring up an OPEN dialog in order to select a file to load. 

I'm stuck at the 'click on this button' part.

TIA

 

Link to comment
Share on other sites

1. I get that you are trying to click a button but you also made mention of a macro that you recorded.  If that worked you could probably run it from AutoIt.  This is from an older post but something like this: $oExcel.Run("Macro1") ;run the macro

2. Can you get any information about the button if you run Au3Info?

3. If you can't see that button with Au3Info then you could try this.

 

Build your own poker game with AutoIt: pokerlogic.au3 | Learn To Program Using FREE Tools with AutoIt

Link to comment
Share on other sites

  • Moderators

The ribbon is notoriously difficult to automate. I believe you will find a couple of examples in the IUIAutomation thread in the Examples forum.

That said, if you're just launching the VBA you should be able to do so without automating the GUI.

"Profanity is the last vestige of the feeble mind. For the man who cannot express himself forcibly through intellect must do so through shock and awe" - Spencer W. Kimball

How to get your question answered on this forum!

Link to comment
Share on other sites

The problem with trying to run the VBA code directly is that this code actually relies on the worksheet and tabs being at a particular focus because the VBA is running ALT-key commands.  If an attempt to run the VBA directly is made, that would change the focus and the ALT-key commands would only cause a bunch of beeps rather than select something.

For example, in a certain view (the Developer tab is selected and the focus is on the worksheet itself), sending an Alt-K might trigger a particular menu item or third-party button.

But if one tries to run the VBA directly, that would require clicking on this and opening that first, which changes options available using the Alt-key codes, thus it won't work.

This is why I need to click the ribbon button that triggers the VBA code directly, since the VBA code knows what to do from that particular focus view.

The alternative would be a complete rewrite of the VBA and putting the Alt-key in control of AutoIT, but it is a third-part (protected) VBA code and I don't want to mess with it.

I will try to find the IUIAutomation thread you mentioned. 

Thanks.

Link to comment
Share on other sites

Instead of clicking the ribbon buttons in Excel, why not use the Lotus 1-2-3 shortcut key to activate the ribbon items? Open Excel, hit "/" to turn on the tab letters, Send the letter that corresponds to the tab you want to activate, then Send the command code needed to activate the ribbon item.

If I posted any code, assume that code was written using the latest release version unless stated otherwise. Also, if it doesn't work on XP I can't help with that because I don't have access to XP, and I'm not going to.
Give a programmer the correct code and he can do his work for a day. Teach a programmer to debug and he can do his work for a lifetime - by Chirag Gude
How to ask questions the smart way!

I hereby grant any person the right to use any code I post, that I am the original author of, on the autoitscript.com forums, unless I've specifically stated otherwise in the code or the thread post. If you do use my code all I ask, as a courtesy, is to make note of where you got it from.

Back up and restore Windows user files _Array.au3 - Modified array functions that include support for 2D arrays.  -  ColorChooser - An add-on for SciTE that pops up a color dialog so you can select and paste a color code into a script.  -  Customizable Splashscreen GUI w/Progress Bar - Create a custom "splash screen" GUI with a progress bar and custom label.  -  _FileGetProperty - Retrieve the properties of a file  -  SciTE Toolbar - A toolbar demo for use with the SciTE editor  -  GUIRegisterMsg demo - Demo script to show how to use the Windows messages to interact with controls and your GUI.  -   Latin Square password generator

Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
 Share

  • Recently Browsing   0 members

    • No registered users viewing this page.
×
×
  • Create New...