Return index number (not index value) of an array

[vinyking]

Hi all,

I need help at this stage.

I have been able to read my array into aweb form then perfom some other activity like print web page to pdf, save file and even read/write array to excel.

I willlike to have progress bar that display in the center of the screen on top of every other window which shows the percentage of of the array completed.

I figured if i am able to have the index number (not index value) of array returned perharps this could be a lead.

below is my script and it works so fine except not been able to see the stage of work done. 

 

I am ready to learn. Cheers    

expandcollapse popup

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

;-----------------------------------------------------------------------------
;Open Excel file and capture/read Array
;-----------------------------------------------------------------------------
Local $sFilePath1 = "file path\excel.xlsx" ;This file should already exist
Local $oExcel = _ExcelBookOpen($sFilePath1, 0)


If @error = 1 Then
    MsgBox(0, "Error!", "Unable to Create the Excel Object")
    Exit
ElseIf @error = 2 Then
    MsgBox(0, "Error!", "File does not exist - Shame on you!")
    Exit
EndIf
Dim $cell[1001]

Local $aArray1 = _ExcelReadArray($oExcel, 2, 1, 230, 1) ;Direction is Vertical

; you could opt to show the array before proceeding (un-comment the next line)
;_ArrayDisplay($aArray1, "Vertical")
$oExcel.Application.Run("Delete_Created_Sheet.Delete_Created_Sheet")
Sleep(200)
$oExcel.Application.Run("Lapta")


;-----------------------------------------------------------------------------
;Excel file opened Array reading completed and Excel file still opened
;-----------------------------------------------------------------------------


;-----------------------------------------------------------------------------
;trying to open web page login form
;-----------------------------------------------------------------------------
$oIE = _IECreate("my website for form/portal/login", "", 0)
$oForm = _IEFormGetObjByName($oIE, "form name")
Local $oQuery = _IEFormElementGetObjByName($oForm, "login")
_IEFormElementSetValue($oQuery, "eco")
Local $oQuery = _IEFormElementGetObjByName($oForm, "the Password")
_IEFormElementSetValue($oQuery, "login password")
_IEFormSubmit($oForm)
Sleep(2000) ; wait 2 seconds
;-----------------------------------------------------------------------------
;form/ webpage login complteted
;-----------------------------------------------------------------------------

;-----------------------------------------------------------------------------
;Lopping through Array and executing Web form
;-----------------------------------------------------------------------------

For $i = 0 To UBound($aArray1) - 1

    _IENavigate($oIE, "new page with another form")
    $oForm1 = _IEFormGetObjByName($oIE, "form name")
    $oSelect = _IEFormElementGetObjByName($oForm1, "numbers")
    _IEAction($oSelect, "focus")
    _IEFormElementOptionSelect($oSelect, $aArray1[$i], 1, "byValue")
    $oSubmit = _IEGetObjByName($oIE, "$enter")
    _IEAction($oSubmit, "click")
    _IELoadWait($oIE)
    Local $oTable = _IETableGetCollection($oIE)
    Local $iNumTables = @extended
    If $iNumTables > 5 Then
        ;MsgBox(0, "Table Info", "There are " & $iNumTables & " tables on the page")
        Local $oTable = _IETableGetCollection($oIE, 5)
        ;create the table array from web page
        Local $aTableData = _IETableWriteToArray($oTable, True)
        Sleep(100)
        _ExcelSheetActivate($oExcel, $aArray1[$i])
        _ExcelWriteSheetFromArray($oExcel, $aTableData, 1, 1, 0, 0) ;0-Base Array parameters
    EndIf
    Sleep(1000)

Next

;-----------------------------------------------------------------------------
;end of Loop through Array and executing Web form
;-----------------------------------------------------------------------------
$oExcel.Application.Run("Change_WKSTNameLapta")
_ExcelBookClose($oExcel, 1)
_IENavigate($oIE, "different page")
_IEAction($oIE, "quit")

[bogQ]

$unbound_array = 120; instead of 120 put here your UBound($aArray1) command
For $i = 0 to $unbound_array - 1;your loop
    ;your code
    ToolTip(Round((100/$unbound_array)*$i))
    ;Sleep(100)
Next 

 

your $i variable that is declared to start from 0 will hold record curent position in array your UBound-ing

to display it like some gui with text on top you can use (look in help file for examples) SplashTextOn or some other gui commands like GUICtrlCreateProgress

so if $i holds number of current reccord and UBound holds tottal records

edit:small edit

Edited January 8, 2014 by bogQ

[vinyking]

Thanks bogQ

tried the codes and the tooltip return this value "-9223372036854775807" which do not comprehend.

details in my array are strings (not numbers)

What I am hoping to get is for example

giving the folowing array which would be run through a web form

luke

mark

john

williams

sullivan

nate

jude

rose

paul

james

10 items in array

I want the progress bar to show 10% completed after proceesing luke; 20% after processing mark etc...... 100% completed atfer james (last item in array)

I know this is quite simple for some pro. I beg your mercy if i sound so newbie.

Regards,

[bogQ]

if i set $unbound_array to have 10 elements i get normal results from upper code from 0 to 90 that i expected

so show your code that isnt working with value "-9223372036854775807" that others can test to see what is wrong, and someone will try to assist you.

Edited January 9, 2014 by bogQ

[vinyking]

thanks bogQ 

expandcollapse popup

;-----------------------------------------------------------------------------
;Lopping through Array and executing Web form
;-----------------------------------------------------------------------------
Local $unbound_array = _ExcelReadArray($oExcel, 2, 1, 10, 1) ;Direction is Vertical

For $i = 0 To $unbound_array - 1
    ;----------
    ;my code
    ;--------------
    _IENavigate($oIE, "web page")
    $oForm1 = _IEFormGetObjByName($oIE, "form name")
    $oSelect = _IEFormElementGetObjByName($oForm1, "form id")
    _IEAction($oSelect, "focus")
    _IEFormElementOptionSelect($oSelect, $unbound_array[$i], 1, "byValue")
    $oSubmit = _IEGetObjByName($oIE, "Go")
    _IEAction($oSubmit, "click")
    _IELoadWait($oIE)
    Local $oTable = _IETableGetCollection($oIE)
    Local $iNumTables = @extended
    If $iNumTables > 5 Then
        ;MsgBox(0, "Table Info", "There are " & $iNumTables & " tables on the page")
        Local $oTable = _IETableGetCollection($oIE, 5)
        ;create the table array from web page
        Local $aTableData = _IETableWriteToArray($oTable, True)
        Sleep(100)
        _ExcelSheetActivate($oExcel, $unbound_array[$i])
        _ExcelWriteSheetFromArray($oExcel, $aTableData, 1, 1, 0, 0) ;0-Base Array parameters
    EndIf

    ;tooltip code
    ToolTip(Round((100 / $aArray1) * $i)) ; i get the tool tip value -9223372036854775807
    Sleep(100)
Next

;-----------------------------------------------------------------------------
;end of Loop through Array and executing Web form
;-----------------------------------------------------------------------------
;other codes comes here after writing all data from web page to excel

Please find my extract of my script above.

All the code works with or without the tool tip. However, the tool tip value is always -9223372036854775807 as the loop run throug.

 

Actually what i want to achieve is a progress bar that shows the stages of the array already looped through the web form.

yor help is highly appreciated

[bogQ]

from first look your first two lines instead your first line of code in there shud be

Local $aArray1 = _ExcelReadArray($oExcel, 2, 1, 10, 1) ;Direction is Vertical

Local $unbound_array = UBound($aArray1); instead of 120 put here your UBound($aArray1) command

and lines

_IEFormElementOptionSelect($oSelect, $unbound_array[$i], 1, "byValue")

_ExcelSheetActivate($oExcel, $unbound_array[$i])

shud have $aArray1 instead $unbound_array

Edited January 9, 2014 by bogQ

[Gianni]

You can calculate the percentage of completion in this way:

$_Percent = Int($done / $to_do * 100)

here an example of progress bar that you can easly adapt to your case:

Local $to_do = 200
Local $done = 0
ProgressOn("Progress Meter", "done ", $done & "/" & $to_do & " ( 0 %)", -1, -1, 16) ; Progress bar On

For $done = 0 To $to_do

    $_Percent = Int($done / $to_do * 100) ; percentage of completion

    ProgressSet($_Percent, $done & "/" & $to_do & " ( " & $_Percent & " %)", "step " & $done) ; show progree

    If $_Percent = 100 Then ; when complete turn progress off
        ProgressSet(100, "Done", "Complete")
        Sleep(500)
        ProgressOff()
    EndIf
    Sleep(10) ; wait a bit
Next

Edited January 9, 2014 by PincoPanco

[vinyking]

hi PincoPanco

i have tried my hands on the code.

here is what worked eventually .

I am not sure if the progress bar code is placed correctly within the  script (though it is working)

expandcollapse popup

;withe $aArray1 already declared and its elements captured
;-----------------------------------------------------------------------------
;now Lopping through Array and executing Web form while progress is shown
;-----------------------------------------------------------------------------

ProgressOn("Progress Meter", "done ", $i & "/" & UBound($aArray1) & " ( 0 %)", -1, -1, 16) ; Progress bar On

For $i = 0 To UBound($aArray1) - 1

    
    $_Percent = Int($i / UBound($aArray1) * 100) ; percentage of completion

    ProgressSet($_Percent, $i & "/" & UBound($aArray1) & " ( " & $_Percent & " %)", "step " & $i) ; show progree

    If $_Percent = 100 Then ; when complete turn progress off
        ProgressSet(100, "Done", "Complete")
        Sleep(500)
        ProgressOff()
    EndIf
    Sleep(10) ; wait a bit

    _IENavigate($oIE, "my web form")
    $oForm1 = _IEFormGetObjByName($oIE, "form name")
    $oSelect = _IEFormElementGetObjByName($oForm1, "form id")
    _IEAction($oSelect, "focus")
    _IEFormElementOptionSelect($oSelect, $aArray1[$i], 1, "byValue")
    $oSubmit = _IEGetObjByName($oIE, "enter")
    _IEAction($oSubmit, "click")
    _IELoadWait($oIE)
    Local $oTable = _IETableGetCollection($oIE)
    Local $iNumTables = @extended
    If $iNumTables > 5 Then
        ;MsgBox(0, "Table Info", "There are " & $iNumTables & " tables on the page")
        Local $oTable = _IETableGetCollection($oIE, 5)
        ;create the table array from web page
        Local $aTableData = _IETableWriteToArray($oTable, True)
        Sleep(100)
        _ExcelSheetActivate($oExcel, $aArray1[$i])
        _ExcelWriteSheetFromArray($oExcel, $aTableData, 1, 1, 0, 0) ;0-Base Array parameters
    EndIf

    $_Percent = Int($i / UBound($aArray1) * 100) ; percentage of completion

    ProgressSet($_Percent, $i & "/" & UBound($aArray1) & " ( " & $_Percent & " %)", "step " & $i) ; show progree

    If $_Percent = 100 Then ; when complete turn progress off
        ProgressSet(100, "Done", "Complete")
        Sleep(500)
        ProgressOff()
    EndIf
    Sleep(10) ; wait a bit
Next

thanks to you all 

[Gianni]

Hi vinyking
you could simplify a bit the listing,
it should also work in this way:

;withe $aArray1 already declared and its elements captured
;-----------------------------------------------------------------------------
;now Lopping through Array and executing Web form while progress is shown
;-----------------------------------------------------------------------------
ProgressOn("Progress Meter", "done ", $i & "/" & UBound($aArray1) & " ( 0 %)", -1, -1, 16) ; Progress bar On

For $i = 0 To UBound($aArray1) - 1

    $_Percent = Int($i / UBound($aArray1) * 100) ; percentage of completion
    ProgressSet($_Percent, $i & "/" & UBound($aArray1) & " ( " & $_Percent & " %)", "step " & $i) ; show progress

_IENavigate($oIE, "my web form")
    $oForm1 = _IEFormGetObjByName($oIE, "form name")
    $oSelect = _IEFormElementGetObjByName($oForm1, "form id")
    _IEAction($oSelect, "focus")
    _IEFormElementOptionSelect($oSelect, $aArray1[$i], 1, "byValue")
    $oSubmit = _IEGetObjByName($oIE, "enter")
    _IEAction($oSubmit, "click")
    _IELoadWait($oIE)
    Local $oTable = _IETableGetCollection($oIE)
    Local $iNumTables = @extended
    If $iNumTables > 5 Then
        ;MsgBox(0, "Table Info", "There are " & $iNumTables & " tables on the page")
        Local $oTable = _IETableGetCollection($oIE, 5)
        ;create the table array from web page
        Local $aTableData = _IETableWriteToArray($oTable, True)
        Sleep(100)
        _ExcelSheetActivate($oExcel, $aArray1[$i])
        _ExcelWriteSheetFromArray($oExcel, $aTableData, 1, 1, 0, 0) ;0-Base Array parameters
    EndIf
Next
; when for-next loop has finished, show "Complete"
ProgressSet(100, "Done", "Complete")
Sleep(500) ; wait half a second
ProgressOff() ; and  then turn off the bar

bye

[vinyking]

Thanks to bogQ and PincoPanco. I have conquered this. Now moving on to the other challenges.

Regards,

Vinyking