Copy from excel

sahilshetty · 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

XaelloNegative · 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

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

sahilshetty · 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

water · January 20, 2018

What is "Link2Data"?

What do you try to achieve with your script?

sahilshetty · 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.

water · 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?

sahilshetty · 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

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

sahilshetty · 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

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

sahilshetty · 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

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

water · January 20, 2018

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 · 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

water · January 20, 2018

Sign In

Copy from excel

Recommended Posts

sahilshetty

XaelloNegative

water

sahilshetty

water

sahilshetty

water

sahilshetty

water

sahilshetty

water

sahilshetty

water

water

sahilshetty

water

Create an account or sign in to comment

Create an account

Sign in

Recently Browsing 0 members

Browse

AutoIt Resources

Release

Beta