Jump to content
timmyc

Copy/Paste excel cells until cell is blank

Recommended Posts

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!

Share this post


Link to post
Share on other sites
232showtime

I have different approach for your problem,  :P:P:P

#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 by 232showtime
Small modification.

ill get to that... i still need to learn and understand a lot of codes graduated.gif

Correct answer, learn to walk before you take on that marathon.

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

×

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.