Jump to content
TomTom35

Excel- clicking buttons

Recommended Posts

Hello.

 

I have an issue with button click in Excel. I don't know how to do it. This is Button (Form Control) object on Excel sheet. AutoIT cannot recognize it (software will see everything on worksheet as one object).

I can do it with ImageSearch.au3 but that solution is not good for me. Also running macro that is assigned to this button will not work for me.

Tried ControlClick solution but fails. Any other options/suggestions?

Share this post


Link to post
Share on other sites

explain much better  what you  want  do , probably you walk in a bad  way  , for  work with excel  you can use a udf inside of autoit , you look there ???  F1

_Excel , 

 

 

Share this post


Link to post
Share on other sites

Welcome to the forum, please provide your code or an example code for us to determine the issue and provide you with working code based on your example code.
Thank You.

Share this post


Link to post
Share on other sites

Ok.

There is Button (Form Control) on Excel sheet called 'Button 1'. I would like to click this button. Not to call macro assigned to this button and not to use ImageSearch library or use AutoIt MouseClick function. I cannot use these solutions because Excel window is not visible during script execution.

There is not much code I can provide.

#include <Excel.au3>

Local $var = "excelfile.xlsb"
Local $Sheet = "sheetName"
Global $oExcel_1 = _Excel_Open()
Global $oWorkbook = _Excel_BookOpen($oExcel_1, $var)
WinSetState("excel window title", "", @SW_MAXIMIZE)

$oWorkbook.Sheets($Sheet).Activate
$oExcel_1.Run("Macro_name")
SOME_NEXT_STEP

Here I tried with run macro command but this will not work. This macro will open user form that I want to fill up. AutoIT will freeze and will not go to next line in code until macro is finished. Macro is finished when user form is closed.

 

But if I "click" on this button simulating mouse then script will go to the next step. Problem is like I said I cannot simulate mouse click because Excel window is not visible and code with pixels parameters or image recognition will not work.

 

Basically I am looking for something like "Button1".Click() but I dont know if that's possible.

Share this post


Link to post
Share on other sites

As AutoIt is waiting until the macro has finished processing you can't click a button through the AutoIt that gets created by the Excel macro.


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (2018-12-03 - Version 1.4.11.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2019-03-02 - Version 1.3.5.0) - Download - General Help & Support - Example Scripts - Wiki
Outlook Tools (2019-01-22 - Version 0.1.0.0) - Download - General Help & Support
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
PowerPoint (2017-06-06 - Version 0.0.5.0) - Download - General Help & Support
Excel - Example Scripts - Wiki
Word - Wiki
 
Tutorials:

ADO - Wiki

 

Share this post


Link to post
Share on other sites

I am not sure if I understand your answer..

Clicking button will run macro. 

If I run this macro through AutoIT script then this script will freeze, but if I "simulate" click on button (and not run macro through $excel.run("macro")) then script will work fine.

Share this post


Link to post
Share on other sites

I got the impression that the macro creates the button - my fault.


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (2018-12-03 - Version 1.4.11.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2019-03-02 - Version 1.3.5.0) - Download - General Help & Support - Example Scripts - Wiki
Outlook Tools (2019-01-22 - Version 0.1.0.0) - Download - General Help & Support
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
PowerPoint (2017-06-06 - Version 0.0.5.0) - Download - General Help & Support
Excel - Example Scripts - Wiki
Word - Wiki
 
Tutorials:

ADO - Wiki

 

Share this post


Link to post
Share on other sites

I think you need to get the coordinates of the button via excel dom and then just do a Mouseclick on it. ControlClick could also work on that, i havent tried that.

Share this post


Link to post
Share on other sites

I assume you didn't read whole thread. I mentioned that I tried also MouseClick function. It will work but is not good for me.

I cannot use any function where I pass any "visual" parameters like position or image to search on screen because Excel window is not visible during script execution.

Share this post


Link to post
Share on other sites

@TomTom35,  I have stumbled to the same issue some time ago.  Even if you run your script not visible, when you will launch the macro (form), it will appear to the screen.  So the only workaround I have found is to run () a small script that will respond to the form, and close it, so the main script can continue its work.

 

Share this post


Link to post
Share on other sites

That can be some idea... One issue I can see right now is that to run macro I need $oExcel object and that it will be the same Excel as in 'main' script.

Example

Main script:

1. Define $oExcel, $oWorkbook etc. open file and do some operation on Excel cells

2. Run sub script that will run macro. But to run this macro I will have to define $oExcel again because syntax is $oExcel.run("mymacro1");

I can see that there is a _Excel_BookAttach and I can connect to active workbook, but I don't know right now how then run macro. I will look at this tough.

 

edit: OK I managed to get Excel object with ObjGet function :) will take a look further

Edited by TomTom35

Share this post


Link to post
Share on other sites
18 minutes ago, TomTom35 said:

2. Run sub script that will run macro. But to run this macro I will have to define $oExcel again because syntax is $oExcel.run("mymacro1");

No No, you run your macro from the main script but you run () the subscript.exe just before, it will wait for the screen to appear, do what it needs to do and close it

Share this post


Link to post
Share on other sites

I managed to run this macro but the problem is after I run it from other script I am not focused on this form so I cannot fill cells. I don't know how to get this focus.

I tried with WinActivate, also tried to send commands buttons like ALT+TAB to "switch" between windows but with no results.

What is weird, if my remote desktop is active then this WInActive function WILL work (I can see that form receive focus and next steps will work), but if this is disabled, then when I connect with remote AFTER my script is finished to just see results then I see this form and no focus on it.

 

WinSetState("WindowTitle", "", @SW_MAXIMIZE)

WILL work even if remote desktop is switched off.

 

If I put a line

ConsoleWriteError(WinGetState("WindowTitle"))

after I meximize this window without remote I get: "39". I don't know what this code means.

Share this post


Link to post
Share on other sites
3 hours ago, TomTom35 said:

I managed to run this macro but the problem is after I run it from other script I am not focused on this form so I cannot fill cells. I don't know how to get this focus.

I already told you, DO NOT run the macro from the other script, run it from the MAIN script. Before you run the macro, run the other (child) script.exe, that will wait for the window to appear, fill the form and close it.  Sigh...

Share this post


Link to post
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

×
×
  • Create New...