Copy-paste issues in excel

[modernes]

Hi,

I am trying to create a script that will (1)copy one cell and (2)paste the info into an empty cell in excel in a list until the list(1) is complete. I am finding that using the send function is not working very well to copy and paste the data. Any ideas?

[Locodarwin]

Hi,

I am trying to create a script that will (1)copy one cell and (2)paste the info into an empty cell in excel in a list until the list(1) is complete. I am finding that using the send function is not working very well to copy and paste the data. Any ideas?

Absolutely. Have a look at the UDF proposal link in my signature. Download that, place it in your \Include or script directory, and you'll have a decent package of functions to help you automate Excel, including copy & paste.

Unfortunately, I don't yet have any new example programs to help show you how to use the functions - so here's the basics on how a task such as yours would be done:

#include <ExcelCOM_UDF.au3>   ; include the UDF

; Open your Excel workbook as an object and make it visible
$oBook = _ExcelBookOpen("C:\The_Excel_File.xls", 1)  ; replace the filepath with your own

; Copy and paste a range of cells to another location
_ExcelCopy($oBook, "A1:A999")  ; you can use R1C1 format, too - check the function's header for syntax
_ExcelPaste($oBook, "B1")

; Save and close the workbook
_ExcelBookClose($oBook)

To copy & paste based on a range you don't know the size of at runtime, you could create a loop that checks each individual cell in your list with _ExcelRead() to see if it is empty or whatever, and once you figure out where the end of your list is, perform the copy. That would require using R1C1 cell addressing to make iterating through the cells easier.

Hope that helps.

-S

Edited October 27, 2006 by Locodarwin