Automatic Excel Ribbon Button Click - Help

[riqrat]

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

 

[Jfish]

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.

 

[JLogan3o13]

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.

[riqrat]

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.

[Jfish]

I linked to it in #3 in my post ...

 

[BrewManNH]

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.