Run excel macro with pop up form need to auto fill - (Moved)

[aeolus187]

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)

[water]

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 .

[Jos]

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

[Nine]

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

[aeolus187]

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

[aeolus187]

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!!

[Nine]

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

[aeolus187]

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