Jump to content

Help required to run the script as a batch file as per excel table values

amb2301
 Share

Recommended Posts

amb2301 Seeker

amb2301

Posted
Posted

Hi Friends,
       I am beginner in Autoit, i have created a script using Autoit, its working fine,
now i want the same script to be run as a batch, only 3 excel values to be looped from the table
i have mentioned (Commented) those values in script, also attached the excel sheet,
In excel sheet yellow highlighted cells are now to be looped with the Green highlighted cells one by one as a set
could someone help me on completing this task. 

#include <Excel.au3>
#include <GuiComboBox.au3>
Local $sWorkbook = "C:\TESTING.xlsm"
Local $oExcel = _Excel_Open()
Local $oWorkbook = _Excel_BookAttach($sWorkbook)
Local $aCell = _Excel_RangeRead($oWorkbook, Default, "E4")
Local $cCell = _Excel_RangeRead($oWorkbook, Default, "f6")   ;;; THIS VALUE TO BE LOOPED FROM EXCEL TABLE  ;;;
Local $dCell = _Excel_RangeRead($oWorkbook, Default, "e7")
Local $eCell = _Excel_RangeRead($oWorkbook, Default, "h6")   ;;; THIS VALUE TO BE LOOPED FROM EXCEL TABLE  ;;;
Local $hCell = _Excel_RangeRead($oWorkbook, Default, "g6")   ;;; THIS VALUE TO BE LOOPED FROM EXCEL TABLE  ;;;
Local $kCell = _Excel_RangeRead($oWorkbook, Default, "b16")

;;; LOOP TO START HERE  ;;;

ControlClick("Job Selection","",4144)
sleep(2000)
WinWait("Job Creation", "Job Owner")
WinActivate("Job Creation")
Sleep(2000)
ControlSend("Job Creation", "", "[CLASS:SNET$combobox; INSTANCE:5]", "0")
Sleep(1000)
ControlSend("Job Creation", "", "[CLASS:SNET$combobox; INSTANCE:5]", $aCell)
Sleep(600)
$hCombo = ControlGetHandle("Job Creation", "", "[CLASS:SNET$combobox;INSTANCE:5]")
_GUICtrlComboBox_SelectString($hCombo, $hCell)
Sleep(500)
$hCombo = ControlGetHandle("Job Creation", "", "[CLASS:SNET$combobox;INSTANCE:1]")
_GUICtrlComboBox_SelectString($hCombo, "NETWORK")
Sleep(500)
ControlSend("Job Creation", "", "[CLASS:SNET$combobox;INSTANCE:2]", "1")
Sleep(500)
ControlSend("Job Creation", "", "[CLASS:SNET$combobox;INSTANCE:2]", "5")
Sleep(500)
ControlSend("Job Creation", "", "[CLASS:Edit; INSTANCE:1]", $dCell)
Sleep(500)
ControlSend("Job Creation", "", "[CLASS:Edit; INSTANCE:1]","^{right}")
Sleep(500)
ControlSend("Job Creation", "", "[CLASS:Edit; INSTANCE:1]","{right}")
Sleep(500)
ControlSend("Job Creation", "", "[CLASS:SNET$combobox;INSTANCE:8]", "e")
Sleep(500)
ControlSend("Job Creation", "", "[CLASS:SNET$combobox;INSTANCE:9]", "e")
Sleep(500)
ControlSend("Job Creation", "", "[CLASS:SNET$combobox;INSTANCE:10]", "e")
Sleep(500)
ControlSend("Job Creation", "", "[CLASS:Edit; INSTANCE:2]", $cCell)
Sleep(500)
WinActivate("Job Creation")
Sleep(1500)
ControlSend("Job Creation", "", "[CLASS:SNET$combobox; INSTANCE:11]", "<")
Sleep(1000)
ControlSend("Job Creation", "", "[CLASS:SNET$combobox; INSTANCE:11]", "T")
Sleep(600)
$hCombo = ControlGetHandle("Job Creation", "", "[CLASS:SNET$combobox;INSTANCE:11]")
_GUICtrlComboBox_SelectString($hCombo, "TCTS TQ profil")
WinActivate("Job Creation")
Sleep(1500)
$hCombo = ControlGetHandle("Job Creation", "", "[CLASS:SNET$combobox;INSTANCE:12]")
_GUICtrlComboBox_SelectString($hCombo, $kCell)
Sleep(500)
ControlSend("Job Creation", "", "[CLASS:SNET$Edit; INSTANCE:2]", "{home}")
Sleep(300)
ControlSend("Job Creation", "", "[CLASS:SNET$Edit; INSTANCE:2]", "^{del}")
Sleep(300)
ControlSend("Job Creation", "", "[CLASS:SNET$Edit; INSTANCE:2]", "^{left}")
Sleep(300)
ControlSend("Job Creation", "", "[CLASS:SNET$Edit; INSTANCE:2]", "{home}")
Sleep(300)
ControlSend("Job Creation", "", "[CLASS:SNET$Edit; INSTANCE:2]", "^{del}")
Sleep(500)
ControlSend("Job Creation", "", "[CLASS:SNET$Edit; INSTANCE:2]", "^{del}")
Sleep(1000)
ControlSend("Job Creation", "", "[CLASS:SNET$Edit; INSTANCE:2]", $eCell)
Sleep(500)

;;; LOOP TO END HERE  ;;;

 

Testing.xlsm

Link to comment
Share on other sites
Subz Universalist

Subz

Posted
Posted (edited)

Just create an array for f6, h6 and g6 then, loop through the array.

Basic usage example: 

#include <Array.au3>
#include <Excel.au3>
Local $oExcel = _Excel_Open()
Local $oWorkbook = _Excel_BookAttach($sWorkbook)
Local $aCol[0]
_ArrayAdd($aCol, _Excel_RangeRead($oWorkbook, Default, "f6"))   ;;; THIS VALUE TO BE LOOPED FROM EXCEL TABLE  ;;;
_ArrayAdd($aCol, _Excel_RangeRead($oWorkbook, Default, "h6"))   ;;; THIS VALUE TO BE LOOPED FROM EXCEL TABLE  ;;;
_ArrayAdd($aCol, _Excel_RangeRead($oWorkbook, Default, "g6"))   ;;; THIS VALUE TO BE LOOPED FROM EXCEL TABLE  ;;;

For $i = 0 To UBound($aCol) - 1
    ConsoleWrite($aCol[$i] & @CRLF)
Next

 

Edited by Subz
Link to comment
Share on other sites
amb2301 Seeker

amb2301

Posted
  • Author
Posted

Hi Friend, 

       i am confused on this part, could you please help me fix this script, i am getting error as

(34) : ==> Variable used without being declared.: 

#include <Excel.au3>
#include <Array.au3>
#include <GuiComboBox.au3>
;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;
Local $sWorkbook = "c:\Testing.xlsm"
Local $oExcel = _Excel_Open()
Local $oWorkbook = _Excel_BookAttach($sWorkbook)
Local $aCol[0]
_ArrayAdd($aCol, _Excel_RangeRead($oWorkbook, Default, "f6"))   ;;; THIS VALUE TO BE LOOPED FROM EXCEL TABLE  ;;;
_ArrayAdd($aCol, _Excel_RangeRead($oWorkbook, Default, "h6"))   ;;; THIS VALUE TO BE LOOPED FROM EXCEL TABLE  ;;;
_ArrayAdd($aCol, _Excel_RangeRead($oWorkbook, Default, "g6"))   ;;; THIS VALUE TO BE LOOPED FROM EXCEL TABLE  ;;;
Local $aCell = _Excel_RangeRead($oWorkbook, Default, "E4")
Local $dCell = _Excel_RangeRead($oWorkbook, Default, "e7")
Local $kCell = _Excel_RangeRead($oWorkbook, Default, "b16")
;Local $cCell = _Excel_RangeRead($oWorkbook, Default, "f6")   
;Local $eCell = _Excel_RangeRead($oWorkbook, Default, "h6")   
;Local $hCell = _Excel_RangeRead($oWorkbook, Default, "g6")   

For $i = 0 To UBound($aCol) - 1
    ConsoleWrite($aCol[$i] & @CRLF)
Next

;;; LOOP TO START HERE  ;;;
ControlClick("Job Selection","",4144)
sleep(2000)
WinWait("Job Creation", "Job Owner")
WinActivate("Job Creation")
Sleep(2000)
ControlSend("Job Creation", "", "[CLASS:SNET$combobox; INSTANCE:5]", "0")
Sleep(1000)
ControlSend("Job Creation", "", "[CLASS:SNET$combobox; INSTANCE:5]", $aCell)
Sleep(600)
$hCombo = ControlGetHandle("Job Creation", "", "[CLASS:SNET$combobox;INSTANCE:5]")
_GUICtrlComboBox_SelectString($hCombo, $hCell)
Sleep(500)
$hCombo = ControlGetHandle("Job Creation", "", "[CLASS:SNET$combobox;INSTANCE:1]")
_GUICtrlComboBox_SelectString($hCombo, "NETWORK")
Sleep(500)
ControlSend("Job Creation", "", "[CLASS:SNET$combobox;INSTANCE:2]", "1")
Sleep(500)
ControlSend("Job Creation", "", "[CLASS:SNET$combobox;INSTANCE:2]", "5")
Sleep(500)
ControlSend("Job Creation", "", "[CLASS:Edit; INSTANCE:1]", $dCell)
Sleep(500)
ControlSend("Job Creation", "", "[CLASS:Edit; INSTANCE:1]","^{right}")
Sleep(500)
ControlSend("Job Creation", "", "[CLASS:Edit; INSTANCE:1]","{right}")
Sleep(500)
ControlSend("Job Creation", "", "[CLASS:SNET$combobox;INSTANCE:8]", "e")
Sleep(500)
ControlSend("Job Creation", "", "[CLASS:SNET$combobox;INSTANCE:9]", "e")
Sleep(500)
ControlSend("Job Creation", "", "[CLASS:SNET$combobox;INSTANCE:10]", "e")
Sleep(500)
ControlSend("Job Creation", "", "[CLASS:Edit; INSTANCE:2]", $cCell)
Sleep(500)
WinActivate("Job Creation")
Sleep(1500)
ControlSend("Job Creation", "", "[CLASS:SNET$combobox; INSTANCE:11]", "<")
Sleep(1000)
ControlSend("Job Creation", "", "[CLASS:SNET$combobox; INSTANCE:11]", "T")
Sleep(600)
$hCombo = ControlGetHandle("Job Creation", "", "[CLASS:SNET$combobox;INSTANCE:11]")
_GUICtrlComboBox_SelectString($hCombo, "TCTS TQ profil")
WinActivate("Job Creation")
Sleep(1500)
$hCombo = ControlGetHandle("Job Creation", "", "[CLASS:SNET$combobox;INSTANCE:12]")
_GUICtrlComboBox_SelectString($hCombo, $kCell)
Sleep(500)
ControlSend("Job Creation", "", "[CLASS:SNET$Edit; INSTANCE:2]", "{home}")
Sleep(300)
ControlSend("Job Creation", "", "[CLASS:SNET$Edit; INSTANCE:2]", "^{del}")
Sleep(300)
ControlSend("Job Creation", "", "[CLASS:SNET$Edit; INSTANCE:2]", "^{left}")
Sleep(300)
ControlSend("Job Creation", "", "[CLASS:SNET$Edit; INSTANCE:2]", "{home}")
Sleep(300)
ControlSend("Job Creation", "", "[CLASS:SNET$Edit; INSTANCE:2]", "^{del}")
Sleep(500)
ControlSend("Job Creation", "", "[CLASS:SNET$Edit; INSTANCE:2]", "^{del}")
Sleep(1000)
ControlSend("Job Creation", "", "[CLASS:SNET$Edit; INSTANCE:2]", $eCell)
Sleep(500)
;;; LOOP TO END HERE  ;;;

 

Link to comment
Share on other sites
  • Developers
Jos Universalist

Jos

Posted
  • Developers
Posted

Use au3check and  you can figure this out for yourself! ;)

Quote

>Running AU3Check (3.3.14.5)  from:C:\Program Files (x86)\AutoIt3  input:D:\Development\AutoIt3\programs\test\test\test.au3
"D:\Development\AutoIt3\programs\test\test\test.au3"(34,46) : warning: $hCell: possibly used before declaration.
_GUICtrlComboBox_SelectString($hCombo, $hCell)
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~^
"D:\Development\AutoIt3\programs\test\test\test.au3"(55,67) : warning: $cCell: possibly used before declaration.
ControlSend("Job Creation", "", "[CLASS:Edit; INSTANCE:2]", $cCell)
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~^
"D:\Development\AutoIt3\programs\test\test\test.au3"(82,72) : warning: $eCell: possibly used before declaration.
ControlSend("Job Creation", "", "[CLASS:SNET$Edit; INSTANCE:2]", $eCell)
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~^
"D:\Development\AutoIt3\programs\test\test\test.au3"(34,46) : error: $hCell: undeclared global variable.
_GUICtrlComboBox_SelectString($hCombo, $hCell)
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~^
"D:\Development\AutoIt3\programs\test\test\test.au3"(55,67) : error: $cCell: undeclared global variable.
ControlSend("Job Creation", "", "[CLASS:Edit; INSTANCE:2]", $cCell)
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~^
"D:\Development\AutoIt3\programs\test\test\test.au3"(82,72) : error: $eCell: undeclared global variable.
ControlSend("Job Creation", "", "[CLASS:SNET$Edit; INSTANCE:2]", $eCell)
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~^
D:\Development\AutoIt3\programs\test\test\test.au3 - 3 error(s), 3 warning(s)
 

so those 3 commented lines are your problem as those define variables that are missing now!

Jos

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
amb2301 Seeker

amb2301

Posted
  • Author
Posted
On 3/12/2020 at 6:02 AM, Subz said:

Just create an array for f6, h6 and g6 then, loop through the array.

Basic usage example: 

#include <Array.au3>
#include <Excel.au3>
Local $oExcel = _Excel_Open()
Local $oWorkbook = _Excel_BookAttach($sWorkbook)
Local $aCol[0]
_ArrayAdd($aCol, _Excel_RangeRead($oWorkbook, Default, "f6"))   ;;; THIS VALUE TO BE LOOPED FROM EXCEL TABLE  ;;;
_ArrayAdd($aCol, _Excel_RangeRead($oWorkbook, Default, "h6"))   ;;; THIS VALUE TO BE LOOPED FROM EXCEL TABLE  ;;;
_ArrayAdd($aCol, _Excel_RangeRead($oWorkbook, Default, "g6"))   ;;; THIS VALUE TO BE LOOPED FROM EXCEL TABLE  ;;;

For $i = 0 To UBound($aCol) - 1
    ConsoleWrite($aCol[$i] & @CRLF)
Next

 

Hi Subz, 

          Thanks for looking on my task, actually i tried to update my code using your example, but i cant....so could you please fix my code 

it will be helpful for my learning purpose

i m attaching the code here...which i tried....but its not working  :( 

MsgBox(64,"Telus Bot","Make Sure SNET & SAP is opened!!",1)
#include <Excel.au3>
#include <Array.au3>
#include <GuiComboBox.au3>
;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;
Local $sWorkbook = "c:\Testing.xlsm"
Local $oExcel = _Excel_Open()
Local $oWorkbook = _Excel_BookAttach($sWorkbook)
Local $iCol[0]
Local $jCol[0]
Local $kCol[0]

_ArrayAdd($iCol, _Excel_RangeRead($oWorkbook, Default, "f6"))   ;;; THIS VALUE TO BE LOOPED FROM EXCEL TABLE  ;;;
_ArrayAdd($jCol, _Excel_RangeRead($oWorkbook, Default, "h6"))   ;;; THIS VALUE TO BE LOOPED FROM EXCEL TABLE  ;;;
_ArrayAdd($kCol, _Excel_RangeRead($oWorkbook, Default, "g6"))   ;;; THIS VALUE TO BE LOOPED FROM EXCEL TABLE  ;;;
Local $aCell = _Excel_RangeRead($oWorkbook, Default, "E4")
Local $dCell = _Excel_RangeRead($oWorkbook, Default, "e7")
Local $kCell = _Excel_RangeRead($oWorkbook, Default, "b16")

For $x = 0 To UBound($iCol) - 1
    ConsoleWrite($iCol[$x] & @CRLF)
Next
  For $y = 0 To UBound($jCol) - 1
    ConsoleWrite($jCol[$y] & @CRLF)
next
 For $z = 0 To UBound($kCol) - 1
    ConsoleWrite($kCol[$z] & @CRLF)
 next
 

;;;;;;my function starts here,which contains values to be looped ;;;;;;;;
ControlClick("Job Selection","",4144)
sleep(2000)
WinWait("Job Creation", "Job Owner")
WinActivate("Job Creation")
Sleep(2000)
ControlSend("Job Creation", "", "[CLASS:SNET$combobox; INSTANCE:5]", "0")
Sleep(1000)
ControlSend("Job Creation", "", "[CLASS:SNET$combobox; INSTANCE:5]", $aCell)
Sleep(600)
$hCombo = ControlGetHandle("Job Creation", "", "[CLASS:SNET$combobox;INSTANCE:5]")
_GUICtrlComboBox_SelectString($hCombo, $jCol[0])
Sleep(500)
$hCombo = ControlGetHandle("Job Creation", "", "[CLASS:SNET$combobox;INSTANCE:1]")
_GUICtrlComboBox_SelectString($hCombo, "NETWORK")
Sleep(500)
ControlSend("Job Creation", "", "[CLASS:SNET$combobox;INSTANCE:2]", "1")
Sleep(500)
ControlSend("Job Creation", "", "[CLASS:SNET$combobox;INSTANCE:2]", "5")
Sleep(500)
ControlSend("Job Creation", "", "[CLASS:Edit; INSTANCE:1]", $dCell)
Sleep(500)
ControlSend("Job Creation", "", "[CLASS:Edit; INSTANCE:1]","^{right}")
Sleep(500)
ControlSend("Job Creation", "", "[CLASS:Edit; INSTANCE:1]","{right}")
Sleep(500)
ControlSend("Job Creation", "", "[CLASS:SNET$combobox;INSTANCE:8]", "e")
Sleep(500)
ControlSend("Job Creation", "", "[CLASS:SNET$combobox;INSTANCE:9]", "e")
Sleep(500)
ControlSend("Job Creation", "", "[CLASS:SNET$combobox;INSTANCE:10]", "e")
Sleep(500)
ControlSend("Job Creation", "", "[CLASS:Edit; INSTANCE:2]", $icol[0])
Sleep(500)
WinActivate("Job Creation")
Sleep(1500)
ControlSend("Job Creation", "", "[CLASS:SNET$combobox; INSTANCE:11]", "<")
Sleep(1000)
ControlSend("Job Creation", "", "[CLASS:SNET$combobox; INSTANCE:11]", "T")
Sleep(600)
$hCombo = ControlGetHandle("Job Creation", "", "[CLASS:SNET$combobox;INSTANCE:11]")
_GUICtrlComboBox_SelectString($hCombo, "TCTS TQ profil")
WinActivate("Job Creation")
Sleep(1500)
$hCombo = ControlGetHandle("Job Creation", "", "[CLASS:SNET$combobox;INSTANCE:12]")
_GUICtrlComboBox_SelectString($hCombo, $kCell)
Sleep(500)
ControlSend("Job Creation", "", "[CLASS:SNET$Edit; INSTANCE:2]", "{home}")
Sleep(300)
ControlSend("Job Creation", "", "[CLASS:SNET$Edit; INSTANCE:2]", "^{del}")
Sleep(300)
ControlSend("Job Creation", "", "[CLASS:SNET$Edit; INSTANCE:2]", "^{left}")
Sleep(300)
ControlSend("Job Creation", "", "[CLASS:SNET$Edit; INSTANCE:2]", "{home}")
Sleep(300)
ControlSend("Job Creation", "", "[CLASS:SNET$Edit; INSTANCE:2]", "^{del}")
Sleep(500)
ControlSend("Job Creation", "", "[CLASS:SNET$Edit; INSTANCE:2]", "^{del}")
Sleep(1000)
ControlSend("Job Creation", "", "[CLASS:SNET$Edit; INSTANCE:2]", $jcol[0])
Sleep(500)
;;;;;;my function ends here ;;;;;;;;

 

4 hours ago, Jos said:

Use au3check and  you can figure this out for yourself! ;)

so those 3 commented lines are your problem as those define variables that are missing now!

Jos

Thank you Jos, i spotted that errors using au3 check, i tried to update my codes as per Subz guidance, but failed, waiting for Subz Reply

Link to comment
Share on other sites
Subz Universalist

Subz

Posted
Posted

Think I understand what you were trying to achieve, you wanted to loop from F6:H11 to F11:H11?  Try: 

#include <Excel.au3>
#include <Array.au3>
#include <GuiComboBox.au3>

Local $sWorkbook = "C:\TESTING.xlsm"
Local $oExcel = _Excel_Open()
Local $oWorkbook
    $oWorkbook = _Excel_BookAttach($sWorkbook)
        ;~ If Excel Workbook isn't open, then open the workbook
        If @error Then $oWorkbook = _Excel_BookOpen($oExcel, $sWorkbook)

;~ Select Job Row Start and Job Row End
Local $iJobRowStart = 6, $iJobRowEnd = 11
;~ Copy F6 to H11 to an array
Local $aJobCells = _Excel_RangeRead($oWorkbook, Default, "F" & $iJobRowStart & ":H" & $iJobRowEnd)
Local $aCell = _Excel_RangeRead($oWorkbook, Default, "E4")
Local $dCell = _Excel_RangeRead($oWorkbook, Default, "e7")
Local $kCell = _Excel_RangeRead($oWorkbook, Default, "b16")

;~ Array is in the format:
;~ x is the row number
;~ $aJobCells[x][0] = Excel Column F
;~ $aJobCells[x][1] = Excel Column G
;~ $aJobCells[x][2] = Excel Column H

For $i = 0 To UBound($aJobCells) - 1
    ;~ Start: Debug Code allows you to view the Col:Row for debuggin (Press F5 in Scite) (not required)
    ConsoleWrite("Excel Column F" & ($iJobRowStart + $i) & " = " & $aJobCells[$i][0] & @CRLF)
    ConsoleWrite("Excel Column G" & ($iJobRowStart + $i) & " = " & $aJobCells[$i][1] & @CRLF)
    ConsoleWrite("Excel Column H" & ($iJobRowStart + $i) & " = " & $aJobCells[$i][2] & @CRLF)
    ;~ End: Debug Code

    ControlClick("Job Selection","",4144)
    sleep(2000)
    WinWait("Job Creation", "Job Owner")
    WinActivate("Job Creation")
    Sleep(2000)
    ControlSend("Job Creation", "", "[CLASS:SNET$combobox; INSTANCE:5]", "0")
    Sleep(1000)
    ControlSend("Job Creation", "", "[CLASS:SNET$combobox; INSTANCE:5]", $aCell)
    Sleep(600)
    $hCombo = ControlGetHandle("Job Creation", "", "[CLASS:SNET$combobox;INSTANCE:5]")
    _GUICtrlComboBox_SelectString($hCombo, $aJobCells[$i][1])
    Sleep(500)
    $hCombo = ControlGetHandle("Job Creation", "", "[CLASS:SNET$combobox;INSTANCE:1]")
    _GUICtrlComboBox_SelectString($hCombo, "NETWORK")
    Sleep(500)
    ControlSend("Job Creation", "", "[CLASS:SNET$combobox;INSTANCE:2]", "1")
    Sleep(500)
    ControlSend("Job Creation", "", "[CLASS:SNET$combobox;INSTANCE:2]", "5")
    Sleep(500)
    ControlSend("Job Creation", "", "[CLASS:Edit; INSTANCE:1]", $dCell)
    Sleep(500)
    ControlSend("Job Creation", "", "[CLASS:Edit; INSTANCE:1]","^{right}")
    Sleep(500)
    ControlSend("Job Creation", "", "[CLASS:Edit; INSTANCE:1]","{right}")
    Sleep(500)
    ControlSend("Job Creation", "", "[CLASS:SNET$combobox;INSTANCE:8]", "e")
    Sleep(500)
    ControlSend("Job Creation", "", "[CLASS:SNET$combobox;INSTANCE:9]", "e")
    Sleep(500)
    ControlSend("Job Creation", "", "[CLASS:SNET$combobox;INSTANCE:10]", "e")
    Sleep(500)
    ControlSend("Job Creation", "", "[CLASS:Edit; INSTANCE:2]", $aJobCells[$i][0])
    Sleep(500)
    WinActivate("Job Creation")
    Sleep(1500)
    ControlSend("Job Creation", "", "[CLASS:SNET$combobox; INSTANCE:11]", "<")
    Sleep(1000)
    ControlSend("Job Creation", "", "[CLASS:SNET$combobox; INSTANCE:11]", "T")
    Sleep(600)
    $hCombo = ControlGetHandle("Job Creation", "", "[CLASS:SNET$combobox;INSTANCE:11]")
    _GUICtrlComboBox_SelectString($hCombo, "TCTS TQ profil")
    WinActivate("Job Creation")
    Sleep(1500)
    $hCombo = ControlGetHandle("Job Creation", "", "[CLASS:SNET$combobox;INSTANCE:12]")
    _GUICtrlComboBox_SelectString($hCombo, $kCell)
    Sleep(500)
    ControlSend("Job Creation", "", "[CLASS:SNET$Edit; INSTANCE:2]", "{home}")
    Sleep(300)
    ControlSend("Job Creation", "", "[CLASS:SNET$Edit; INSTANCE:2]", "^{del}")
    Sleep(300)
    ControlSend("Job Creation", "", "[CLASS:SNET$Edit; INSTANCE:2]", "^{left}")
    Sleep(300)
    ControlSend("Job Creation", "", "[CLASS:SNET$Edit; INSTANCE:2]", "{home}")
    Sleep(300)
    ControlSend("Job Creation", "", "[CLASS:SNET$Edit; INSTANCE:2]", "^{del}")
    Sleep(500)
    ControlSend("Job Creation", "", "[CLASS:SNET$Edit; INSTANCE:2]", "^{del}")
    Sleep(1000)
    ControlSend("Job Creation", "", "[CLASS:SNET$Edit; INSTANCE:2]", $aJobCells[$i][2])
    Sleep(500)
Next

 

Link to comment
Share on other sites
  • 3 weeks later...
amb2301 Seeker

amb2301

Posted
  • Author
Posted (edited)
On 3/14/2020 at 3:51 PM, Subz said:

Think I understand what you were trying to achieve, you wanted to loop from F6:H11 to F11:H11?  Try: 

#include <Excel.au3>
#include <Array.au3>
#include <GuiComboBox.au3>

Local $sWorkbook = "C:\TESTING.xlsm"
Local $oExcel = _Excel_Open()
Local $oWorkbook
    $oWorkbook = _Excel_BookAttach($sWorkbook)
        ;~ If Excel Workbook isn't open, then open the workbook
        If @error Then $oWorkbook = _Excel_BookOpen($oExcel, $sWorkbook)

;~ Select Job Row Start and Job Row End
Local $iJobRowStart = 6, $iJobRowEnd = 11
;~ Copy F6 to H11 to an array
Local $aJobCells = _Excel_RangeRead($oWorkbook, Default, "F" & $iJobRowStart & ":H" & $iJobRowEnd)
Local $aCell = _Excel_RangeRead($oWorkbook, Default, "E4")
Local $dCell = _Excel_RangeRead($oWorkbook, Default, "e7")
Local $kCell = _Excel_RangeRead($oWorkbook, Default, "b16")

;~ Array is in the format:
;~ x is the row number
;~ $aJobCells[x][0] = Excel Column F
;~ $aJobCells[x][1] = Excel Column G
;~ $aJobCells[x][2] = Excel Column H

For $i = 0 To UBound($aJobCells) - 1
    ;~ Start: Debug Code allows you to view the Col:Row for debuggin (Press F5 in Scite) (not required)
    ConsoleWrite("Excel Column F" & ($iJobRowStart + $i) & " = " & $aJobCells[$i][0] & @CRLF)
    ConsoleWrite("Excel Column G" & ($iJobRowStart + $i) & " = " & $aJobCells[$i][1] & @CRLF)
    ConsoleWrite("Excel Column H" & ($iJobRowStart + $i) & " = " & $aJobCells[$i][2] & @CRLF)
    ;~ End: Debug Code

    ControlClick("Job Selection","",4144)
    sleep(2000)
    WinWait("Job Creation", "Job Owner")
    WinActivate("Job Creation")
    Sleep(2000)
    ControlSend("Job Creation", "", "[CLASS:SNET$combobox; INSTANCE:5]", "0")
    Sleep(1000)
    ControlSend("Job Creation", "", "[CLASS:SNET$combobox; INSTANCE:5]", $aCell)
    Sleep(600)
    $hCombo = ControlGetHandle("Job Creation", "", "[CLASS:SNET$combobox;INSTANCE:5]")
    _GUICtrlComboBox_SelectString($hCombo, $aJobCells[$i][1])
    Sleep(500)
    $hCombo = ControlGetHandle("Job Creation", "", "[CLASS:SNET$combobox;INSTANCE:1]")
    _GUICtrlComboBox_SelectString($hCombo, "NETWORK")
    Sleep(500)
    ControlSend("Job Creation", "", "[CLASS:SNET$combobox;INSTANCE:2]", "1")
    Sleep(500)
    ControlSend("Job Creation", "", "[CLASS:SNET$combobox;INSTANCE:2]", "5")
    Sleep(500)
    ControlSend("Job Creation", "", "[CLASS:Edit; INSTANCE:1]", $dCell)
    Sleep(500)
    ControlSend("Job Creation", "", "[CLASS:Edit; INSTANCE:1]","^{right}")
    Sleep(500)
    ControlSend("Job Creation", "", "[CLASS:Edit; INSTANCE:1]","{right}")
    Sleep(500)
    ControlSend("Job Creation", "", "[CLASS:SNET$combobox;INSTANCE:8]", "e")
    Sleep(500)
    ControlSend("Job Creation", "", "[CLASS:SNET$combobox;INSTANCE:9]", "e")
    Sleep(500)
    ControlSend("Job Creation", "", "[CLASS:SNET$combobox;INSTANCE:10]", "e")
    Sleep(500)
    ControlSend("Job Creation", "", "[CLASS:Edit; INSTANCE:2]", $aJobCells[$i][0])
    Sleep(500)
    WinActivate("Job Creation")
    Sleep(1500)
    ControlSend("Job Creation", "", "[CLASS:SNET$combobox; INSTANCE:11]", "<")
    Sleep(1000)
    ControlSend("Job Creation", "", "[CLASS:SNET$combobox; INSTANCE:11]", "T")
    Sleep(600)
    $hCombo = ControlGetHandle("Job Creation", "", "[CLASS:SNET$combobox;INSTANCE:11]")
    _GUICtrlComboBox_SelectString($hCombo, "TCTS TQ profil")
    WinActivate("Job Creation")
    Sleep(1500)
    $hCombo = ControlGetHandle("Job Creation", "", "[CLASS:SNET$combobox;INSTANCE:12]")
    _GUICtrlComboBox_SelectString($hCombo, $kCell)
    Sleep(500)
    ControlSend("Job Creation", "", "[CLASS:SNET$Edit; INSTANCE:2]", "{home}")
    Sleep(300)
    ControlSend("Job Creation", "", "[CLASS:SNET$Edit; INSTANCE:2]", "^{del}")
    Sleep(300)
    ControlSend("Job Creation", "", "[CLASS:SNET$Edit; INSTANCE:2]", "^{left}")
    Sleep(300)
    ControlSend("Job Creation", "", "[CLASS:SNET$Edit; INSTANCE:2]", "{home}")
    Sleep(300)
    ControlSend("Job Creation", "", "[CLASS:SNET$Edit; INSTANCE:2]", "^{del}")
    Sleep(500)
    ControlSend("Job Creation", "", "[CLASS:SNET$Edit; INSTANCE:2]", "^{del}")
    Sleep(1000)
    ControlSend("Job Creation", "", "[CLASS:SNET$Edit; INSTANCE:2]", $aJobCells[$i][2])
    Sleep(500)
Next

 

Hi Subz,

      Thank you so much for your help!!!    (sorry for the delay in reply, due to corona outbreak in my country, its complete lockdown, so we cannot able to work due to limited internet, its better now.)

script is working perfectly as per the request, in the meanwhile i tried to update some more thing on it, but failed to work properly, 

requesting to please update it.

earlier the cell ranges are limited from F6 to H11, now its not fixed till H11, it varies
so i need like.... for example: if cell H17 is blank, it should work till H16 & stop at H17 &  should not loop again.

sorry for my poor english
Thanks in Advance.

 

Edited by amb2301
update
Link to comment
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
 Share
Go to topic listing

  • Recently Browsing   0 members

    • No registered users viewing this page.
×
×
  • Create New...