aeolus187 Posted February 11, 2022 Share Posted February 11, 2022 Hi there, I am new to autoit, i have script to run macro in excel, the general process is once macro is run, there will be a pop up form need username/pwd and then process but seems the code is not continue after the script MsgBox() line, do you have any idea how i can auto fill the user id and pwd part? much appreciated! the script is like below: #include <Excel.au3> Local $oExcel_1 = _Excel_Open() Local $sWorkbook = @ScriptDir&"\57799_Generic OSA.xlsm" Local $oWorkbook = _Excel_BookOpen($oExcel_1,$sWorkbook) WinActivate($oWorkbook) Sleep(3000) $oExcel_1.run("DoIt") WinWaitActive("OSA Logon Net35") ; seems line below this are not executed MsgBox(0,"excel shown","111") ControlSetText("OSA Logon Net35","","[CLASS:WindowsForms10.EDIT.app.0.378734a; Instance:1]","e123456") ControlSetText("OSA Logon Net35","","[CLASS:WindowsForms10.EDIT.app.0.378734a; Instance:2]","e123456") _Excel_Close($oExcel_1,Default,True) Link to comment Share on other sites More sharing options...
water Posted February 11, 2022 Share Posted February 11, 2022 WinActivate($oWorkbook) is wrong. You have to pass the title of the window not the workbook object. Insert something like Local $aWinList = Winlist() _ArrayDisplay($aWinList) before WinWaitActive to make sure the window title is correct . My UDFs and Tutorials: Spoiler UDFs:Active Directory (NEW 2022-02-19 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - WikiExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example ScriptsOutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - WikiOutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - DownloadOutlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - WikiPowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - WikiTask Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki Standard UDFs:Excel - Example Scripts - WikiWord - Wiki Tutorials:ADO - WikiWebDriver - Wiki Link to comment Share on other sites More sharing options...
Developers Jos Posted February 11, 2022 Developers Share Posted February 11, 2022 Moved to the appropriate AutoIt General Help and Support forum, as the Developer General Discussion forum very clearly states: Quote General development and scripting discussions. Do not create AutoIt-related topics here, use the AutoIt General Help and Support or AutoIt Technical Discussion forums. Moderation Team SciTE4AutoIt3 Full installer Download page - Beta files Read before posting How to post scriptsource Forum etiquette Forum Rules Live for the present, Dream of the future, Learn from the past. Link to comment Share on other sites More sharing options...
Nine Posted February 11, 2022 Share Posted February 11, 2022 (edited) Running an excel macro from an AutoIt script will totally block the script until the macro is completed. Even an AdlibRegister (or any timer set) will not interrupt the macro. So the only way to interact with the form displayed in the macro is to launch (before the .run) a second process (script) that will fill the fields of the form. You can create the script on the fly and run it or you can run a pre-written script and pass parameters to it. I personally prefer the first avenue as everything is included in a single script... Edited February 11, 2022 by Nine “They did not know it was impossible, so they did it” ― Mark Twain Spoiler Block all input without UAC Save/Retrieve Images to/from Text Monitor Management (VCP commands) Tool to search in text (au3) files Date Range Picker Virtual Desktop Manager Sudoku Game 2020 Overlapped Named Pipe IPC HotString 2.0 - Hot keys with string x64 Bitwise Operations Multi-keyboards HotKeySet Recursive Array Display Fast and simple WCD IPC Multiple Folders Selector Printer Manager GIF Animation (cached) Screen Scraping Multi-Threading Made Easy Link to comment Share on other sites More sharing options...
aeolus187 Posted February 14, 2022 Author Share Posted February 14, 2022 On 2/11/2022 at 3:27 PM, water said: WinActivate($oWorkbook) is wrong. You have to pass the title of the window not the workbook object. Insert something like Local $aWinList = Winlist() _ArrayDisplay($aWinList) before WinWaitActive to make sure the window title is correct . Hi Water, the first WinActivate($oWorkbook) is use to focus on Excel and run Macro Then I set the WinWaitActive("OSA Logon Net35") which is passing the title of the window of the form. but the code is stuck at $oExcel_1.run("DoIt") this line Link to comment Share on other sites More sharing options...
aeolus187 Posted February 14, 2022 Author Share Posted February 14, 2022 On 2/11/2022 at 9:11 PM, Nine said: Running an excel macro from an AutoIt script will totally block the script until the macro is completed. Even an AdlibRegister (or any timer set) will not interrupt the macro. So the only way to interact with the form displayed in the macro is to launch (before the .run) a second process (script) that will fill the fields of the form. You can create the script on the fly and run it or you can run a pre-written script and pass parameters to it. I personally prefer the first avenue as everything is included in a single script... Thanks Nine, that explains , and per your suggestion, can you hint more for how to create the script on the fly for interact with the form displayed in the macro? much appreciated for your replay!! Link to comment Share on other sites More sharing options...
Solution Nine Posted February 14, 2022 Solution Share Posted February 14, 2022 (edited) Here a full runable example : expandcollapse popup#pragma compile(AutoItExecuteAllowed, true) #include <Constants.au3> #include <Excel.au3> #include <WinAPIFiles.au3> ; AdLibRegister and _Timer_SetTimer do not work during a macro launch Opt ("MustDeclareVars", True) Local $oExcel = _Excel_Open(True, False, Default, Default, True) If @error Then Exit MsgBox($MB_SYSTEMMODAL, "", "Error creating a new Excel application object." & @CRLF & "@error = " & @error & ", @extended = " & @extended) Local $sWorkbook = @ScriptDir & "\Test Excel Example.xls" Local $oWorkbook = _Excel_BookOpen($oExcel, $sWorkbook) If @error Then Exit MsgBox($MB_SYSTEMMODAL, "", "Error opening '" & $sWorkbook & "'." & @CRLF & "@error = " & @error & ", @extended = " & @extended) ;MsgBox (0,"",$oWorkbook.name) _CreateChild() $oExcel.Run("Launch") _Excel_Close ($oExcel) Func _CreateChild () Local $sTempFile = _WinAPI_GetTempFileName (@TempDir, "~") Local $sScript = _ "#NoTrayIcon" & @CRLF & _ "While Sleep(100)" & @CRLF & _ "If WinExists('UserForm1') Then ExitLoop" & @CRLF & _ "WEnd" & @CRLF & _ "Send ('{TAB}User')" & @CRLF & _ "Send ('{TAB}pwrd')" & @CRLF & _ "Send ('{TAB}{SPACE}')" & @CRLF & _ "FileDelete (@ScriptFullPath)" FileWrite ($sTempFile, $sScript) Run (@AutoItExe & ' /AutoIt3ExecuteScript "' & $sTempFile & '"', @TempDir, @SW_HIDE) EndFunc Test Excel Example.xls Edited February 14, 2022 by Nine “They did not know it was impossible, so they did it” ― Mark Twain Spoiler Block all input without UAC Save/Retrieve Images to/from Text Monitor Management (VCP commands) Tool to search in text (au3) files Date Range Picker Virtual Desktop Manager Sudoku Game 2020 Overlapped Named Pipe IPC HotString 2.0 - Hot keys with string x64 Bitwise Operations Multi-keyboards HotKeySet Recursive Array Display Fast and simple WCD IPC Multiple Folders Selector Printer Manager GIF Animation (cached) Screen Scraping Multi-Threading Made Easy Link to comment Share on other sites More sharing options...
aeolus187 Posted February 15, 2022 Author Share Posted February 15, 2022 20 hours ago, Nine said: Here a full runable example : expandcollapse popup#pragma compile(AutoItExecuteAllowed, true) #include <Constants.au3> #include <Excel.au3> #include <WinAPIFiles.au3> ; AdLibRegister and _Timer_SetTimer do not work during a macro launch Opt ("MustDeclareVars", True) Local $oExcel = _Excel_Open(True, False, Default, Default, True) If @error Then Exit MsgBox($MB_SYSTEMMODAL, "", "Error creating a new Excel application object." & @CRLF & "@error = " & @error & ", @extended = " & @extended) Local $sWorkbook = @ScriptDir & "\Test Excel Example.xls" Local $oWorkbook = _Excel_BookOpen($oExcel, $sWorkbook) If @error Then Exit MsgBox($MB_SYSTEMMODAL, "", "Error opening '" & $sWorkbook & "'." & @CRLF & "@error = " & @error & ", @extended = " & @extended) ;MsgBox (0,"",$oWorkbook.name) _CreateChild() $oExcel.Run("Launch") _Excel_Close ($oExcel) Func _CreateChild () Local $sTempFile = _WinAPI_GetTempFileName (@TempDir, "~") Local $sScript = _ "#NoTrayIcon" & @CRLF & _ "While Sleep(100)" & @CRLF & _ "If WinExists('UserForm1') Then ExitLoop" & @CRLF & _ "WEnd" & @CRLF & _ "Send ('{TAB}User')" & @CRLF & _ "Send ('{TAB}pwrd')" & @CRLF & _ "Send ('{TAB}{SPACE}')" & @CRLF & _ "FileDelete (@ScriptFullPath)" FileWrite ($sTempFile, $sScript) Run (@AutoItExe & ' /AutoIt3ExecuteScript "' & $sTempFile & '"', @TempDir, @SW_HIDE) EndFunc Thanks a lot!! Test Excel Example.xls 38.5 kB · 0 downloads Link to comment Share on other sites More sharing options...
Recommended Posts
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 accountSign in
Already have an account? Sign in here.
Sign In Now