excel - array issue with search validation

[royalmarine]

Hey guys,

I'm trying to write a script that will allow me to enter a bit of text.

e.g. 12345

It will then search through an excel document for the text on a given column. (Column 1)

If found, it will then display all row's with data associated in the GUI below.

Here's what I have so far, and I'm struggling to find out what's going wrong.

expandcollapse popup

#include <GuiConstantsEx.au3>
#include <WindowsConstants.au3>
#include <Excel.au3>
#include <array.au3>

   Dim $test1

   Global $sFilePath1 = @ScriptDir & "\Test.xls"
   Global $Input_partnumber, $Input_Description, $Stock_Quantity
   Global $Input_ReOrder, $Input_Location, $Input_Buy
   Global $Input_Sell, $Input_Orderfrom
   
   ; Confirm if file exists
   Global $datasheet = _ExcelBookOpen($sFilePath1)
      $test1 = @error
      If @error = 1 Then
       MsgBox(0, "Error!", "Can't open excel object")
       Exit
   ElseIf @error = 2 Then
       MsgBox(0, "Error!", "File not found")
       Exit
    EndIf
    ; End check

_Main()

Func _Main()
   
   $aArray = _ExcelReadSheetToArray($datasheet) ;Using Default Parameters
   $sSearch = InputBox("", "String to find?")
   If @error Then Exit
   $sColumn = InputBox("", "Column to search?")
   If @error Then Exit
   $sColumn = Int($sColumn)
   $row = UBound($aArray)
   $sColumn = UBound($aArray, 2)
   $iIndex = _ArraySearch($aArray, $sSearch, "", "", "", "", 0, $sColumn)
   
   If @error Then
       MsgBox(0, "Not Found", $sSearch & " was not found on column " & $sColumn)
       Exit
   
   Else
       MsgBox(0, "Found", '"' & $sSearch & '" was found in the array at position ' & $iIndex & ' on column ' & $sColumn)

   Local $partnumber = _ExcelReadCell($datasheet, $row, 1)
   Local $Description = _ExcelReadCell($datasheet, $row, 2)
   Local $Quantity = _ExcelReadCell($datasheet, $row, 3)
   Local $ReOrder = _ExcelReadCell($datasheet, $row, 4)
   Local $Location = _ExcelReadCell($datasheet, $row, 5)
   Local $Buy = _ExcelReadCell($datasheet, $row, 6)
   Local $Sell = _ExcelReadCell($datasheet, $row, 7)
   Local $Orderfrom = _ExcelReadCell($datasheet, $row, 8)

   ; GUI
    GUICreate("Test", 400, 400, (@DesktopWidth - 300) / 2, (@DesktopHeight - 400) / 2, $WS_OVERLAPPEDWINDOW + $WS_CLIPSIBLINGS)
    GUICtrlCreateLabel("Part Number", 10, 10, 150, 20)
    GUICtrlCreateLabel("Description", 10, 40, 150, 20)
    GUICtrlCreateLabel("Quantity", 10, 70, 150, 20)
    GUICtrlCreateLabel("Re Order Level", 10, 100, 150, 20)
    GUICtrlCreateLabel("Location", 10, 130, 150, 20)
    GUICtrlCreateLabel("Buy Price", 10, 160, 150, 20)
    GUICtrlCreateLabel("Sell Price", 10, 190, 150, 20)
    GUICtrlCreateLabel("Order from", 10, 220, 150, 20)

    $Input_partnumber = GUICtrlCreateLabel("" & $partnumber, 180, 10, 280, 20)
    $Input_Description = GUICtrlCreateLabel("" & $Description, 180, 40, 280, 20)
    $Stock_Quantity = GUICtrlCreateLabel("" & $Quantity, 180, 70, 280, 20)
    $Input_ReOrder = GUICtrlCreateLabel("" & $ReOrder, 180, 100, 280, 20)
    $Input_Location = GUICtrlCreateLabel("" & $Location, 180, 130, 280, 20)
    $Input_Buy = GUICtrlCreateLabel("" & $Buy, 180, 160, 280, 20)
    $Input_Sell = GUICtrlCreateLabel("" & $Sell, 180, 190, 280, 20)
    $Input_Orderfrom = GUICtrlCreateLabel("" & $Orderfrom, 180, 220, 280, 20)
    EndIf

    GUISetState()
    While 1
        $msg = GUIGetMsg()
        Select
            Case $msg = $GUI_EVENT_CLOSE
                ExitLoop
             Case Else
                _ExcelBookClose($datasheet)
        EndSelect
    WEnd
    Exit

EndFunc   ;==>_Main

At the moment, the script runs, allows me to enter the required data to search for, but it returns the following

MsgBox(0, "Not Found", $sSearch & " was not found on column " & $sColumn)

Not Found 

12345 was not found on column 9

However, I enter to search on column 1.

Hopefully someone can put me out of my misery!

 

[BrewManNH]

$sColumn = UBound($aArray, 2), you sure that you want to change the value that was coming from the inputbox to the number of columns in the array?

[royalmarine]

*sigh* the joys of late nights...

Cheers Brew

[BrewManNH]

It's usually the simple things that cause sleepless nights.