Excel search _ExcelReadSheetToArray: Need help

[KickStarter15]

Hi Experts,

Sorry, but I need your help on this one, Experts out there. I have this code that will search in excel, but "_ExcelReadSheetToArray" is so slow and when I input what I want to search in Excel "$input11", somethings wrong is happening, like it will search from top until it reaches to the searched input. Example: In my input to search is in row 100, now, when clicking view button, it will search from 1 to 99 until it reaches to 100 and before it show's my input details needed.

I tried looking in Excel UDF from Water but I can't determine what am I looking for. Kind of new here and searching in Excel file is my first time using autoit code.

Here's my code so far:

expandcollapse popup

#include <Excel.au3>
#Include <File.au3>
#Include <Array.au3>
#include <ButtonConstants.au3>
#include <EditConstants.au3>
#include <GUIConstantsEx.au3>
#include <GUIConstants.au3>
#include <StaticConstants.au3>
#include <WindowsConstants.au3>

Opt("GUIOnEventMode", 1)
#Region ### START Koda GUI section ### Form=
$Form1 = GUICreate("Form1", 609, 298, 192, 124)
GUISetOnEvent($GUI_EVENT_CLOSE, "Form1Close")
GUISetOnEvent($GUI_EVENT_MINIMIZE, "Form1Minimize")
GUISetOnEvent($GUI_EVENT_MAXIMIZE, "Form1Maximize")
GUISetOnEvent($GUI_EVENT_RESTORE, "Form1Restore")
$Input1 = GUICtrlCreateInput("", 48, 16, 177, 21)
$Input2 = GUICtrlCreateInput("", 48, 48, 177, 21)
$Input3 = GUICtrlCreateInput("", 48, 80, 177, 21)
$Input4 = GUICtrlCreateInput("", 48, 112, 177, 21)
$Input5 = GUICtrlCreateInput("", 48, 144, 177, 21)
$Input6 = GUICtrlCreateInput("", 232, 16, 177, 21)
$Input7 = GUICtrlCreateInput("", 232, 48, 177, 21)
$Input8 = GUICtrlCreateInput("", 232, 80, 177, 21)
$Input9 = GUICtrlCreateInput("", 232, 112, 177, 21)
$Input10 = GUICtrlCreateInput("", 232, 144, 177, 21)
$Input11 = GUICtrlCreateInput("STAN", 424, 16, 177, 21)
$View = GUICtrlCreateButton("View", 424, 46, 177, 41)
$Edit1 = GUICtrlCreateEdit("", 48, 176, 361, 113)
GUICtrlSetData(-1, "")
$LabelCeL = GUICtrlCreateLabel("Input to search", 465, 0, 90, 15)
GUICtrlSetFont(-1, 12, 400, 0, "OpenSymbol")
$Clear = GUICtrlCreateButton("Clear", 432, 224, 153, 33)
GUICtrlSetOnEvent(-1, "ClearClick")
$Exit = GUICtrlCreateButton("Exit", 433, 256, 153, 33)
GUICtrlSetOnEvent(-1, "ExitClick")
GUISetState(@SW_SHOW)
#EndRegion ### END Koda GUI section ###

While 1
    Sleep(100)
WEnd

Func ClearClick()
   GUICtrlSetData($Input1, "")
   GUICtrlSetData($Input2, "")
   GUICtrlSetData($Input3, "")
   GUICtrlSetData($Input4, "")
   GUICtrlSetData($Input5, "")
   GUICtrlSetData($Input6, "")
   GUICtrlSetData($Input7, "")
   GUICtrlSetData($Input8, "")
   GUICtrlSetData($Input9, "")
   GUICtrlSetData($Input10, "")
   GUICtrlSetData($Input11, "")
   GUICtrlSetData($Edit1, "")
EndFunc
Func ViewClick()
$sFilePath = "D:\Program\Test\MASTERLIST.xlsx"
Local $oExcel = _ExcelBookOpen($sFilePath, 0, 0)
$aArray = _ExcelReadSheetToArray($oExcel)

$sSearch = GUICtrlRead($Input11)
If @error Then Exit
If GUICtrlRead($Input11) = "" Then
   MsgBox(64,"Warning!","Input Code. Thanks!")
   _ExcelBookClose($oExcel, 1, 0)
   Return
EndIf
$iIndex = _ArraySearch($aArray,$sSearch,"","","","","")
If @error Then
   MsgBox(0, "", $sSearch & " not Found!")
   _ExcelBookClose($oExcel, 1, 0)
   Return
Else

For $i = 2 To $iIndex ;Loop
   $sCellValue = _ExcelReadCell($oExcel, $i, 1)
   $sCellValue2 = _ExcelReadCell($oExcel, $i, 2)
   $sCellValue3 = _ExcelReadCell($oExcel, $i, 3)
   $sCellValue4 = _ExcelReadCell($oExcel, $i, 4)
   $sCellValue5 = _ExcelReadCell($oExcel, $i, 5)
   $sCellValue6 = _ExcelReadCell($oExcel, $i, 6)
   $sCellValue7 = _ExcelReadCell($oExcel, $i, 7)
   $sCellValue8 = _ExcelReadCell($oExcel, $i, 8)
   $sCellValue9 = _ExcelReadCell($oExcel, $i, 9)
   $sCellValue10 = _ExcelReadCell($oExcel, $i, 10)
   $sCellValue11 = _ExcelReadCell($oExcel, $i, 11)
   GUICtrlSetData($Input1, $sCellValue)
   GUICtrlSetData($Input2, $sCellValue2)
   GUICtrlSetData($Input3, $sCellValue3)
   GUICtrlSetData($Input4, $sCellValue4)
   GUICtrlSetData($Input5, $sCellValue5)
   GUICtrlSetData($Input6, $sCellValue6)
   GUICtrlSetData($Input7, $sCellValue7)
   GUICtrlSetData($Input8, $sCellValue8)
   GUICtrlSetData($Input9, $sCellValue9)
   GUICtrlSetData($Input10, $sCellValue10)
   GUICtrlSetData($Edit1, $sCellValue11)
Next   
EndIf
_ExcelBookClose($oExcel, 1, 0)
EndFunc
Func ExitClick()
Exit
EndFunc
Func Form1Close()
Exit
EndFunc

I have 283 rows and 11 columns. See attached.

Hope, someone here can explain this to me and point what's wrong with my coding.

 

Thank you in advance guys.

KS15

MASTERLISTS.xls

[water]

With AutoIt version 3.3.12.0 the Excel and Word UDFs have been rewritten. We changed function names (a bit) and enhanced speed (dramatically).
If possible upgrade to the latest production version of AutoIt.

Details about changes can be found here and here.

[KickStarter15]

Hello Water,

Yes, I was thinking of that lately that my version must be upgraded to the latest but still I  can't because of the assigned tasks that I have. Sorry for this, but is there another way of handling my code using my current version? Please I just need to understand the fact that I was facing right now.

I tried searching for solution in every forum and do some combination (based on what I've learned so far) but still having hard time.

 

[KickStarter15]

Hi,

I figured out how can I get the correct input searched without looping from 1 to 99 in excel file. I used "For $i = $iIndex To $iIndex" to get what I need in the cell.

However, still having issue of the slowness of _ExcelReadSheetToArray().

[Subz]

You don't require a loop, just use the iIndex as the Array row number.  As I don't have the same Excel UDF, created the object manually, but this returns fairly quickly for me.

Func ViewClick()
    $sFilePath = @ScriptDir & "\MASTERLIST.xls"
    $oExcel = ObjCreate("Excel.Application")
    $oWorkBook = $oExcel.Workbooks.Open($sFilePath, Default, Default, Default, Default, Default)
    $aArray = $oExcel.Transpose($oWorkbook.ActiveSheet.UsedRange.value)
    $sSearch = GUICtrlRead($Input11)
    If @error Then Exit
    If GUICtrlRead($Input11) = "" Then
        MsgBox(64,"Warning!","Input Code. Thanks!")
        Return
    EndIf
    $iIndex = _ArraySearch($aArray,$sSearch, 1, 0, 0, 0, 1, 0)
    If @error Then
        MsgBox(0, "", $sSearch & " not Found!")
        Return
    Else
       GUICtrlSetData($Input1, $aArray[$iIndex][1])
       GUICtrlSetData($Input2, $aArray[$iIndex][2])
       GUICtrlSetData($Input3, $aArray[$iIndex][3])
       GUICtrlSetData($Input4, $aArray[$iIndex][4])
       GUICtrlSetData($Input5, $aArray[$iIndex][5])
       GUICtrlSetData($Input6, $aArray[$iIndex][6])
       GUICtrlSetData($Input7, $aArray[$iIndex][7])
       GUICtrlSetData($Input8, $aArray[$iIndex][8])
       GUICtrlSetData($Input9, $aArray[$iIndex][9])
       GUICtrlSetData($Input10, $aArray[$iIndex][10])
    EndIf
EndFunc

 

Edited March 6, 2017 by Subz

[KickStarter15]

Thanks, Subz. You really rock. Countless help from you is appreciated.

I should study more on how to handle autoit. Thanks a lot, worked perfectly and quickly response as expected. Thank you many times....