Jump to content

_ArraySearch doesn't work second time in loop


Recommended Posts

The purpose of the script is to copy the content of a cell in a program to clipboard. This clipboard value is searched in an array by using _ArraySearch. 

This works similar to VLOOKUP in Excel.

The result of the second column is then being pasted in the program's next cell. Then the script continues to the next line doing the same thing again in a loop.

The problem is that the script only works once, for the first line only.

Here's the code:

#include <Excel.au3>
#include <MsgBoxConstants.au3>
#include <Array.au3>

Local $oWorkbook = _Excel_BookAttach("C:\Users\Steven\Desktop\Account Sales invoer.xlsx")

Local $x = WinGetHandle("Toevoegen Account Sale data")
Local $y = WinGetHandle("Account Sales invoer")

WinActivate($x)
Local $aArray = _Excel_RangeRead($oWorkbook)
;_ArrayDisplay($aArray, "2D display") ; Debug works fine

For $i = 1 to 3 ; Number of tries

    ; Copy
    ControlSend ($x, "", "", "^c")
    Local $iIndex = _ArraySearch($aArray, ClipGet())
    
    If @error Then MsgBox(0, "Not Found"," Not found--error="&@error)
      
    msgbox(0, "", ClipGet() & " " & $iIndex) ; Debug
    _ArrayToClip($aArray, "", $iIndex, $iIndex, "", 1, 1)

    ; Paste
    Sleep(100)
    ControlSend($x, "", "", "{TAB 2}")
    ControlSend($x, "", "", "v")
    ControlSend($x, "", "", "{BACKSPACE}")
    Sleep(100)
    ControlSetText($x, "", "", ClipGet())
    ControlSend($x, "", "", "{DOWN}")
    ControlSend($x, "", "", "{LEFT 2}")

Next

 

See below results for msgbox:

This one is correct. Value 60840 is found in the array list, returning index 144.

p.png?fv_content=true&size_mode=5

 

Next line gives error 6: value not found? It's in the list though.

p.png?fv_content=true&size_mode=5

The clipboard function works correct as being shown:

p.png?size=1280x960&size_mode=3

Excel file:

Spoiler
55182 16
60703 104
60704 104
60705 1
60706 104
60707 73
60708 3
60709 104
60710 104
60711 104
60712 94
60713 4
55183 17
60715 104
60716 104
60717 104
60718 104
60719 104
60720 40
60721 88
60722 5
60714 200
60723 80
60724 80
60726 90
60727 80
60728 40
60730 12
60731 300
60732 5
60729 106
60725 80
55184 14
60735 104
60736 104
60737 99
60738 104
60739 76
60740 5
60733 104
60734 32
60741 80
60742 80
60743 75
60747 104
60748 24
60749 67
60750 10
60744 100
60745 100
60746 100
55185 2
55186 19
60751 104
60752 104
60757 104
60758 117
60759 12
60753 104
60754 82
60755 80
60756 70
60768 104
60769 110
60770 40
60771 13
60765 104
60766 26
60767 250
60764 75
60760 2
60761 100
60762 100
60763 100
55187 2
55188 25
60774 104
60776 75
60777 90
60778 90
60779 80
60780 80
60781 19
60772 100
60775 38
60773 104
60785 100
60784 100
60783 100
60782 60
60786 80
60787 75
60788 200
60789 80
60790 28
60791 21
55189 3
55190 27
60794 104
60797 104
60798 104
60796 75
60795 118
60799 104
60800 75
60801 59
60802 16
60792 120
60793 50
60809 100
60810 104
60811 104
60812 104
60813 48
60814 72
60815 15
55191 1
60803 75
60804 75
60805 100
60806 100
60807 100
60808 100
55192 3
55193 19
60817 100
60818 100
60819 100
60816 50
60820 74
60823 108
60824 75
60825 11
60821 75
60822 80
60829 80
60830 80
60831 75
60832 70
60833 69
60834 11
60826 75
60827 75
60828 40
60840 75
60841 104
60842 104
60843 80
60844 143
60845 11
55196 5
55197 19
55194 1
55195 1
60835 5
60836 120
60837 104
60838 104
60839 33
60852 71
60851 80
60853 5
60854 104
60855 104
60856 81
60857 156
60858 16
60846 100
60847 100
60848 100
60849 100
60850 51
60859 1
60860 100
60861 100
60862 100
60867 12
60863 104
60864 104
60865 106
60866 128
60872 75
60873 250
60874 111
60875 140
60876 10
55198 5
55199 8
55200 2
55201 7
60868 100
60869 100
60870 100
60871 30
55202 4
55203 3
55206 1
55204 8
55205 4
60878 24
60877 75
60879 104
60880 98
60881 104
60882 104
60883 104
60884 44
60885 183
60886 14
60888 100
60889 100
60890 100
60891 100
60887 12
60892 35
60893 75
60894 75
60895 55
60896 102
60897 184
60898 18
55207 6
60903 64
60904 104
60905 35
60906 154
60907 14
55208 11
60899 1
60900 100
60901 100
60902 100
60908 75
60909 40
60910 1
60911 57
55209 4
60914 93
60915 160
60916 10
60913 100
60912 100
60920 75
60921 56
60922 175
60923 15
60919 120
55210 6
55211 12
60917 100
60918 120
60924 100
60925 100
60928 2
60926 100
60927 100
60930 120
60931 53
60932 159
60933 17
60929 75

Please help me to get this working. Thanks in advance!

Link to post
Share on other sites

Instead of using multiple, hard to follow, ControlSend, just read and write the Excel cell directly with something like :

Local $sString = _Excel_RangeRead($oWorkbook, Default, "A1")
Local $iIndex = _ArraySearch($aArray, $sString)
_Excel_RangeWrite($oWorkbook, Default, $aArray[$iIndex][0],"C1")

Change your script to that approach, it would be easier to help you afterwards.

ps. your images do not show...

Edited by Nine
Link to post
Share on other sites

I see.  I think the problem is you are copying too rapidly.  Put a sleep (400) after ControlSend($x, "", "", "{LEFT 2}").

Have you tested if you can read the controls in the other program, using Au3Info.exe ?

Edited by Nine
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
  • Recently Browsing   0 members

    No registered users viewing this page.

×
×
  • Create New...