Excel search _ExcelReadSheetToArray: Need help

6 posts in this topic

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:

#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")
#EndRegion ### END Koda GUI section ###

While 1

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, "")
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)
$iIndex = _ArraySearch($aArray,$sSearch,"","","","","")
If @error Then
   MsgBox(0, "", $sSearch & " not Found!")
   _ExcelBookClose($oExcel, 1, 0)

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)
_ExcelBookClose($oExcel, 1, 0)
Func ExitClick()
Func Form1Close()

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.



Share this post

Link to post
Share on other sites

With AutoIt version 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.

My UDFs and Tutorials:


Active Directory (NEW 2017-04-18 - Version - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version - Download - General Help & Support

ADO - Wiki


Share this post

Link to post
Share on other sites

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.:sweating:


Share this post

Link to post
Share on other sites


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().:>

Share this post

Link to post
Share on other sites

#5 ·  Posted (edited)

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!")
    $iIndex = _ArraySearch($aArray,$sSearch, 1, 0, 0, 0, 1, 0)
    If @error Then
        MsgBox(0, "", $sSearch & " not Found!")
       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])


Edited by Subz
1 person likes this

Share this post

Link to post
Share on other sites

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

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

Share this post

Link to post
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