Sign in to follow this  
Followers 0
Vectortech

XL file to second application data transfer

16 posts in this topic

Hello everyone

I'm quite new to scripting put not one to give any new challange a shot. What I'm trying to do is create a script that will read data from two columns in a xls file, one row at a time, and tranfer that data into two data fields in another application.

Simple run though below:

Select Application "X"

Copy data from xls file "Y", column 1, row 1

Transfer data from column 1, row 1 into first field in application "X"

key in "enter" in application "X" 2 times.

Copy data from xls file "Y", column 2, row 1

Transfer data from column 2, row 1 into second field in application "X"

key in "enter" in application "X" 1 time.

Then repeat the process again for row 2 in xls file "Y", and so on and so on until all the rows of data in xls file "Y" are entered in application "X"

Any information that would help me get started on creating such a script, if it can even be created, would greatly be appreciated.

Thanks in advance.

Share this post


Link to post
Share on other sites



It's all in the help file.

Look at the _Excel* functions for working with the spreadsheet. Then ControlSend() to place the data in the apps.

:idea:


Valuater's AutoIt 1-2-3, Class... Is now in Session!For those who want somebody to write the script for them: RentACoder"Any technology distinguishable from magic is insufficiently advanced." -- Geek's corollary to Clarke's law

Share this post


Link to post
Share on other sites

I was hoping for a bit more help to get started then "it's all in the help file". This much I already now, and I place I've had my head buried in all day. :idea:

Share this post


Link to post
Share on other sites

This should get you started:

#include<array.au3> ;inclused for _Arraydisplay
#include<excel.au3>

$path = "enter the full path to your xls file here"
;It is probably easiest to read all the data from the xls file at once and use it when needed. For that we open excel using:
$oExcel = _ExcelBookOpen($path,0) ;this creates a hidden excel instance and and an object with which you can manipulate it.
;then we read all the data on the first worksheet using:
$aExcel = _ExcelReadSheetToArray($oExcel) ;$aExcel should now be an array that mirrors your excel worksheet.
;~ _ArrayDisplay($aExcel) ;uncomment this line to see if the contents if the excel file have been read properly.
_ExcelBookClose($oExcel) ;this closes the hidden excel instance. If your script crashes before it reaches this line, go in taskmanager and kill EXCEL.EXE

;The next part of your script is a bit hard to show without knowing what application you run, but follow these steps and you should be ok:

$ApplicationPath = @ProgramsDir & '\Application "X".exe' ;the full path to your application
$ApplicationName = 'Application "X".exe' ;the name of your application's executable
$ApplicationTitle = 'Application "X"' ;the text in the application's titlebar

;to determine if an application exists:
ProcessExists($ApplicationName)
;to start the process if it doesn't exist yet:
Run($ApplicationPath)
WinWait($ApplicationTitle) ;after the run command you need to wait for the window to load.

;now you have to use the AU3Info tool to get the ClassnameNN of the control you want to use
$ClassnameNN = "Edit1" ;for now I'll asume it's the first edit control
ControlSetText ($ApplicationTitle, "",$ClassnameNN,$aExcel[1][1]) ;the data entered is $aExcel[1][1], for row1, column1. !!!note that most other arrays in autoit are 0 based (data starts at $array[0][0]!!!
ControlSend($ApplicationTitle,"",$ClassnameNN,"{Enter 2}") ; send enter twice

;If you don't know how many cells there will be in the excel file, then read up on For...To...Step...Next loops. And remember that $aExcel[0][0] and $aExcel[0][1] contain the size of the array returned.
;if you still get stuck, then show us what you tried with as much data about the problem as possible.

Share this post


Link to post
Share on other sites

Thanks for the great start Tven. :idea:

You really wouldn't need the commands below though if you application is already open though would you?

ApplicationPath = @ProgramsDir & '\Application "X".exe' ;the full path to your application

$ApplicationName = 'Application "X".exe' ;the name of your application's executable

$ApplicationTitle = 'Application "X"' ;the text in the application's titlebar

;to determine if an application exists:

ProcessExists($ApplicationName)

;to start the process if it doesn't exist yet:

Run($ApplicationPath)

WinWait($ApplicationTitle) ;after the run command you need to wait for the window to load.

Share this post


Link to post
Share on other sites

oops

Ok, I see were you need at least the 3 lines below for commands farther down you your example script

ApplicationPath = @ProgramsDir & '\Application "X".exe' ;the full path to your application

$ApplicationName = 'Application "X".exe' ;the name of your application's executable

$ApplicationTitle = 'Application "X"' ;the text in the application's titlebar

Share this post


Link to post
Share on other sites

Ok, here's my first attempt. I'm trying to do anything fancy on the first past. Just see if I can add the data from cell 1,1 to a wordpad document and then send "enter" twice.

I don't get any errors but wordpad doesn't open at all so of course no data has a chance to get entered into it.

#include<array.au3> ;inclused for _Arraydisplay

#include<excel.au3>

$path = 'C:\Documents and Settings\Purchasing\Desktop\cyclecount.xls'

$oExcel = _ExcelBookOpen($path,0)

$aExcel = _ExcelReadSheetToArray($oExcel)

;~ _ArrayDisplay($aExcel)

_ExcelBookClose($oExcel)

$ApplicationPath = @ProgramsDir & 'C:\Program Files\Windows NT\Accessories\wordpad.exe'

$ApplicationName = 'wordpad.exe'

$ApplicationTitle = 'Document - Wordpad'

ProcessExists($ApplicationName)

Run($ApplicationPath)

WinWait($ApplicationTitle)

$ClassnameNN = "Edit1" ControlSetText ($ApplicationTitle, "",$ClassnameNN,$aExcel[1][1])

ControlSend($ApplicationTitle, "",$ClassnameNN,"{Enter 2}")

Share this post


Link to post
Share on other sites

try it like this:

#include<array.au3> ;included for _Arraydisplay
#include<excel.au3>

$path = @DesktopDir & '\cyclecount.xls' ;@Desktopdir replaces "C:\Documents and Settings\Purchasing\Desktop"
$oExcel = _ExcelBookOpen($path,0)
$aExcel = _ExcelReadSheetToArray($oExcel)
_ArrayDisplay($aExcel)
_ExcelBookClose($oExcel)
$ApplicationPath = @ProgramFilesDir & '\Windows NT\Accessories\wordpad.exe' ;@ProgramFilesDir replaces "C:\Program Files" (@ProgramsDir which I used in the example was a typo)
$ApplicationName = 'wordpad.exe'
$ApplicationTitle = 'Document - Wordpad'
If ProcessExists($ApplicationName) = 0 Then ;If notepad is already open, you don't need to open it again.....
    Run($ApplicationPath)   ;.....which is what we do here
    WinWait($ApplicationTitle)
EndIf
$ClassnameNN = "Edit1"
ControlSetText ($ApplicationTitle, "",$ClassnameNN,$aExcel[1][1])
ControlSend($ApplicationTitle, "",$ClassnameNN,"{Enter 2}")

Share this post


Link to post
Share on other sites

Ok, I was playing around with it a bit more while I was waiting for your reply and I was able to get the script to open Notepad and copy the data from cell 1,1 into Notepad fine. But when I try the same thing whith either a Wordpad Document or my application, which the that case the "ApplicationTitle" is "VECTOR", I get no data transfer.

It's like the script is not recognizing the "Document - WordPad" or "VECTOR" window?

I read that "ApplicationTitle" is case sensitive, so I made sure that was correct in both cases.

Any ideas there?

Thanks in advance

Share this post


Link to post
Share on other sites

IF the window was not recognised the script would hang on "WinWait"

It's also possible that "Edit1" is not the right ClassNN for Wordpad and VECTOR, so the script ends without doing anything, or after entering the info into the wrong control.

Use AU3Info, to check the Title of the windows and the ClassNN of the controls.

You could also take a look at "Window Titles and Text" in the helpfile.

Share this post


Link to post
Share on other sites

Houston we have a problem. :)

I was able to find the ClassnameNN for Wordpad using the AU3Info tool, thanks for that tip. :idea:

But if I use the tool on the "VECTOR" window, I get no ClassnameNN information at all? If there is no title or ClassnameNN information can I use "controlclick" command somehow to select and edit the window. The only information that AU3Info tool gives me about the window is "Class", which is "DEJAWINT". Even though there's no Title infomation there is test on the title bar, which is "VECTOR"

Share this post


Link to post
Share on other sites

#12 ·  Posted (edited)

Ok, basic script but I think I'm getting somewhere. I'm able to get get info into the application now. :idea:

#include<array.au3> ;included for _Arraydisplay

#include<excel.au3>

$path = @DesktopDir & '\cyclecount.xls' ;@Desktopdir replaces "C:\Documents and Settings\Purchasing\Desktop"

$oExcel = _ExcelBookOpen($path,0)

$aExcel = _ExcelReadSheetToArray($oExcel)

_ArrayDisplay($aExcel)

_ExcelBookClose($oExcel)

WinActivate("[CLASS:DEJAWINT]", "")

ControlSetText ("[CLASS:DEJAWINT]", "", "", $aExcel[1][1])

ControlSend("[CLASS:DEJAWINT]", "", "", $aExcel[1][1])

ControlSend("[CLASS:DEJAWINT]", "", "", "{Enter}")

ControlSend("[CLASS:DEJAWINT]", "", "", "1")

ControlSend("[CLASS:DEJAWINT]", "", "", "{Enter}")

ControlSetText ("[CLASS:DEJAWINT]", "", "", $aExcel[1][2])

ControlSend("[CLASS:DEJAWINT]", "", "", $aExcel[1][2])

ControlSend("[CLASS:DEJAWINT]", "", "", "{Enter 4}")

Now I need to continue on with line 2 and so on.

I'm assuming this is where "looping" comes into place?

I'm also assuming I can more then likely clean up some of the controlsend code above.

Thanks again in advance :)

Edited by Vectortech

Share this post


Link to post
Share on other sites

You assume correct.

The most logical choice for this is probably a For...To...Step...Next loop. You should be able to figure it out using the helpfile.

You want to start your loop at 1, because you're using the 1 based array returned by the excel function. Remember that $aExcel[0][0] holds the amount of rows, so you can use that for the end of your loop.

$aExcel[0][1] holds the amount of columns in your array, so you can nest your loops like this:

For $iColumn = 1 To $aExcel[0][1] ;$iColumn will be incremented untill the last column is reached
    For $iRow = 1 To $aExcel[0][0] ;$iRow will be incremented untill the last column is reached
        ConsoleWrite("$aExcel[" & $iRow & "][" & $iColumn & "] = " & $aExcel[$iRow][$iColumn] & @CRLF)
    Next
Next

This script will consolewrite all the values in your array. You need to replace the consolewrite with your own ControlSends

ps: try wrapping your code in [autoit], or

tags.

Share this post


Link to post
Share on other sites

Am I close? It seems to going through the looping proces but adding the same number in over and over.

$path = @DesktopDir & '\cyclecount.xls' ;@Desktopdir replaces "C:\Documents and Settings\Purchasing\Desktop"

$oExcel = _ExcelBookOpen($path,0)

$aExcel = _ExcelReadSheetToArray($oExcel)

;_ArrayDisplay($aExcel)

_ExcelBookClose($oExcel)

WinActivate("[CLASS:DEJAWINT]", "")

For $iColumn = 1 To $aExcel[0][1]

For $iRow = 1 To $aExcel[0][0]

ControlSetText("[CLASS:DEJAWINT]", "", "", $aExcel[1][1])

ControlSend("[CLASS:DEJAWINT]", "", "", $aExcel[1][1])

ControlSend("[CLASS:DEJAWINT]", "", "", "{Enter}")

ControlSend("[CLASS:DEJAWINT]", "", "", "1")

ControlSend("[CLASS:DEJAWINT]", "", "", "{Enter}")

ControlSetText("[CLASS:DEJAWINT]", "", "", $aExcel[1][2])

ControlSend("[CLASS:DEJAWINT]", "", "", $aExcel[1][2])

ControlSend("[CLASS:DEJAWINT]", "", "", "{Enter 4}")

Next

Next

Share this post


Link to post
Share on other sites

Lets say your excelsheet has three rows and two columns.

$aExcel would look like this:

____________

|_3___|_2___|

|_var1_|_var4_|

|_var2_|_var5_|

|_var3_|_var6_|

In a for loop you have a variable that increments each cycle. You can use this variable in the array subscript.

For $iRow = 1 To $aExcel[0][0] ;$iRow wil be 1 on the first loop, 2 on the second and 3 on the third. Then the loop would end.
    ControlSend("[CLASS:DEJAWINT]", "", "", $aExcel[$iRow][1]) ;this will send "var1" on the first loop, "var2" on the second "var3" on the third.
Next

You can use the same principle on columns.

If you have a fixed number of columns you don't have to use another loop though. You can just add another controlsend with $aExcel[$iRow][2] etc.

In your script I see you use ControlSetText keep in mind that that will erase any previous data in the control.

Share this post


Link to post
Share on other sites

Holy crap, I think you got me there Tvern!!

include<array.au3> ;included for _Arraydisplay

#include<excel.au3>

$path = @DesktopDir & '\cyclecount.xls' ;@Desktopdir replaces "C:\Documents and Settings\Purchasing\Desktop"

$oExcel = _ExcelBookOpen($path,0)

$aExcel = _ExcelReadSheetToArray($oExcel)

;_ArrayDisplay($aExcel)

_ExcelBookClose($oExcel)

WinActivate("[CLASS:DEJAWINT]", "")

For $iRow = 1 To $aExcel[0][0] ;$iRow will be incremented untill the last column is reached

ControlSend("[CLASS:DEJAWINT]", "", "", $aExcel[$iRow][1])

ControlSend("[CLASS:DEJAWINT]", "", "", "{Enter}")

ControlSend("[CLASS:DEJAWINT]", "", "", "{1}")

ControlSend("[CLASS:DEJAWINT]", "", "", "{Enter}")

ControlSend("[CLASS:DEJAWINT]", "", "", $aExcel[$iRow][2])

ControlSend("[CLASS:DEJAWINT]", "", "", "{Enter 4}")

Next

Still a bit of testing to do, but I think it's pretty damn close to exactly what I need.

Thanks a ton for your help and patience walking me though the process. :idea:

Lucky

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
Sign in to follow this  
Followers 0