Sign in to follow this  
Followers 0
JasonTHEchristian

Incremental copy in excel 2003

9 posts in this topic

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

Share this post


Link to post
Share on other sites



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?

Share this post


Link to post
Share on other sites

#3 ·  Posted (edited)

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 by Spiff59

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

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!

Share this post


Link to post
Share on other sites

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?

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

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!

Share this post


Link to post
Share on other sites

Thanks for your help everybody, it worked great!

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