Issues with array search

[Sodori]

Hi!

I have this quite large list of names, and I am designating information to them based on a website reference. Atm I input the index manually, copy the text from website, and Autoit recognises by use of ClipGet() and pastes it where index it set to.

However, I would like to automate the index bit. By simply copy the cell containing the name, and have it look for that. But, there's issues with Autoit not recognising what I find to be exact copies of one another! So I need some help! I have even tried a for loop instead, but it's slower and not working either!

expandcollapse popup

#include <IE.au3>
#include <Array.au3>
#include <Excel.au3>
#include <File.au3>
#include <Misc.au3>

Local $fPath = FileOpenDialog("Select the excel file!", @ScriptDir , "All (*.*)")
If @error Then Exit

Local $oAppl = _Excel_Open()
If @error Then Exit MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_RangeRead Example", "Error creating the Excel application object." & @CRLF & "@error = " & @error & ", @extended = " & @extended)

Local $oWorkbook = _Excel_BookOpen($oAppl, $fPath, False, True)
If @error Then
   MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_RangeRead Example", "Error opening workbook '" & $fPath & "'" & @CRLF & "@error = " & @error & ", @extended = " & @extended)
    _Excel_Close($oAppl)
    Exit
 EndIf

 Local $aArray = _Excel_RangeRead($oWorkbook, "Blad1")

 _ArrayColInsert($aArray, 1)

ClipPut("")

Local $time = TimerInit()

While 1
   If TimerDiff($time) > 1000 Then
      Local $search = _ArraySearch($aArray, ClipGet())
      If @error = 0 Then ConsoleWrite($search & @TAB & ClipGet() & @LF)
      If @error <> 0 Then ConsoleWrite('"' & ClipGet() & '"' & @TAB & "Nothing found!" & @LF)
      $time = TimerInit()
   Else
      Sleep(50)
   EndIf
WEnd

I have fabricated a file as well to use with this. Partly for testing and debugging purposes trying to figure this out. I have attached said file. Yes there's a lot of names in it, and yet that's just a third of the scale of the actual document. So you see why I'd like a bit of assistance xD

Test.xlsx

[Melba23]

Sodori,

What you have posted is not a lot of use - we might well get an array, but we have no indication of what you are searching for within it.

What I suggest you do is to create your array and use _FileWriteToArray to get it into a file you can upload. Then type a selection of matches that do not, but should, work for you into another file That way we can load the array and check why the matches fail.

M23

[kylomas]

Sodori,

CLipGet() returns a string suffixed with @CRLF.  There are line feeds in the spreadsheet.  The following gets you by this problem...

expandcollapse popup

#include <IE.au3>
#include <Array.au3>
#include <Excel.au3>
#include <File.au3>
#include <Misc.au3>

Local $fPath = FileOpenDialog("Select the excel file!", @ScriptDir , "All (*.*)")
If @error Then Exit

Local $oAppl = _Excel_Open()
If @error Then Exit MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_RangeRead Example", "Error creating the Excel application object." & @CRLF & "@error = " & @error & ", @extended = " & @extended)

Local $oWorkbook = _Excel_BookOpen($oAppl, $fPath, False, True)
If @error Then
   MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_RangeRead Example", "Error opening workbook '" & $fPath & "'" & @CRLF & "@error = " & @error & ", @extended = " & @extended)
    _Excel_Close($oAppl)
    Exit
 EndIf

 Local $aArray = _Excel_RangeRead($oWorkbook, "Blad1")
 ConsoleWrite(@error & @CRLF)

 _ArrayColInsert($aArray, 1)

ClipPut("")

Local $time = TimerInit()

While 1
   If TimerDiff($time) > 1000 Then
      Local $search = _ArraySearch($aArray, stringreplace(ClipGet(),@CRLF,'')) ; <--- remove @CRLF
      If @error = 0 Then ConsoleWrite($search & @TAB & ClipGet() & @LF)
      If @error <> 0 Then ConsoleWrite('"' & ClipGet() & '"' & @TAB & "Nothing found!" & @LF)
      $time = TimerInit()
   Else
      Sleep(50)
   EndIf
WEnd

Also, you are opening workbook object "Blad1" but that worksheet does not exist in the example spreadsheet.  I renamed it to get this to work.  You will get better response if you post runnable reproducers.

kylomas

edit:  Note - there is an _Excel_RangeFind function that might be useful.

Edited November 6, 2014 by kylomas

[Sodori]

Thank you booth, great tips! And Kylomas, that did the trick. Quite frankfully, I should have thought of that myself as I've noticed in the past copying a cell includes a linefeed. But oh well. Is there any way now though, to make $search not return 0 when clip is empty.. Would like to believe $iCompare could handle that one in _ArraySearch, but alas. Maybe make it an 1 array instead of 0 array I guess (?) That way the index returns exactly where it stands in excel, so thus I guess it's more convenient. Though it feels like there should be a better solution to it xD

PS. My Excel runs in Swedish, but I wanted to rename it to Sheet1 for your convenience looking at it. As you have noticed, I failed in such and caused more inconvenience rather than convenience ^^

Edited November 7, 2014 by Sodori

[kylomas]

If the zero offset is a problem then just reference the offset as $search + 1...

[Melba23]

Sodori,

 

to make $search not return 0 when clip is empty

Just check yourself whether there is any content and only search if there is something on the clipboard - something like this should work (untested):

While 1
    If TimerDiff($time) > 1000 Then
        If ClipGet() Then
            Local $search = _ArraySearch($aArray, StringReplace(ClipGet(), @CRLF, ''))
            If @error Then
                ConsoleWrite('"' & ClipGet() & '"' & @TAB & "Nothing found!" & @LF)
            Else
                ConsoleWrite($search & @TAB & ClipGet() & @LF)
            ElseIf
        Else
            ConsoleWrite("Clipboard empty!" & @LF)
        EndIf
        $time = TimerInit()
    Else
        Sleep(50)
    EndIf
WEnd

M23

[Sodori]

Sodori,

 

Just check yourself whether there is any content and only search if there is something on the clipboard - something like this should work (untested):

While 1
    If TimerDiff($time) > 1000 Then
        If ClipGet() Then
            Local $search = _ArraySearch($aArray, StringReplace(ClipGet(), @CRLF, ''))
            If @error Then
                ConsoleWrite('"' & ClipGet() & '"' & @TAB & "Nothing found!" & @LF)
            Else
                ConsoleWrite($search & @TAB & ClipGet() & @LF)
            ElseIf
        Else
            ConsoleWrite("Clipboard empty!" & @LF)
        EndIf
        $time = TimerInit()
    Else
        Sleep(50)
    EndIf
WEnd

M23

 

 

I did try that.. I believe.. the whole ordeal with "" and 0 are equal partners means that an empty clipboard will return zero. Quite frankly don't ask me why, I tried with some more statements like ClipGet() <> "" and stuff but it kept finding a loophole through them. Like Kylomas said, you need an offset. However does not work just to add an extra unit to $search, not in my case at least. Because then it got a bit screwy some times meaning the code aren't idiot proof. Fortunately my main code had a headliner with the array to gather values from. So it kinda had it's offset natively. In other means the values to search by started at one not zero. So all I had to do was to check if $search ever got greater than 0 and voila. Pretty much anything else does not work, one way or another. Sort of a nuisance that _ArraySearch is that way, but I digress. I now know how to bypass it at least!

Edited November 7, 2014 by Sodori