Jump to content
Sign in to follow this  
hutteto

Clicking Excel msgbox while routine is running

Recommended Posts

hutteto

Hey

I have created an Autoit script that runs a VBA procedure stored in an Excel workbook. This works fine, however while this vba procedure is running, the Autoit script stops and waits until the vba procedure is done running. This is causing a problem because the vba procedure contains a msgbox that requires the user to click "Yes" or "No" to indicate if they want to run the vba script. I was wanting to use Autoit to click "Yes" but Autoit is in a paused state waiting on the vba script to finish running.

How do I get around this problem WITHOUT removing the msgbox code in my vba script?

Below is the Autoit code. I have added the "ConsoleWrite("Test")" line of code to illustrate that it is never executed until the vba routine has finished running.

If WinActive("Microsoft Excel") Then

$objXL = ObjGet("", "Excel.Application")

With $objXL

$x = .Run("'G:\OpsReporting.xls'!REPORTS.Government")

ConsoleWrite("Test")

EndWith

EndIf

Share this post


Link to post
Share on other sites
martin

Hey

I have created an Autoit script that runs a VBA procedure stored in an Excel workbook. This works fine, however while this vba procedure is running, the Autoit script stops and waits until the vba procedure is done running. This is causing a problem because the vba procedure contains a msgbox that requires the user to click "Yes" or "No" to indicate if they want to run the vba script. I was wanting to use Autoit to click "Yes" but Autoit is in a paused state waiting on the vba script to finish running.

How do I get around this problem WITHOUT removing the msgbox code in my vba script?

Below is the Autoit code. I have added the "ConsoleWrite("Test")" line of code to illustrate that it is never executed until the vba routine has finished running.

If WinActive("Microsoft Excel") Then

$objXL = ObjGet("", "Excel.Application")

With $objXL

$x = .Run("'G:\OpsReporting.xls'!REPORTS.Government")

ConsoleWrite("Test")

EndWith

EndIf

You might be able to get around this by using a timer. Look up _Timer_SetTimer. In the function called by the timer check to see if the message box exists and click as needed.

The help example for _Timer_SetTimer looks more complicated than it is, and it doesn't mention that you have to have 4 parameters even though you might need none. So you need something like this

#include <timers.au3>
$t1 = _Timer_SetTimer($Gui,200,"Timer1");$Gui is your gui but if you haven't got one use 0

If WinActive("Microsoft Excel") Then
   $objXL = ObjGet("", "Excel.Application")
    With $objXL
    $x = .Run("'G:\OpsReporting.xls'!REPORTS.Government")
    
    EndWith
EndIf 

Func timer1($a,$b,$c,$d)
  ConsoleWrite("Test");or whatever
endfunc

Not tried!


Serial port communications UDF Includes functions for binary transmission and reception.printing UDF Useful for graphs, forms, labels, reports etc.Add User Call Tips to SciTE for functions in UDFs not included with AutoIt and for your own scripts.Functions with parameters in OnEvent mode and for Hot Keys One function replaces GuiSetOnEvent, GuiCtrlSetOnEvent and HotKeySet.UDF IsConnected2 for notification of status of connected state of many urls or IPs, without slowing the script.

Share this post


Link to post
Share on other sites
hutteto

Thanks. I actually wrote another .au3 file that I built to a .exe that contains a wait of 5 seconds and the Controlclicks to click on the buttons I need. I called this .exe file right before I called the VBA code. So when the VBA code is running and the prompts have popped up and the .exe file completes its 5 second wait, the controlclicks are performed via the .exe which take care of the UI controls.

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
Sign in to follow this  

×