KickStarter15

Excel search _ExcelReadSheetToArray: Need help

6 posts in this topic

#1 ·  Posted

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")
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

Share this post


Link to post
Share on other sites



#2 ·  Posted

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.


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites

#3 ·  Posted

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

#4 ·  Posted

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

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!")
        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 by Subz
1 person likes this

Share this post


Link to post
Share on other sites

#6 ·  Posted

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