Sign in to follow this  
Followers 0
herewasplato

Ms Excel And Notepad (long)

6 posts in this topic

The script below gives me errors when the output goes to MS Excel... so I sent the output to Notepad and it works. As part of the debug, I added sending the entire contents of the clipboard to Excel and that caused yet another error - element number 1 of arrays that have multiple elements are wrong (at least on the 2 systems that I tried it on.) Both systems are running XP pro sp1 and Office 2002[MS Excel (10.5815.4219) SP-2].

I get the same results with autoit-v3.0.91-beta.exe and autoit-v3.0.92-beta.exe.

The original error that I noticed was test4 G22 should be G2 and test5 W2 should be W22... there may be other errors.

Sorry so long of a post, I tried reducing the test string size and the errors went away... this version is reduced... I send the strings to and from the clipboard because in the "real" version of the script, the strings come from another application via the clipboard.

;;;;;;;;;;;; start test1.au3 ;;;;;;;;;;;;

AutoItSetOption ("MouseCoordMode", 0) ;Use Mouse Coordinates relative to window (1-rel to desktop)

AutoItSetOption ("SendKeyDelay", 5) ;Send keys ASAP (milliseconds)

AutoItSetOption ("WinWaitDelay", 0) ;No additiopnal delay once window appears (milliseconds)

AutoItSetOption ("WinTitleMatchMode", 2) ;1-from the start, 2-any substring, 3-exact match

Run ("Notepad")

WinWait ("Notepad", "")

Dim $groupNAME [5]

$groupNAME [0] = "test1"

$groupNAME [1] = "test2"

$groupNAME [2] = "test3"

$groupNAME [3] = "test4"

$groupNAME [4] = "test5"

Dim $string [5]

$string [0] = "E11"

$string [1] = "AB16"

$string [2] = "AB11,AB12,AB14,AB15,AB17,AB19,AB21,AB22,AB5,AB6,AB8,AB9,E10,E12,E13,E15,E16,E18,E19,E21,E22,E6,

E8,F5,G22,G5,H22,H5,K22,K5,L22,L5,N22,N5,P22,P5,T22,T5,U22,U5,W22,W5,Y22,Y5"

$string [3] = "AA23,AB24,AC23,AC24,AC26,AC5,AD24,AD25,AD5,AE22,AE23,AF22,AF24,B2,B24,C1,C22,C23,C3,C5,D1,D2,D2

2,D4,E2,E23,E3,F1,F3,F4,G1,G2,G4,H26,H3,J25,J4,P3,R23,R4,T24,T25,U23,U24,U26,V26,Y23"

$string [4] = "K24,J26,N25,P26,P24,P23,R26,R25,K25,L23,L26,L24,M23,M25,N24,M26,D24,F23,F26,G24,G25,H24,J23,C25

,B26,E24,C26,D25,E26,F25,H23,V25,Y24,V23,A6,A5,AA25,T2,Y26,AF6,AF5,A21,A22,AB25,A15,C16,A18,D19,AC8,

AF9,AD11,AE13,B14,D15,C18,B20,AD8,AE8,AC12,AF12,A16,C17,A17,D20,AC7,AF10,AD10,AE12,B15,D14,C19,B19,A

D9,AE7,AC13,AF11,R3,U2,AE3,AA3,AD1,V3,W1,AA1,V1,U3,T4,AE2,AC2,W4,Y2,AB2,V4,W2,AB1,T1"

For $p = 0 to 4

ClipPut ($string [$p])

$pins = StringSplit (ClipGet (), ",")

WinWait ("Notepad", "")

WinActivate ("Notepad", "")

WinWaitActive ("Notepad", "")

Send ("clipget=")

Send ("{TAB}")

Send (ClipGet ())

Send ("{ENTER}")

For $i = 1 to $pins [0]

WinWait ("Notepad", "")

WinActivate ("Notepad", "")

WinWaitActive ("Notepad", "")

Send ($groupNAME [$p])

Send ("{TAB}")

Send ($pins [$i])

; If $i = 1 Then MsgBox (0, $groupNAME [$p], "$pins " & $i & " " & $pins [$i])

Send ("{ENTER}")

If $pins [$i] = "G2" Then MsgBox (0, $groupNAME [$p], "Value from StringSplit " & $pins [$i])

If $pins [$i] = "W2" Then MsgBox (0, $groupNAME [$p], "Value from StringSplit " & $pins [$i])

Next

Next

;;;;;;;;;;;; end test1.au3 ;;;;;;;;;;;;

;;;;;;;;;;;; start test2.au3 ;;;;;;;;;;;;

AutoItSetOption ("MouseCoordMode", 0) ;Use Mouse Coordinates relative to window (1-rel to desktop)

AutoItSetOption ("SendKeyDelay", 5) ;Send keys ASAP (milliseconds)

AutoItSetOption ("WinWaitDelay", 0) ;No additiopnal delay once window appears (milliseconds)

AutoItSetOption ("WinTitleMatchMode", 2) ;1-from the start, 2-any substring, 3-exact match

Run ("C:\Program Files\Microsoft Office\Office10\EXCEL.EXE", "")

WinWait ("Microsoft Excel - Book1", "")

Dim $groupNAME [5]

$groupNAME [0] = "test1"

$groupNAME [1] = "test2"

$groupNAME [2] = "test3"

$groupNAME [3] = "test4"

$groupNAME [4] = "test5"

Dim $string [5]

$string [0] = "E11"

$string [1] = "AB16"

$string [2] = "AB11,AB12,AB14,AB15,AB17,AB19,AB21,AB22,AB5,AB6,AB8,AB9,E10,E12,E13,E15,E16,E18,E19,E21,E22,E6,

E8,F5,G22,G5,H22,H5,K22,K5,L22,L5,N22,N5,P22,P5,T22,T5,U22,U5,W22,W5,Y22,Y5"

$string [3] = "AA23,AB24,AC23,AC24,AC26,AC5,AD24,AD25,AD5,AE22,AE23,AF22,AF24,B2,B24,C1,C22,C23,C3,C5,D1,D2,D2

2,D4,E2,E23,E3,F1,F3,F4,G1,G2,G4,H26,H3,J25,J4,P3,R23,R4,T24,T25,U23,U24,U26,V26,Y23"

$string [4] = "K24,J26,N25,P26,P24,P23,R26,R25,K25,L23,L26,L24,M23,M25,N24,M26,D24,F23,F26,G24,G25,H24,J23,C25

,B26,E24,C26,D25,E26,F25,H23,V25,Y24,V23,A6,A5,AA25,T2,Y26,AF6,AF5,A21,A22,AB25,A15,C16,A18,D19,AC8,

AF9,AD11,AE13,B14,D15,C18,B20,AD8,AE8,AC12,AF12,A16,C17,A17,D20,AC7,AF10,AD10,AE12,B15,D14,C19,B19,A

D9,AE7,AC13,AF11,R3,U2,AE3,AA3,AD1,V3,W1,AA1,V1,U3,T4,AE2,AC2,W4,Y2,AB2,V4,W2,AB1,T1"

For $p = 0 to 4

ClipPut ($string [$p])

$pins = StringSplit (ClipGet (), ",")

WinWait ("Microsoft Excel - Book1", "")

WinActivate ("Microsoft Excel - Book1", "")

WinWaitActive ("Microsoft Excel - Book1", "")

Send ("clipget=")

Send ("{TAB}")

Send (ClipGet ())

Send ("{ENTER}")

For $i = 1 to $pins [0]

WinWait ("Microsoft Excel - Book1", "")

WinActivate ("Microsoft Excel - Book1", "")

WinWaitActive ("Microsoft Excel - Book1", "")

Send ($groupNAME [$p])

Send ("{TAB}")

Send ($pins [$i])

; If $i = 1 Then MsgBox (0, $groupNAME [$p], "$pins " & $i & " " & $pins [$i])

Send ("{ENTER}")

If $pins [$i] = "G2" Then MsgBox (0, $groupNAME [$p], "Value from StringSplit " & $pins [$i])

If $pins [$i] = "W2" Then MsgBox (0, $groupNAME [$p], "Value from StringSplit " & $pins [$i])

Next

Next

Send ("{RIGHT 2}{UP 3}'<<<Should be W2.{UP}")

MsgBox (0, "AutoIt", "Ready to see the another possible error?")

Send ("{UP 109}'<<<Should be G2.{UP}")

;;;;;;;;;;;; end test2.au3 ;;;;;;;;;;;;


[size="1"][font="Arial"].[u].[/u][/font][/size]

Share this post


Link to post
Share on other sites



#2 ·  Posted (edited)

Excell does autofil, so if you type Fred, then the line under it you type Fre{enter} it will fill in Fred.

Not an AutoIt problem, it is a Excel feature.

open excel type t111{down}t{down}t{down} see what I meen?

Edited by scriptkitty

AutoIt3, the MACGYVER Pocket Knife for computers.

Share this post


Link to post
Share on other sites

I'd guess that when W2 is typed the excel autocomplete function overwrites it with W22.

In excel under test 5 I went down the the W22 entry, deleted it and then typed "W2" W22 then popped up.

Share this post


Link to post
Share on other sites

Yea, quick show:

;;;;;;;;;;;; start test2.au3;;;;;;;;;;;;

AutoItSetOption ("MouseCoordMode", 0);Use Mouse Coordinates relative to window (1-rel to desktop)
AutoItSetOption ("SendKeyDelay", 5);Send keys ASAP (milliseconds)
AutoItSetOption ("WinWaitDelay", 0);No additiopnal delay once window appears (milliseconds)
AutoItSetOption ("WinTitleMatchMode", 2);1-from the start, 2-any substring, 3-exact match

Run ("C:\Program Files\Microsoft Office\Office10\EXCEL.EXE", "")
WinWait ("Microsoft Excel - Book1", "")


WinWait ("Microsoft Excel - Book1", "")
WinActivate ("Microsoft Excel - Book1", "")
WinWaitActive ("Microsoft Excel - Book1", "")
Send ("T11111111{down}")
send ("t")
sleep(2000)
send("{down}")
send ("t{down}")
send ("t{down}")

AutoIt3, the MACGYVER Pocket Knife for computers.

Share this post


Link to post
Share on other sites

Can you say, "EMBARRASSED"?

I know better!

Thanks...


[size="1"][font="Arial"].[u].[/u][/font][/size]

Share this post


Link to post
Share on other sites

we all do it sometimes,

If you want to stop excel BS, :whistle: You can always put a space and then erase it as in:

send($pins[$i] & " {bs}") that will stop any autofill


AutoIt3, the MACGYVER Pocket Knife for computers.

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