Sign in to follow this  
Followers 0
Sodori

Issues with array search

7 posts in this topic

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!

#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

Share this post


Link to post
Share on other sites



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

1 person likes this

Any of my own code posted anywhere on the forum is available for use by others without any restriction of any kind._______My UDFs:

Spoiler

ArrayMultiColSort ---- Sort arrays on multiple columns
ChooseFileFolder ---- Single and multiple selections from specified path treeview listing
Date_Time_Convert -- Easily convert date/time formats, including the language used
ExtMsgBox --------- A highly customisable replacement for MsgBox
GUIExtender -------- Extend and retract multiple sections within a GUI
GUIFrame ---------- Subdivide GUIs into many adjustable frames
GUIListViewEx ------- Insert, delete, move, drag, sort, edit and colour ListView items
GUITreeViewEx ------ Check/clear parent and child checkboxes in a TreeView
Marquee ----------- Scrolling tickertape GUIs
NoFocusLines ------- Remove the dotted focus lines from buttons, sliders, radios and checkboxes
Notify ------------- Small notifications on the edge of the display
Scrollbars ----------Automatically sized scrollbars with a single command
StringSize ---------- Automatically size controls to fit text
Toast -------------- Small GUIs which pop out of the notification area

 

Share this post


Link to post
Share on other sites

#3 ·  Posted (edited)

Sodori,

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

#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 by kylomas
1 person likes this

Forum Rules         Procedure for posting code

"I like pigs.  Dogs look up to us.  Cats look down on us.  Pigs treat us as equals."

- Sir Winston Churchill

Share this post


Link to post
Share on other sites

#4 ·  Posted (edited)

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 by Sodori

Share this post


Link to post
Share on other sites

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

1 person likes this

Forum Rules         Procedure for posting code

"I like pigs.  Dogs look up to us.  Cats look down on us.  Pigs treat us as equals."

- Sir Winston Churchill

Share this post


Link to post
Share on other sites

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

Any of my own code posted anywhere on the forum is available for use by others without any restriction of any kind._______My UDFs:

Spoiler

ArrayMultiColSort ---- Sort arrays on multiple columns
ChooseFileFolder ---- Single and multiple selections from specified path treeview listing
Date_Time_Convert -- Easily convert date/time formats, including the language used
ExtMsgBox --------- A highly customisable replacement for MsgBox
GUIExtender -------- Extend and retract multiple sections within a GUI
GUIFrame ---------- Subdivide GUIs into many adjustable frames
GUIListViewEx ------- Insert, delete, move, drag, sort, edit and colour ListView items
GUITreeViewEx ------ Check/clear parent and child checkboxes in a TreeView
Marquee ----------- Scrolling tickertape GUIs
NoFocusLines ------- Remove the dotted focus lines from buttons, sliders, radios and checkboxes
Notify ------------- Small notifications on the edge of the display
Scrollbars ----------Automatically sized scrollbars with a single command
StringSize ---------- Automatically size controls to fit text
Toast -------------- Small GUIs which pop out of the notification area

 

Share this post


Link to post
Share on other sites

#7 ·  Posted (edited)

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 by Sodori

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  
Followers 0