Multiple Copy Commands to Generated Variables

[LostOneInLine]

I think my questions are pretty generic, but I have looked though every forum out there and tried my hardest to figure it out and i lost the battle. So here goes...

I am attempting to copy a large number of cells from excel (i can only copy cell by cell) to another program to fill out a form. If i can get it to work correctly, I will want to copy a few hundred cells and save them to variables. I made a step by step script for copying cells and saving to variables, but a script that large isn't very efficient. Also, the copy command seems to limit itself to 62 copies.

I would like to create a loop for copying and a loop for pasting where the copy creates its own variables and the paste references the same variables.

This is what I have right now (for a test i have it copying one line and pasting it on the line below). Just open up a new book in Excel and type 1, 2, 3 in the first three horizontal cells.:

WinActivate ("Microsoft Excel - Book1")
WinWaitActive ("Microsoft Excel - Book1")
send ("{home}")
send ("^c")
$1 = ClipGet()
send ("{tab}")
send ("^c")
$2 = ClipGet()
send ("{tab}")
send ("^c")
$3 = ClipGet()
send ("{tab}")
Sleep (1000)
send ("{tab}")
send ("{down}")
send ("{home}")
ClipPut ($1)
send ("^v")
send ("{tab}")
ClipPut ($2)
send ("^v")
send ("{tab}")
ClipPut ($3)
MsgBox ( 0, "Line Copy", "Complete")

It also pastes a little too fast and screws up.

You can see how ridiculous this would be for 1000 copy/pastes.

Sadly, this is as good as I got:

WinActivate("Microsoft Excel  Book1")
WinWaitActive("Microsoft Excel  Book1")
send ("{home}")
send ("^c")
$first = ClipGet()
$i = 0
$var = ("$"&$i)
Do
    send ("{tab}")
    send ("^c")
    $var = ClipGet()
    $i = $i + 1
    Until $i = 63
send ("{tab}")
send ("{down}")
send ("{home}")
$var = ("$"&$i)
$i = 0
Do
    ClipPut ($var)
    send ("^v")
    Send ("{tab}")
    $i = $i + 1
    until $i = 63

Also, I'm not too sure on how to have the script check if the window is already open. And I would like to have it switch to a window without giving it the full window name (ie "Book" instead of "Microsoft Excel - Book1.xls"

Something like:

Ifopen, *Book1.xls

activate

else

open

endif

I'm a script newb and I only have a trial and error education; so the more in depth and descriptive you can be, the better. Thanks in advance.

[Zedna]

ExcelCOM_UDF here and here

Opt('WinTitleMatchMode', 2)

[LostOneInLine]

ExcelCOM_UDF here and here

Opt('WinTitleMatchMode', 2)

Ummmm...

I wasn't joking. I really have no idea what I'm doing here. I need step-by-steps or I'm lost.

I read through everything both of those links had to offer. Now I'm just confused. I can't even get the example scripts to work right.

I don't want anything specific to excel.

Just think of it as copying multiple values from a form with multiple form fields and pasting to a different form (in a different program/window) with only switching the window once. I found that switching back and forth copying and pasting for each field is either too unreliable or too slow (depending on "Sleep (x)" times).

I've played around with multiple scripting programs and I like this one the best. I had some advice with AutoHotKey and was given the script below. I later found AutoIt and realized that AHK just wasn't as "user-friendly" as I need.

I still haven't solved the window open / switch problem I was having before.

I could actually use a version of this (maybe):

WinActivate Book1.xls 
setkeydelay, 0, 0
Send, {home}
loop,150
{
Send, ^c 
%a_index%:=clipboardall
Send, {TAB} 
}
Send {Down} 
Send {HOME} 
Loop,150
{
Clipboard:=%a_index%
Send, ^v 
Send, {TAB}
}

Thanks.

[PsaltyDS]

I think my questions are pretty generic, but I have looked though every forum out there and tried my hardest to figure it out and i lost the battle. So here goes...

I am attempting to copy a large number of cells from excel (i can only copy cell by cell) to another program to fill out a form. If i can get it to work correctly, I will want to copy a few hundred cells and save them to variables. I made a step by step script for copying cells and saving to variables, but a script that large isn't very efficient. Also, the copy command seems to limit itself to 62 copies.

I would like to create a loop for copying and a loop for pasting where the copy creates its own variables and the paste references the same variables.

I'm a script newb and I only have a trial and error education; so the more in depth and descriptive you can be, the better. Thanks in advance.

Why can you only copy cell by cell? I suspect it's only because you don't know how to get them all at once. Download a copy of Locodarwin's ExcelCOM_UDF and learn to use that.

You can easily get cells one at time, if you must, read a row or column at a time with _ExcelReadArray(), or get the entire sheet in one array with _ExcelReadSheetToArray(). While learning the UDF may seem complicated at first, it will make your excel scripts MUCH simpler after you do!

Here's a working demo:

#include <ExcelCOM_UDF.au3>

; Open a new instance of Excel
$oExcel = _ExcelBookNew()

; Put numbers in the first three columns
For $n = 1 To 3
    _ExcelWriteCell($oExcel, $n * 5, 1, $n)
Next

Sleep(1000) ; Delay so you can see it work

; Read first row, double it, write to second row, one cell at a time
For $n = 1 To 3
    $Data = _ExcelReadCell($oExcel, 1, $n)
    _ExcelWriteCell($oExcel, $Data * 2, 2, $n)
Next

Sleep(1000) ; Delay so you can see it work

; Read second row, triple it, write to third row, all at once
$avData = _ExcelReadArray($oExcel, 2, 1, 3)
For $n = 0 To UBound($avData) - 1
    $avData[$n] = $avData[$n] * 3
Next
_ExcelWriteArray($oExcel, 3, 1, $avData)

Sleep(2000) ; Delay so you can see it work

_ExcelBookSaveAs($oExcel, "C:\Temp\MyExcel.xls")
_ExcelBookClose($oExcel)

[LostOneInLine]

...

Yup, that is exactly how i look right now. I copied the "example", pasted it into the AutoIt editor, saved it as whatever on the desktop (*.au3), and hit F5.

I got a list of errors, noting anything that used an "_Excel" function. I assume that AutoIt has no idea what _Excel is and that i need to download and install some kind of patch. I downloaded (copied and pasted) the file in the link you posed. It all looks pretty; bunch of colors; and that is about it for me. I understand what is going on with each of them, and what they are expected to do. I can't make them work at all though. It is the same weird errors.

I also don't understand what the "Include" line is about.

Thanks guys.

[PsaltyDS]

...

Yup, that is exactly how i look right now. I copied the "example", pasted it into the AutoIt editor, saved it as whatever on the desktop (*.au3), and hit F5.

I got a list of errors, noting anything that used an "_Excel" function. I assume that AutoIt has no idea what _Excel is and that i need to download and install some kind of patch. I downloaded (copied and pasted) the file in the link you posed. It all looks pretty; bunch of colors; and that is about it for me. I understand what is going on with each of them, and what they are expected to do. I can't make them work at all though. It is the same weird errors.

I also don't understand what the "Include" line is about.

Thanks guys.

Download the ExcelCOM_UDF.au3 file from the link given above, and save it in the Include subdirectory of your AutoIt3 program folder. If you open that file, you'll see a bunch of variables and functions declared. By putting #include <ExcelCOM_UDF.au3> at the top of your script, you included all the code in that file into your script as though you has copy/pasted it in by hand. I tested the demo I posted, and it works if you have the ExcelCOM_UDF.au3 file available.

[LostOneInLine]

Wow.

I'm pretty sure I can get it from here.

I wish I could help you in return. Ummm... need any help with drafting programs? hehe

Your my hero Salty.