Jump to content
Sign in to follow this  
royalmarine

excel - array issue with search validation

Recommended Posts

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.

#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!

 

Share this post


Link to post
Share on other sites
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?


If I posted any code, assume that code was written using the latest release version unless stated otherwise. Also, if it doesn't work on XP I can't help with that because I don't have access to XP, and I'm not going to.
Give a programmer the correct code and he can do his work for a day. Teach a programmer to debug and he can do his work for a lifetime - by Chirag Gude
How to ask questions the smart way!

I hereby grant any person the right to use any code I post, that I am the original author of, on the autoitscript.com forums, unless I've specifically stated otherwise in the code or the thread post. If you do use my code all I ask, as a courtesy, is to make note of where you got it from.

Back up and restore Windows user files _Array.au3 - Modified array functions that include support for 2D arrays.  -  ColorChooser - An add-on for SciTE that pops up a color dialog so you can select and paste a color code into a script.  -  Customizable Splashscreen GUI w/Progress Bar - Create a custom "splash screen" GUI with a progress bar and custom label.  -  _FileGetProperty - Retrieve the properties of a file  -  SciTE Toolbar - A toolbar demo for use with the SciTE editor  -  GUIRegisterMsg demo - Demo script to show how to use the Windows messages to interact with controls and your GUI.  -   Latin Square password generator

Share this post


Link to post
Share on other sites
royalmarine

*sigh* the joys of late nights...

Cheers Brew :)

Share this post


Link to post
Share on other sites
BrewManNH

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


If I posted any code, assume that code was written using the latest release version unless stated otherwise. Also, if it doesn't work on XP I can't help with that because I don't have access to XP, and I'm not going to.
Give a programmer the correct code and he can do his work for a day. Teach a programmer to debug and he can do his work for a lifetime - by Chirag Gude
How to ask questions the smart way!

I hereby grant any person the right to use any code I post, that I am the original author of, on the autoitscript.com forums, unless I've specifically stated otherwise in the code or the thread post. If you do use my code all I ask, as a courtesy, is to make note of where you got it from.

Back up and restore Windows user files _Array.au3 - Modified array functions that include support for 2D arrays.  -  ColorChooser - An add-on for SciTE that pops up a color dialog so you can select and paste a color code into a script.  -  Customizable Splashscreen GUI w/Progress Bar - Create a custom "splash screen" GUI with a progress bar and custom label.  -  _FileGetProperty - Retrieve the properties of a file  -  SciTE Toolbar - A toolbar demo for use with the SciTE editor  -  GUIRegisterMsg demo - Demo script to show how to use the Windows messages to interact with controls and your GUI.  -   Latin Square password generator

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
Sign in to follow this  

×