Copy from excel

[sahilshetty]

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

[XaelloNegative]

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

[water]

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

 

[sahilshetty]

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

 

[water]

What is "Link2Data"?

What do you try to achieve with your script?

[sahilshetty]

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.

[water]

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?

[sahilshetty]

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

[water]

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

 

[sahilshetty]

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

[water]

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

 

[sahilshetty]

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

[water]

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

[water]

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

[sahilshetty]

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

[water]