cloud920

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

20 posts in this topic

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:sweating:

Share this post


Link to post
Share on other sites



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


When you're dead, you don't know you're dead - it's only difficult for those that know you. It's the same way when you're stupid...

My Scripts: SCCM UDFInclude Source with Compiled Script, Windows Firewall UDF

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

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


IEbyXPATH-Grab IE DOM objects by XPATH IEscriptRecord-Makings of an IE script recorder ExcelFromXML-Create Excel docs without excel installed GetAllWindowControls-Output all control data on a given window.

Share this post


Link to post
Share on other sites

#5 ·  Posted (edited)

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 by cloud920
TYPO

Share this post


Link to post
Share on other sites

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


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (2016-08-18 - Version 1.4.6.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2016-12-04 - Version 1.2.2.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites

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.

 

#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!!!!!!!!!!!!!

 

Share this post


Link to post
Share on other sites

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.


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (2016-08-18 - Version 1.4.6.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2016-12-04 - Version 1.2.2.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites
  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)

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (2016-08-18 - Version 1.4.6.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2016-12-04 - Version 1.2.2.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites

#11 ·  Posted (edited)

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

Share this post


Link to post
Share on other sites

#13 ·  Posted (edited)

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

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (2016-08-18 - Version 1.4.6.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2016-12-04 - Version 1.2.2.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites

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?

Share this post


Link to post
Share on other sites

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

1 person likes this

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (2016-08-18 - Version 1.4.6.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2016-12-04 - Version 1.2.2.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

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

 

1 person likes this

IEbyXPATH-Grab IE DOM objects by XPATH IEscriptRecord-Makings of an IE script recorder ExcelFromXML-Create Excel docs without excel installed GetAllWindowControls-Output all control data on a given window.

Share this post


Link to post
Share on other sites

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?

Share this post


Link to post
Share on other sites

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?


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (2016-08-18 - Version 1.4.6.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2016-12-04 - Version 1.2.2.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites

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


My Scripts:

False Positive Reporter - Mass email all anti virus vendors with an attachment of your program for easy whitelisting.

PortableApps.com App Creation Wizard  - A simple GUI-based Wizard for creating PortableApps.

AutoISO  - Automatic ISO Image creation using ImgBurn.

SoundBoard - Play any song or sound you want at the press of a hotkey

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