sahilshetty Posted January 19, 2018 Share Posted January 19, 2018 I'm trying to copy a text from excel and show it on a msgbox . I want to be a loop such that it'll copy the text in the next row the next time. I cant get it to copy the text. Can anybody help me new3.au3 Link to comment Share on other sites More sharing options...
XaelloNegative Posted January 19, 2018 Share Posted January 19, 2018 Hi @sahilshetty, There are a lot of ways to approach this. However i need to know where your coming from. Can you at least post what you have so far in your code and lets try to work with that ~XN~ Link to comment Share on other sites More sharing options...
water Posted January 19, 2018 Share Posted January 19, 2018 There's no need to use WinActivate (and automate the GUI this way). The Excel UDF uses COM to directly access Excel. You could read the whole worksheet with a single call into an array and then process the array: #include<excel.au3> HotKeySet("{s}","start") HotKeySet("{e}","end") Local $oExcel = _Excel_Open() Local $oWorkbook = _Excel_BookOpen($oExcel, "C:\Users\SAHIL\Desktop\l2d\book1.xlsx") Local $aContent = _Excel_RangeRead($oWorkbook) While 1 Sleep(1) WEnd Func start() For $i = 0 to UBound($aContent) - 1 MsgBox(0, "Content", "Content of cell A" & $i+1 & ": " & $aContent[$i]) Next EndFunc Func end() Exit 0 EndFunc My UDFs and Tutorials: Spoiler UDFs:Active Directory (NEW 2022-02-19 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - WikiExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example ScriptsOutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - WikiOutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - DownloadOutlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - WikiPowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - WikiTask Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki Standard UDFs:Excel - Example Scripts - WikiWord - Wiki Tutorials:ADO - WikiWebDriver - Wiki Link to comment Share on other sites More sharing options...
sahilshetty Posted January 20, 2018 Author Share Posted January 20, 2018 i could get it to work with this code but this just wont loop. It copies the cell A1 and B1 but wont go any further. Can you help me with this? #include<excel.au3> HotKeySet("{s}","start") HotKeySet("{e}","end") Local $oExcel_1= _Excel_Open() Local $oworkbook = _Excel_BookOpen($oExcel_1,"C:\Users\SAHIL\Desktop\l2d\book1.xlsx") While 1 Sleep(1) WEnd Func start() For $i= 1 To 25 Local $_name = _Excel_RangeRead($oworkbook,default,"A" & $i) WinActivate("Link2Data") MouseClick("left",450,430) Send($_name) Local $_email = _Excel_RangeRead($oworkbook,default,"b" & $i) MouseClick("left",450,460) Send($_email) Next EndFunc Func end() exit 0 EndFunc Link to comment Share on other sites More sharing options...
water Posted January 20, 2018 Share Posted January 20, 2018 What is "Link2Data"? What do you try to achieve with your script? My UDFs and Tutorials: Spoiler UDFs:Active Directory (NEW 2022-02-19 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - WikiExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example ScriptsOutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - WikiOutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - DownloadOutlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - WikiPowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - WikiTask Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki Standard UDFs:Excel - Example Scripts - WikiWord - Wiki Tutorials:ADO - WikiWebDriver - Wiki Link to comment Share on other sites More sharing options...
sahilshetty Posted January 20, 2018 Author Share Posted January 20, 2018 3 hours ago, water said: What is "Link2Data"? What do you try to achieve with your script? im trying to make an auto form filling script. Link2data is the app where the forms are. Link to comment Share on other sites More sharing options...
water Posted January 20, 2018 Share Posted January 20, 2018 I suggest to use Control* functions. Working with screen coordinates and Send isn't very reliable. When you use the AutoIt Window Info Tool does it "see" the controls where you want to insert your data? My UDFs and Tutorials: Spoiler UDFs:Active Directory (NEW 2022-02-19 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - WikiExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example ScriptsOutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - WikiOutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - DownloadOutlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - WikiPowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - WikiTask Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki Standard UDFs:Excel - Example Scripts - WikiWord - Wiki Tutorials:ADO - WikiWebDriver - Wiki Link to comment Share on other sites More sharing options...
sahilshetty Posted January 20, 2018 Author Share Posted January 20, 2018 8 minutes ago, water said: I suggest to use Control* functions. Working with screen coordinates and Send isn't very reliable. When you use the AutoIt Window Info Tool does it "see" the controls where you want to insert your data? my problem is that i cannot get the script to loop. i am fine with working with screen coordinates as anything else might become too complicated for me Link to comment Share on other sites More sharing options...
water Posted January 20, 2018 Share Posted January 20, 2018 Add some debugging to your script. This way you will see how often it loops: #include<excel.au3> HotKeySet("{s}","start") HotKeySet("{e}","end") Local $oExcel_1= _Excel_Open() Local $oworkbook = _Excel_BookOpen($oExcel_1,"C:\Users\SAHIL\Desktop\l2d\book1.xlsx") While 1 Sleep(1) WEnd Func start() For $i = 1 To 25 ConsoleWrite("Loop #" & $i & @CRLF) Local $_name = _Excel_RangeRead($oworkbook,default,"A" & $i) WinActivate("Link2Data") MouseClick("left",450,430) Send($_name) Local $_email = _Excel_RangeRead($oworkbook,default,"b" & $i) MouseClick("left",450,460) Send($_email) Next EndFunc Func end() Exit 0 EndFunc My UDFs and Tutorials: Spoiler UDFs:Active Directory (NEW 2022-02-19 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - WikiExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example ScriptsOutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - WikiOutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - DownloadOutlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - WikiPowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - WikiTask Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki Standard UDFs:Excel - Example Scripts - WikiWord - Wiki Tutorials:ADO - WikiWebDriver - Wiki Link to comment Share on other sites More sharing options...
sahilshetty Posted January 20, 2018 Author Share Posted January 20, 2018 17 minutes ago, water said: Add some debugging to your script. This way you will see how often it loops: #include<excel.au3> HotKeySet("{s}","start") HotKeySet("{e}","end") Local $oExcel_1= _Excel_Open() Local $oworkbook = _Excel_BookOpen($oExcel_1,"C:\Users\SAHIL\Desktop\l2d\book1.xlsx") While 1 Sleep(1) WEnd Func start() For $i = 1 To 25 ConsoleWrite("Loop #" & $i & @CRLF) Local $_name = _Excel_RangeRead($oworkbook,default,"A" & $i) WinActivate("Link2Data") MouseClick("left",450,430) Send($_name) Local $_email = _Excel_RangeRead($oworkbook,default,"b" & $i) MouseClick("left",450,460) Send($_email) Next EndFunc Func end() Exit 0 EndFunc it's showing that it loops only 1 time. I want it to loop 25 times Link to comment Share on other sites More sharing options...
water Posted January 20, 2018 Share Posted January 20, 2018 Give this a try: #include<excel.au3> HotKeySet("{s}","start") HotKeySet("{e}","end") Local $oExcel = _Excel_Open() Local $oWorkbook = _Excel_BookOpen($oExcel,"C:\Users\SAHIL\Desktop\l2d\book1.xlsx") Local $aValues = _Excel_RangeRead($oWorkbook) While 1 Sleep(100) WEnd Func start() For $i = 1 To 25 ConsoleWrite("Loop #" & $i & @CRLF) WinActivate("Link2Data") MouseClick("left", 450, 430) Send($aValues[$i-1][0]) ; $_name MouseClick("left", 450, 460) Send($aValues[$i-1][1]) ; $_email Next EndFunc Func end() Exit 0 EndFunc My UDFs and Tutorials: Spoiler UDFs:Active Directory (NEW 2022-02-19 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - WikiExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example ScriptsOutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - WikiOutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - DownloadOutlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - WikiPowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - WikiTask Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki Standard UDFs:Excel - Example Scripts - WikiWord - Wiki Tutorials:ADO - WikiWebDriver - Wiki Link to comment Share on other sites More sharing options...
sahilshetty Posted January 20, 2018 Author Share Posted January 20, 2018 4 minutes ago, water said: Give this a try: #include<excel.au3> HotKeySet("{s}","start") HotKeySet("{e}","end") Local $oExcel = _Excel_Open() Local $oWorkbook = _Excel_BookOpen($oExcel,"C:\Users\SAHIL\Desktop\l2d\book1.xlsx") Local $aValues = _Excel_RangeRead($oWorkbook) While 1 Sleep(100) WEnd Func start() For $i = 1 To 25 ConsoleWrite("Loop #" & $i & @CRLF) WinActivate("Link2Data") MouseClick("left", 450, 430) Send($aValues[$i-1][0]) ; $_name MouseClick("left", 450, 460) Send($aValues[$i-1][1]) ; $_email Next EndFunc Func end() Exit 0 EndFunc it still loops only 1 time. I've even tried different loop commands like while and do_until. It always loops only a single time Link to comment Share on other sites More sharing options...
water Posted January 20, 2018 Share Posted January 20, 2018 Will have a look. BTW: There is no need to quote my replies. I know what I have written Simply enter your reply and press the "Submit Reply" button My UDFs and Tutorials: Spoiler UDFs:Active Directory (NEW 2022-02-19 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - WikiExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example ScriptsOutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - WikiOutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - DownloadOutlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - WikiPowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - WikiTask Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki Standard UDFs:Excel - Example Scripts - WikiWord - Wiki Tutorials:ADO - WikiWebDriver - Wiki Link to comment Share on other sites More sharing options...
water Posted January 20, 2018 Share Posted January 20, 2018 (edited) I think I found the problem You have two letters set as a hotkey. When the data read from Excel contains an "e" then the Exit hotkey gets triggered. Use keys not contained in the data you send to the other application. The example now uses F1 to start and F4 to exit the script. #include<excel.au3> HotKeySet("{F1}","start") HotKeySet("{F4}","end") Local $oExcel = _Excel_Open() Local $oWorkbook = _Excel_BookOpen($oExcel,"C:\Users\SAHIL\Desktop\l2d\book1.xlsx") Local $aValues = _Excel_RangeRead($oWorkbook) While 1 Sleep(100) WEnd Func start() For $i = 1 To 25 ConsoleWrite("Loop #" & $i & @CRLF) WinActivate("Link2Data") MouseClick("left", 450, 430) Send($aValues[$i-1][0]) ; $_name MouseClick("left", 450, 460) Send($aValues[$i-1][1]) ; $_email Next EndFunc Func end() Exit 0 EndFunc Edited January 20, 2018 by water My UDFs and Tutorials: Spoiler UDFs:Active Directory (NEW 2022-02-19 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - WikiExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example ScriptsOutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - WikiOutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - DownloadOutlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - WikiPowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - WikiTask Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki Standard UDFs:Excel - Example Scripts - WikiWord - Wiki Tutorials:ADO - WikiWebDriver - Wiki Link to comment Share on other sites More sharing options...
sahilshetty Posted January 20, 2018 Author Share Posted January 20, 2018 9 minutes ago, water said: I think I found the problem You have two letters set as a hotkey. When the data read from Excel contains an "e" then the Exit hotkey gets triggered. Use keys not contained in the data you send to the other application. The example now uses F1 to start and F4 to exit the script. #include<excel.au3> HotKeySet("{F1}","start") HotKeySet("{F4}","end") Local $oExcel = _Excel_Open() Local $oWorkbook = _Excel_BookOpen($oExcel,"C:\Users\SAHIL\Desktop\l2d\book1.xlsx") Local $aValues = _Excel_RangeRead($oWorkbook) While 1 Sleep(100) WEnd Func start() For $i = 1 To 25 ConsoleWrite("Loop #" & $i & @CRLF) WinActivate("Link2Data") MouseClick("left", 450, 430) Send($aValues[$i-1][0]) ; $_name MouseClick("left", 450, 460) Send($aValues[$i-1][1]) ; $_email Next EndFunc Func end() Exit 0 EndFunc THANK YOU SOO MUCH!!!!!!!!!!. YOU'RE THE BEST Link to comment Share on other sites More sharing options...
water Posted January 20, 2018 Share Posted January 20, 2018 My UDFs and Tutorials: Spoiler UDFs:Active Directory (NEW 2022-02-19 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - WikiExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example ScriptsOutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - WikiOutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - DownloadOutlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - WikiPowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - WikiTask Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki Standard UDFs:Excel - Example Scripts - WikiWord - Wiki Tutorials:ADO - WikiWebDriver - Wiki Link to comment Share on other sites More sharing options...
Recommended Posts
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 accountSign in
Already have an account? Sign in here.
Sign In Now