send('^v') does not work in EXCEL

[cloud920]

hi there, i am new to AUTOIT, and this tool is amazing good!

I am writing a script to copy some text from an internal ERP, which is written by Critix.

it's about to copy some numbers, and text from some pumped up windows to EXCEL.

Everything works fine until below,

WinWait('Internet Explorer - \\Remote')
Sleep(5000)
MouseClick('primary',194,253,2,0)
Send('^c') ;this works
Sleep(2000)
WinActivate('CQ_FY16Q3_W10.xlsm')
$oTargetRange.Select
Send('^v') ; this does not work at all, I've tried a lot of other method...

But if i manually press ctrl+v button, it works.... so please help me, thanks in adv

[JLogan3o13]

cloud920, welcome to the forum. I would suggest you look into the Excel functions in the help file, rather than trying to manually copy the data back and forth with send commands. You'll find a function like _Excel_RangeWrite will be a lot more stable than trying to simulate a CTRL+V

[cloud920]

cloud920, welcome to the forum. I would suggest you look into the Excel functions in the help file, rather than trying to manually copy the data back and forth with send commands. You'll find a function like _Excel_RangeWrite will be a lot more stable than trying to simulate a CTRL+V

Thanks Logan, appreicate your reply. below are my trying -

_Excel_RangeWrite($oWorkbook1,'shee1',ClipGet(),$oTargetRange)

still does not work, i assume that winactivate does not work very well, is there any other method to bring the EXCEL file to the FRONT of the screen?

because when i tried winactiavte my Excel file, it's still in background, not in front of the screen, thanks again.

[jdelaney]

The excel functions use COM objects, so don't need the sheet to be active.  You are probably just using the wrong function.

[cloud920]

The excel functions use COM objects, so don't need the sheet to be active.  You are probably just using the wrong function.

Thanks...any chance that give me some advice?

The ERP is written by Critix, so nothing can be detected by Finder tool, currently, i could MsgBox what i copied, but just can not paste it to a assigned excel range.

WHY i am trying to tell you that EXCEL is not activate because, i tried to send F2 to excel, which means 'edit' in excel, and try '^v', but it shows me a window from the ERP, which apparently i just press the hotkey of the ERP instead of EXCEL...

 

Edited November 1, 2015 by cloud920
TYPO

[water]

Can you please show us at least the whole block of code where you write the data to Excel?

[cloud920]

Thanks...any chance that give me some advice?

The ERP is written by Critix, so nothing can be detected by Finder tool, currently, i could MsgBox what i copied, but just can not paste it to a assigned excel range.

WHY i am trying to tell you that EXCEL is not activate because, i tried to send F2 to excel, which means 'edit' in excel, and try '^v', but it shows me a window from the ERP, which apparently i just press the hotkey of the ERP instead of EXCEL...

 

thanks for reply. below are the whole code.

 

expandcollapse popup

#include <excel.au3>

WinActivate('Gii (3.8.0.42)  -  KUN_XIANG  -  AGIIP_BU_CN  -  374  -  China - \\Remote');active ERP
Sleep(2000)
Send('+{F8}');ERP internal hotkey, change page to Order detail page
Sleep(1000)
Send('{F2}');ERP internal hotkey, ask me if i search for a specific order#

WinWait('Gii - \\Remote');when press F2, this window show up
Send('{ENTER}');yes - i search for a specific order#
WinWait('Order Search - \\Remote');Order search window show up
WinActivate('Order Search - \\Remote')
MouseClick('primary',533,374,1,0);choose one type of the order#, i could not use ID to choose because Finder could not detect anything
MouseClick('primary',614,517,1,0);then activate the input window

;################################Open the Excel file where stores the order# and copy cells('2','W'),order Number########################
Local $oExcel=_Excel_Open()
Local $oWorkbook1 = _Excel_BookOpen($oExcel, @ScriptDir & '\CQ_FY16Q3_W10.xlsm',False)
Local $oRange = $oWorkbook1.worksheets('sheet1').cells(2,'w')
Local $oTargetRange = $oWorkbook1.worksheets('sheet1').cells(2,'BY')
_Excel_RangeCopyPaste($oWorkbook1.worksheets('sheet1'), $oRange)

;TargetRange will be used to store the later data which copied form the ERP. I just try to do it once, when it works, it would be a loop that happens more than 300,000 times
;because i have more than 300,000 order row in excel every Q, currently it's a manual work, 3 months later maybe IT could do it for us, but we need it very urgently
;some times we have to do it the whole team#####

Sleep(2000)

WinActivate('Order Search - \\Remote')
Send('^v');paste the copied order# to ERP input box, works
Send('{ENTER}');then search
WinWait('Order Details - \\Remote')
sleep(2000)
Send('{ENTER}');some details show up, no useful, click enter to pass

WinWait('List Price / Cost Changes - \\Remote')
Send('{ENTER}');same as above

Sleep(10000)
Opt('mousecoordmode',0)
WinActivate('Gii (3.8.0.42)  -  KUN_XIANG  -  AGIIP_BU_CN  -  374  -  China - \\Remote')
MouseClick('primary',1199,433);click the detail of order, because Finder tool can not detect anything, i have to use the XY to click it

WinWait('Internet Explorer - \\Remote');details show up, first the window, then some details, so sleep 5secs
Sleep(5000)
MouseClick('primary',194,253,2,0);where i need to copy
Send('^c');copy
Sleep(2000)
WinActivate('CQ_FY16Q3_W10.xlsm');activate EXCEL
$oTargetRange.Select;select target ragne, 'BY2'

Send('^v'); CTRL V, does not work....

_Excel_RangeWrite($oWorkbook1,'shee1',ClipGet(),$oTargetRange);does not work either....help!!!!!!!!!!!!!

 

[water]

Your range definition is wrong. Property cells expects that you specify the row and column index as numbers.
So you need to change

Local $oRange = $oWorkbook1.worksheets('sheet1').cells(2,'w')
Local $oTargetRange = $oWorkbook1.worksheets('sheet1').cells(2,'BY')

Why don't you use

Local $oRange = $oWorkbook1.worksheets('sheet1').Range("W2")
Local $oTargetRange = $oWorkbook1.worksheets('sheet1').Range("BY2")

Your script misses error checking. I would check for @error <> 0 after each line where you work with Excel objects.

[cloud920]

Your range definition is wrong. Property cells expects that you specify the row and column index as numbers.
So you need to change

Local $oRange = $oWorkbook1.worksheets('sheet1').cells(2,'w')
Local $oTargetRange = $oWorkbook1.worksheets('sheet1').cells(2,'BY')

Why don't you use

Local $oRange = $oWorkbook1.worksheets('sheet1').Range("W2")
Local $oTargetRange = $oWorkbook1.worksheets('sheet1').Range("BY2")

Your script misses error checking. I would check for @error <> 0 after each line where you work with Excel objects.

thanks for your prompt help Water, no idea what is the Time @ your timezone, mine is 8pm, GMT+8

Let me answer your question.

1. I have some VBA experience, so when i wanna a loop, then i will use cells() instead of range() because cells can accept variables better than range() [i guesse]

and if i use below script, it works very well. so i guess cells works good, targetrange will be filled with 'AUTOIT IS GOOD' i tested it

_Excel_RangeWrite($oWorkbook1,'shee1','AUTOIT IS GOOD',$oTargetRange);'TEXT' just for example purpose

2. for the error check, i surely new to AUTOIT and program mystery, so i still green hand.

[water]

1. My bad. Seems that the cells property accepts numbers and letters. That's what MSDN tells me: "A number or string that indicates the column number of the cell you want to access, starting with either 1 or "A" for the first column in the range."
2. Could you insert the following line after the RangeWrite so we know it worked or which error occurred?
   MsgBox(0, "RangeWrite", "@error = " & @error & ", @extended = " & @extended)

[cloud920]

1.  111.bmp 111.bmp

can you see it? thanks Water

 

not sure you can see it, let me type. my script is

_Excel_RangeWrite($oWorkbook1,'shee1',ClipGet(),$oTargetRange)
MsgBox(0, "RangeWrite", "@error = " & @error & ", @extended = " & @extended)

 

@error=2, @extended= -2147352567

Edited November 1, 2015 by cloud920

[cloud920]

111.bmp

[water]

Means: Worksheet is invalid.
Isn't there a "t" missing in the name of the sheet? Shouldn't it be:

_Excel_RangeWrite($oWorkbook1, 'sheet1', ClipGet(), $oTargetRange)

 

Edited November 1, 2015 by water

[cloud920]

Means: Worksheet is invalid.
Isn't there a "t" missing in the name of the sheet? Shouldn't it be:

_Excel_RangeWrite($oWorkbook1, 'sheet1', ClipGet(), $oTargetRange)

 

how stupid i ammmmmmmmmmmmmmmmmmmmmmmmmmmmmmm, thanks Water for your attention for such a minor mistake bother me for a whole weekend!!!!!!!!!!!!!!!!!!!!

appreciate!

BTW, can you please advise where i could mapping those mistake with error?

[water]

The values of @error for each function are described in the help file.

[cloud920]

The values of @error for each function are described in the help file.

thanks Water for your advice.

1 more question, is there a function that like 'On error resume next' in AUTOIT? I have search out the help information but nothing found.

[jdelaney]

Put in an error handler:

$oErrorHandler = ObjEvent("AutoIt.Error", "Var_ErrFunc")
Func Var_ErrFunc($oError) ; Com error handling
    ; Do anything here.
    ConsoleWrite("err.number is: " & @TAB & $oError.number & @CRLF & _
            "err.windescription:" & @TAB & $oError.windescription & @CRLF & _
            "err.description is: " & @TAB & $oError.description & @CRLF & _
            "err.source is: " & @TAB & $oError.source & @CRLF & _
            "err.helpfile is: " & @TAB & $oError.helpfile & @CRLF & _
            "err.helpcontext is: " & @TAB & $oError.helpcontext & @CRLF & _
            "err.lastdllerror is: " & @TAB & $oError.lastdllerror & @CRLF & _
            "err.scriptline is: " & @TAB & $oError.scriptline & @CRLF & _
            "err.retcode is: " & @TAB & $oError.retcode & @CRLF & @CRLF)
EndFunc   ;==>Var_ErrFunc

 

[cloud920]

Put in an error handler:

$oErrorHandler = ObjEvent("AutoIt.Error", "Var_ErrFunc")
Func Var_ErrFunc($oError) ; Com error handling
    ; Do anything here.
    ConsoleWrite("err.number is: " & @TAB & $oError.number & @CRLF & _
            "err.windescription:" & @TAB & $oError.windescription & @CRLF & _
            "err.description is: " & @TAB & $oError.description & @CRLF & _
            "err.source is: " & @TAB & $oError.source & @CRLF & _
            "err.helpfile is: " & @TAB & $oError.helpfile & @CRLF & _
            "err.helpcontext is: " & @TAB & $oError.helpcontext & @CRLF & _
            "err.lastdllerror is: " & @TAB & $oError.lastdllerror & @CRLF & _
            "err.scriptline is: " & @TAB & $oError.scriptline & @CRLF & _
            "err.retcode is: " & @TAB & $oError.retcode & @CRLF & @CRLF)
EndFunc   ;==>Var_ErrFunc

 

thanks for your reply. as i said, i am new to AUTOIT and i am not very aware of where i should put it, so i put it before my script.

but things not very smooth when i test it, is it something wrong where i put it?

[water]

The top of your script is the perfect place for a COM error handler.
What does "not very smooth" mean? Do you get any error messages?

[EmilyLove]

Shift+Insert is the same as Control+V. Does Shift+Insert not work either?