Problem with _Excel_RangeFind returning nothing

[cwoolsey]

I am having a problem with my code. When I run the _Excel_Find it returns no errors and says that it successfully ran but then when I display the information in an _ArrayDisplay it shows that it is an empty array. Any help would be great!

;Now we will have to search the open workbook for the item we are looking for and obtain the coordinates of that item
local $tempArray = _Excel_RangeFind($Excel, $ItemOut, Default, Default, $xlWhole)
_arrayDisplay($tempArray)
$ItemOutLoc = $tempArray[0][2]
MsgBox(default, default, $ItemOutLoc)

 

[JLogan3o13]

Look at the help file, it shows that a failure will set @error based on what went wrong. Try some error checking after your call to Excel_RangeFind, what does it return?

[cwoolsey]

I did, I used one of the examples to error check. I used this code and replaced the $aResult with my $tempArray. When I run that code it displays the "Find all occurences of value '37000' (partial match). Data successfully searched." this is the default message that should play if no errors were thrown. And then the _ArrayDisplay in this example shows again an empty array table. If it found the correct input which should be Laptop in Cell A2 within the excel sheet, it should show me this information.

If @error Then Exit MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_RangeFind Example 1", "Error searching the range." & @CRLF & "@error = " & @error & ", @extended = " & @extended)
MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_RangeFind Example 1", "Find all occurrences of value '37000' (partial match)." & @CRLF & "Data successfully searched.")
_ArrayDisplay($tempArray, "Excel UDF: _Excel_RangeFind Example 1", "", 0, "|", "Sheet|Name|Cell|Value|Formula|Comment")

 

[water]

Which version of AutoIt do you run?

[cwoolsey]

I run AutoIt3. I downloaded the fresh bran new version along with the full Scite Script Editor from the website about a week ago. I'm new to AutoIt and have only had experience with it for about a week. You guys have any idea?

[cwoolsey]

Here is the entire code for this script. I have commented out a large portion because I can't run it without access to the location of the cell I am trying to find. PLEASE HELP

expandcollapse popup

#include <ButtonConstants.au3>
#include <EditConstants.au3>
#include <GUIConstantsEx.au3>
#include <StaticConstants.au3>
#include <WindowsConstants.au3>
#include <Excel.au3>
#include <Array.au3>
#include <MsgBoxConstants.au3>
#Region ### START Koda GUI section ### Form=P:\IT_Interns\Projects\InventoryProject\ItemCheckoutForm.kxf

$ItemCheckoutForm = GUICreate("Item Checkout Form", 333, 566, 384, 136)
GUISetBkColor(0xC0C0C0)
$ItemOutLabel = GUICtrlCreateLabel("What Item Is Being Checked Out:", 48, 24, 228, 20)
GUICtrlSetFont(-1, 10, 800, 0, "MS Sans Serif")
GUICtrlSetBkColor(-1, 0xFFFFFF)
$ItemOutID = GUICtrlCreateInput("", 64, 64, 193, 24)
GUICtrlSetFont(-1, 10, 400, 0, "MS Sans Serif")
$NumItemOutID = GUICtrlCreateInput("", 64, 160, 193, 24)
GUICtrlSetFont(-1, 10, 400, 0, "MS Sans Serif")
$NumItemOutLabel = GUICtrlCreateLabel("Number of This Item Being Checked Out:", 24, 120, 282, 20)
GUICtrlSetFont(-1, 10, 800, 0, "MS Sans Serif")
GUICtrlSetBkColor(-1, 0xFFFFFF)
$WhoLabel = GUICtrlCreateLabel("Who is This Item Going To:", 64, 216, 190, 20)
GUICtrlSetFont(-1, 10, 800, 0, "MS Sans Serif")
GUICtrlSetBkColor(-1, 0xFFFFFF)
$WhoID = GUICtrlCreateInput("", 64, 256, 193, 24)
GUICtrlSetFont(-1, 10, 400, 0, "MS Sans Serif")
$WhereLabel = GUICtrlCreateLabel("Where is This Item Being Assigned To:", 24, 312, 272, 20)
GUICtrlSetFont(-1, 10, 800, 0, "MS Sans Serif")
GUICtrlSetBkColor(-1, 0xFFFFFF)
$WhereID = GUICtrlCreateInput("", 64, 352, 193, 24)
GUICtrlSetFont(-1, 10, 400, 0, "MS Sans Serif")
$CommentsLabel = GUICtrlCreateLabel("Comments:", 120, 408, 80, 20)
GUICtrlSetFont(-1, 10, 800, 0, "MS Sans Serif")
GUICtrlSetBkColor(-1, 0xFFFFFF)
$CommentsID = GUICtrlCreateInput("", 64, 448, 193, 24)
GUICtrlSetFont(-1, 10, 400, 0, "MS Sans Serif")
$Next = GUICtrlCreateButton("Next", 192, 504, 99, 33)
$Back = GUICtrlCreateButton("Back", 32, 504, 99, 33)
GUISetState(@SW_SHOW)
#EndRegion ### END Koda GUI section ###
While 1
    $nMsg = GUIGetMsg()
    Switch $nMsg

        Case $GUI_EVENT_CLOSE
            Exit
        Case $Next
            ;First define all the local variables that will be used in the checkout process
            Local $ItemOut      = $ItemOutID

            Local $NumItemOut   = $NumItemOutID

            Local $Who          = $WhoID

            Local $Where        = $WhereID

            Local $Comments     = $CommentsID



            ;After the variables have been set, we will now set the filepath that the Excel file will be opened with
            Local $fPath ="P:\IT_Interns\Projects\InventoryProject\Inventory.xlsx"

            ;After the filepath is set, we will test to make sure the Inventory Excel file is not already open
            ;If it is we will then close it
            If WinExists("Inventory.xlsx - Excel") Then
                _Excel_BookSave("Inventory.xlsx - Excel")
                WinClose("Inventory.xlsx - Excel")
            ElseIf WinExists("Excel") Then
                WinClose("Excel")
            EndIf



            ;Now we will open up Excel Sheet and wait to do anything until it is opened and waiting
            $ExcelInstance = _Excel_Open()
            WinActivate("Excel")
            WinWaitActive("Excel")

            ;Now that we have a fresh excel sheet open, we will open our workbook
            $Excel = _Excel_BookOpen( $ExcelInstance, $fPath, Default, Default, True)

            ;Now we will have to search the open workbook for the item we are looking for and obtain the coordinates of that item
            local $tempArray = _Excel_RangeFind($Excel, $ItemOut, Default, Default, $xlWhole)
If @error Then Exit MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_RangeFind Example 1", "Error searching the range." & @CRLF & "@error = " & @error & ", @extended = " & @extended)
MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_RangeFind Example 1", "Find all occurrences of value '37000' (partial match)." & @CRLF & "Data successfully searched.")
_ArrayDisplay($tempArray, "Excel UDF: _Excel_RangeFind Example 1", "", 0, "|", "Sheet|Name|Cell|Value|Formula|Comment")


            $ItemOutLoc = $tempArray[0][2]
            MsgBox(default, default, $ItemOutLoc)
#cs

            ;Because we are checking this item out, we have to change the colum from A to D; The reason we do this is
            ;because colum A is the name of the item and colum D is how many of these items have been checked out
            local $StrLength = StringLen($ItemOutLoc)
            $ItemOutLocCSTRING = StringToASCIIArray($ItemOutLoc)
            if ($StrLength = 4) Then ;if the string length is 4 that means that the location is $A$# where # is the row
                $ItemOutLocCSTRING[0] = '68' ;68 is the ascii value for 'D', to change the array to DA$# where # is the row
                $ItemOutLocCSTRING[1] = $ItemOutLocCSTRING[3];Change the array to show the following, D#$# where # is the row
                Local $loopTempArray[2] ;We create a temporary 2 character row to pass the new values into (D and the #)
                $loopTempArray[0] = $ItemOutLocCSTRING[0];Pass the characters D and # from the first two of old array to first two
                $loopTempArray[1] = $ItemOutLocCSTRING[1]; of new array
                $ItemOutLoc = StringFromASCIIArray($loopTempArray) ;Change the location of the item we are looking for to the new value
            ElseIf ($StrLength = 5) Then ;This elseif will repeat the above steps but for a location that has two digits (ex. D22)
                $ItemOutLocCSTRING[0] = '68'
                $ItemOutLocCSTRING[1] = $ItemOutLocCSTRING[3]
                $ItemOutLocCSTRING[2] = $ItemOutLocCSTRING[4]
                local $loopTempArray[3]
                $loopTempArray[0] = $ItemOutLocCSTRING[0]
                $loopTempArray[1] = $ItemOutLocCSTRING[1]
                $loopTempArray[2] = $ItemOutLocCSTRING[2]
                $ItemOutLoc = StringFromASCIIArray($loopTempArray)
            endif

            MsgBox(default, default, $ItemOutLoc)
#ce



        Case $Back
            If WinExists("Item Checkout Form") Then
                WinClose("Item Checkout Form")
            EndIf

    EndSwitch
WEnd

 

[cwoolsey]

I found the problem, I was not using GUICtrlRead to obtain the actual input of the GUICtrlCreateInput, it was retaining the value 4 from ItemOutID which was the 4th input box in the form so it returned 4 if it ran successfully. GUICtrlRead allows me to retain the actual input which will then test against the object I am searching for.

Thanks for all the help!

[water]

For future use.
If you need to know your AutoIt version simply run

MsgBox(0, "", "AutoitVersion: " & @AutoItVersion)