Incremental copy in excel 2003

[JasonTHEchristian]

Hello,

I have a 2 column list of data in excel that varies in length. I copy the data from this list, one cell at a time and paste it into another application (encompix) which does some other functions with the data. I would like the script to continue until the active cell is blank, and instead of writing another line for each cell how can i tell autoIT to move incrementally from the first cell for example start in a1 then b1 then a2 and b2 then a3 and b3 and continue until cell="". Here is a sample of my code. Thanks in advance for your help

$oExcel = ObjGet("","Excel.Application")
WinActivate("Job Order Browser - \\Remote")   ;switches to encompix
send("{ALT}tu")                                                    ;opens duplicator window

With $oExcel
Do
if .activeCell="" then ExitLoop
   .Range("a1").Select
   .ActiveCell.copy
   WinActivate("Job Order Browser - \\Remote")  
   send("{tab}")
    send("^v")
  $oExcel.Application.ActiveSheet.Range("b1").Select
    .ActiveCell.copy
   WinActivate("Job Order Browser - \\Remote")  
   send("{tab}{tab}{right}{tab}^v{tab}{tab}{tab}{space}")
   send("{tab}{tab}{tab}{space}")
   sleep(1000)
   WinActivate("Job Order Browser - \\Remote")    ;switches to encompix
   send("{ALT}tu")                                                   ;opens duplicator window
  
Until

EndWith

[JasonTHEchristian]

So basically what im asking is... how do you write a script that says for each iteration of the loop move the cell down 1?

[Spiff59]

Not having, or being familiar with, Emcompix, I haven't a clue about all the Send() statements, but you might try playing with a control loop something like this:

Global $oExcel, $row = 1, $col_a, $col_b

;-------------------------------------------------------------------------------
$oExcel = ObjCreate("Excel.Application")
$oExcel.Visible = 0
$oExcel.WorkBooks.Open(@ScriptDir & "test.xls")
;WinActivate("Job Order Browser - Remote")
;send("{ALT}tu")

;-------------------------------------------------------------------------------
While 1
    $col_a = $oExcel.Activesheet.Cells($row, 1).Value
    If $col_a = "" Then ExitLoop
    $col_b = $oExcel.Activesheet.Cells($row, 2).Value
    Update_Encompix()
    $Row += 1
WEnd

$oExcel.ActiveWorkbook.Close
ToolTip($Row - 1 & " ROWS PROCESSED")
Exit

;-------------------------------------------------------------------------------
Func Update_Encompix()
;   send("{tab}" & $col_a)
;   send("{tab}{tab}{right}{tab} & $col_B)
;   send("{enter}")
    ToolTip($col_a & "  " & $col_b)
    sleep(50)
EndFunc

Edit: PS - Welcome to the forum

Edited February 20, 2012 by Spiff59

[JasonTHEchristian]

Thanks! I'll try that out and let you know what happens!

[JasonTHEchristian]

I appreciate your time, however it didnt work for me. Im probably doing a terrible job at explaining my situation. The encompix program is just another window that duplicates, "jobs" but it will only do 1 at a time, so to speed up the process i have a 2 column list in excel with a copy from value and a copy to value. There are some parameter's to set in the window (which explains the send commands) and finally a "duplicate" button is hit, the program is slow and after hitting the button may take up to a full minute to process the function...the code i have currently written and pasted at the top works fine, but only for the first 2 value's in cell's a1 and b1, i'd like the script to continue down the list until there is no more data... im not sure if that explanation helps, hopefully i wasnt just being circumlocutious!

[Spiff59]

I'm able to park any spreadsheet (renamed test.xls) in the same directory as that test script above and when run, it will pass through the entire spreadsheet, retrieving for each row the values in column A and B until it encounters an empty Column A. For each line of the spreadsheet, I made it call the user-defined function "Update_Encompix()", which, not having the software, I just made into a simple on-screen display.

Does not this Excel portion, that parses the spreadsheet, work for you?

If not, are you getting an error of some sort?

[Blinky]

hi this is a remake of your script using Excel.au3.

this will help u if u have the excel file on your pc.

it includes a loop jumping rows

#include <Excel.au3>
$oExcel=_ExcelBookOpen(@ScriptDir & "test.xls",0); replace path and file name
;_ExcelSheetActivate($oExcel,1)
WinActivate("Job Order Browser - Remote")  ;switches to encompix
send("{ALT}tu")      ;opens duplicator window
For $i=1 to 100
$a=_ExcelReadCell($oExcel,$i,1)
If $a="" Then ExitLoop
ClipPut($a)
MsgBox(0,"",$a)
WinActivate("Job Order Browser - Remote")
send("{tab}")
send("^v")
$b=_ExcelReadCell($oExcel,$i,2)
ClipPut($b)
WinActivate("Job Order Browser - Remote")
send("{tab}{tab}{right}{tab}^v{tab}{tab}{tab}{space}")
send("{tab}{tab}{tab}{space}")
sleep(1000)
WinActivate("Job Order Browser - Remote")    ;switches to encompix
send("{ALT}tu")       ;opens duplicator window
Next
_ExcelBookClose($oExcel)

hope it helps

[JasonTHEchristian]

Spliff and blinky both thanks for your help, i've been overwhelmed by work, let me try both of these out and i'll let you know what happens, again thanks for your help! Even if it doesnt work, you guys deserve to be paid for your willingness! THANK YOU!

[JasonTHEchristian]

Thanks for your help everybody, it worked great!