cwoolsey

Problem with _Excel_RangeFind returning nothing

8 posts in this topic

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)

 

Share this post


Link to post
Share on other sites



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?


√-1 2^3 ∑ π, and it was delicious!

Share this post


Link to post
Share on other sites

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

 

Share this post


Link to post
Share on other sites

Which version of AutoIt do you run?


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

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?

Share this post


Link to post
Share on other sites

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

#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

 

Share this post


Link to post
Share on other sites

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!

Share this post


Link to post
Share on other sites

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

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

 


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

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