Copy/Paste excel cells until cell is blank

[timmyc]

Hey all,

Need a bit of a hand with this one. Essentially i am trying to copy an excel cell, paste it somewhere go back to excel, go down a cell and repeat until the cell is blank. I have a larger chunk of script that i will be using instead of this small task (copy and pasting into notepad) but i just wanted to loop something basic until i can get this working. 

This is my first time playing with AutoIt and excel. I suspect where i am going wrong is that i'm telling it to read a cell range in excel where as i need to tell it to read 1 cell at a time until cell = blank. Should i be adding in some logic where it increases the cell number each loop or should i be building an array from the column then calling back on those values?

 

#include <Array.au3>
#include <Excel.au3>
#include <MsgBoxConstants.au3>


;Essentially i am trying to copy an excel cell, paste it somewhere go back to excel, go down a cell and repeat untill the cell is blank

Local $oExcel = _Excel_Open()
Local $sWorkbook = @ScriptDir & "\Book1.xls"
Local $oWorkbook = _Excel_BookOpen($oExcel, $sWorkbook)

;Unsure if instead of using a cell range i should be getting excel to read one cell, then in the next loop add a 1 to the cell and read again. Eg. Read A1 do function, read A2 do function
Local $aResult = _Excel_RangeRead($oWorkbook,1, "A1:A14")

Do
   WinActivate("Microsoft Excel - Book1.xls")
   Send("{CTRLDOWN}c{CTRLUP}")
   ;Copy cell
   WinActivate("Untitled - Notepad")
   Send("{CTRLDOWN}v{CTRLUP}")
   ;Paste cell
   WinActivate("Microsoft Excel - Book1.xls")
   ;Go down a cell
   Send("{ENTER}{ENTER}")
Until $aResult = ""

Exit

Thanks for your help!

[232showtime]

I have different approach for your problem, 

#include <Array.au3>
#include <Excel.au3>
#include <MsgBoxConstants.au3>


;Essentially i am trying to copy an excel cell, paste it somewhere go back to excel, go down a cell and repeat untill the cell is blank

Local $oExcel = _Excel_Open()
Local $sWorkbook = "Book1.xlsx"
Local $oWorkbook = _Excel_BookAttach($sWorkbook, "filename")
If WinExists("Book1") Then
    ;Unsure if instead of using a cell range i should be getting excel to read one cell, then in the next loop add a 1 to the cell and read again. Eg. Read A1 do function, read A2 do function
    $aResult = _Excel_RangeRead($oWorkbook, $oWorkbook.Activesheet, "A1:A14", 1)

    For $i = 0 To UBound($aResult) - 1
        MsgBox($MB_OK, "Open", "Open Calculator???" & @CRLF & "Excel Value: " & $aResult[$i])
        Run("calc.exe")
        Sleep(300)
        MsgBox($MB_OK, "Close", "Close Calculator???")
        ProcessClose("calc.exe")
        If $aResult[$i] = "" Then MsgBox($MB_ICONERROR, "Error!!!", "Range A" & $i & " is Blank")
    Next
Else
    MsgBox($MB_OK, "Exit", "Book1 does not exist")
    Exit
EndIf

read the helpfile for excelrange read as well.

Edited October 15, 2017 by 232showtime
Small modification.