XL file to second application data transfer

[Vectortech]

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.

[PsaltyDS]

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.

[Vectortech]

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.

[Tvern]

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.

[Vectortech]

Thanks for the great start Tven.

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.

[Vectortech]

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

[Vectortech]

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}")

[Tvern]

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}")

[Vectortech]

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

[Tvern]

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.

[Vectortech]

Houston we have a problem.

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

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"

[Vectortech]

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

#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 May 21, 2010 by Vectortech

[Tvern]

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.

[Vectortech]

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

[Tvern]

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.

[Vectortech]

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.

Lucky