Logic Help

[treniets]

I am trying to use AutoIt to copy/paste data from one program to other programs (mostly spreadheets). I have setup one spreadsheet ("Yes Events") that simply shows a 1 in cell A1 if the data is ready to copy from another spreadsheet or a 0 (zero) if the data is not ready to copy.

The proram has an endless While loop so that it is always looking to see if the A1 cell is set to 1. It copies the 1 or 0 into $Yes and interprets a 1 as "Yes" the data is ready to copy and 0 as "No" the data is not ready to copy.

I would like it if the data is copied only when it is refreshed; that is I do not want to endlessly copy the same data. The A1 cell will change from 1 ("Yes") to 0 ("No") and back to 1 again when the data is refreshed.

I have tried different If logic statements to determine if the data has been refreshed, but my Brains internal logic has not been able to figure it out. The Code shows my attempt to use a second variable, $Yes2, that is supposed to be reset each time the data is copied and only allow the program to execute when the data is refreshed. (The first pass will always have fresh data and can be copied as long as A1 = 1.)

All Help is Appreciated.

--------------------------------------------

Dim $Yes

Dim $Yes2

$Yes = 0 ;Set $Yes to zero ("No")

$Yes2 = 0 ;Set $Yes2 to zero ("No")

While 1

; Open Spreadsheet

WinActivate("Microsoft Excel - Yes Event.xls", "")

WinWaitActive("Microsoft Excel - Yes Event.xls")

; Move to Cell A1 ("Yes" Cell)

Sleep(1000)

Send("{HOME}{CTRLUP}{HOME}{CTRLDOWN}{UP}{CTRLUP}{CTRLDOWN}{UP}{CTRLUP}")

Sleep(1000)

; Copy from Yes Cell (A1)

; If Yes Cell = 1, means "Yes"

; If Yes Cell = 0, means "No"

Send("^c")

sleep(1000)

$Yes = ClipGet()

; Display $Yes and $Yes2

MsgBox(0, "1st $Yes =", $Yes, 2)

MsgBox(0, "1st $Yes2 =", $Yes2, 2)

ClipPut("")

Sleep(1000)

; Determine if Cell A1 has changed to "No" (0) and back to "Yes" (1)

If $Yes = 1 Then

Sleep(1000)

IF $Yes2 = 0 Then

$Yes2 = 1

ElseIf $Yes2 = 1 Then

$Yes2 = 0

Sleep(1000)

MsgBox(0, "2nd $Yes =", $Yes, 2)

MsgBox(0, "2nd $Yes2 =", $Yes2, 2)

Sleep(1000)

; Proceed with rest of Code if Yes Cell = "Yes" (1)

; and Cell has changed to "No" (0) and back to "Yes" (1)

WinActivate("Microsoft Excel - Input.xls", "")

WinWaitActive("Microsoft Excel - Input.xls")

; Lots of ClipGet, ClipPut and Activate other programs code in this area.

EndIf

EndIf

Sleep(5000)

WEnd

-----------------------------------------

I also have to start using the ExcelCOM UDF, as may be obvious from the Send("{HOME}{CTRLUP}{HOME}{CTRLDOWN}{UP}{CTRLUP}{CTRLDOWN}{UP}{CTRLUP}") statement; it was the only way I could reliably get to Cell A1 (???).

Thanks,

Bob

[SmOke_N]

I'm sure someone else will understand your specific needs more than I do... but I don't understand you Send() command really. You have

Send("{HOME}{CTRLUP}{HOME}{CTRLDOWN}{UP}{CTRLUP}{CTRLDOWN}{UP}{CTRLUP}")

The first Ctrl command you do is Ctrl up, isn't it already up or am I missing something with the rest of your script?

My assumption would be that you mean to do:

Send('{HOME 2}^{UP 2}')

? Sorry I can't be of help though... The only other thing I would suggest that I can see, is your getting data from the clip board, you have that data being recognized as an integer ('1'), so $Yes = 1 (so the clipboard is going to have the number '1'? Might try it as a string:

expandcollapse popup

Dim $Yes = 0, $Yes2 = 0
While 1
; Open Spreadsheet
    WinActivate("Microsoft Excel - Yes Event.xls", "")
    WinWaitActive("Microsoft Excel - Yes Event.xls")
; Move to Cell A1 ("Yes" Cell)
    Sleep(1000)
    Send('{HOME 2}^{UP 2}')
    Sleep(1000)
; Copy from Yes Cell (A1)
; If Yes Cell = 1, means "Yes"
; If Yes Cell = 0, means "No"
    Send("^c")
    Sleep(1000)
    $Yes = ClipGet()
; Display $Yes and $Yes2
    MsgBox(0, "1st $Yes =", $Yes, 2)
    MsgBox(0, "1st $Yes2 =", $Yes2, 2)
    ClipPut("")
    Sleep(1000)
; Determine if Cell A1 has changed to "No" (0) and back to "Yes" (1)
    If $Yes = '1' Then
        Sleep(1000)
        If $Yes2 = 0 Then
            $Yes2 = 1
        Else
            $Yes2 = 0
            Sleep(1000)
            MsgBox(0, "2nd $Yes =", $Yes, 2)
            MsgBox(0, "2nd $Yes2 =", $Yes2, 2)
            Sleep(1000)
; Proceed with rest of Code if Yes Cell = "Yes" (1)
; and Cell has changed to "No" (0) and back to "Yes" (1)
            WinActivate("Microsoft Excel - Input.xls", "")
            WinWaitActive("Microsoft Excel - Input.xls")
; Lots of ClipGet, ClipPut and Activate other programs code in this area.
        EndIf
    EndIf
    Sleep(5000)
WEnd

Hope you get something out of it and get it worked out.

Edit:

Had to fix code tags.

Edit2:

I've not tried to do anything in Excel, but would the ControlCommands() work there?

Edited April 15, 2006 by SmOke_N

[treniets]

I'm sure someone else will understand your specific needs more than I do... but I don't understand you Send() command really. You have

Send("{HOME}{CTRLUP}{HOME}{CTRLDOWN}{UP}{CTRLUP}{CTRLDOWN}{UP}{CTRLUP}")

The first Ctrl command you do is Ctrl up, isn't it already up or am I missing something with the rest of your script?

My assumption would be that you mean to do:

Send('{HOME 2}^{UP 2}')

? Sorry I can't be of help though... The only other thing I would suggest that I can see, is your getting data from the clip board, you have that data being recognized as an integer ('1'), so $Yes = 1 (so the clipboard is going to have the number '1'? Might try it as a string:

Dim $Yes = 0, $Yes2 = 0

Edit:

Had to fix code tags.

Edit2:

I've not tried to do anything in Excel, but would the ControlCommands() work there?

SmOke_N:

Thanks

I'll try Send('{HOME 2}^{UP 2}'). I copied the more confusing 'Send' from a complex Spreadsheet which goes to many different Cells. I tried simplifyng it for that Spreadheet, but the only thing that worked for me was a multi 'Send' like the one in this code. Maybe the caret will be the magic bullet.

My first attempts for the 'If' code used the strings Yes and No. I switched to numbers because I wasn't sure if the strings were confusing things (different Formats/Fonts?). The Msg boxes that show either the numbers 1 and 0 or the strings Yes and No looked OK to me. I'll try 1 as a string but the code is gettng past the 'If $Yes = 1 Then' statement.

Thanks for fixing the code. As I am writng this I have just discovered the Code button at the top! And the Emoticons; now I'm dangerous!

Bob

[Simucal]

Rather than copy and paste from open excel windows, would it be possible for your project to simply extract the data via ExcelCOM and send the data to whatever excel file you wanted in whatever format you wanted?

I think running everything in the backend is much more sleek than physical automation. If you have the option, I would choose to do it that way.

If you need help with the ExcelCOM, I would be happy to.

Edited April 15, 2006 by Simucal

[treniets]

Rather than copy and paste from open excel windows, would it be possible for your project to simply extract the data via ExcelCOM and send the data to whatever excel file you wanted in whatever format you wanted?

I think running everything in the backend is much more sleek than physical automation. If you have the option, I would choose to do it that way.

If you need help with the ExcelCOM, I would be happy to.

[treniets]

Rather than copy and paste from open excel windows, would it be possible for your project to simply extract the data via ExcelCOM and send the data to whatever excel file you wanted in whatever format you wanted?

I think running everything in the backend is much more sleek than physical automation. If you have the option, I would choose to do it that way.

If you need help with the ExcelCOM, I would be happy to.

Simucal:

It's starting to sink in; I'm sure I am wasting time not doing it the right way in the first place.

From the little I know of ExcelCOM I do not think t will work on the Laptop I am writing this with (Win ME, 64K, Windows 2000). I downloaded ExcelCOM and got an error message when I tried to run it. My desktop is running nearly 24/7 using AutoIT to generate stat tables with AutoIt, Excel and a calculation program. I'd probably be better off interruppting that and using ExcelCOM.

My Desktop is Win XP with Windows 2000. Will that work with ExcelCOM?

Some basic instructions with ExcelCOM would be helpful; maybe in a separate post in Scripts/ Scraps so everyone can benefit.

Does ExcelCOM Require the Latest Beta Version of AutoIt?

Should it be installed instead of the official version?

Where should ExcelCOM be installed?

Does ExcelCOM require special Include functions?

What Op system and Windows version.

Are there help files?

All help would be appreciated.

THANKS,

Bob

[Simucal]

Well, the benefits of doing things behind the scenes instead of automating physical windows would be great. You would STILL be able to use your computer without fear of messing up your generation of stat tables.

Does ExcelCOM Require the Latest Beta Version of AutoIt?

Yes, It requires beta due to its use of Objects

Should it be installed instead of the official version?

As I understand it... you must have the official version in order to install BETA.

Where should ExcelCOM be installed?

Well, it is just a .au3 file that needs to be #included in your future script. So I would make a folder on your desktop and have your script, plus whatever includes you need there.

Does ExcelCOM require special Include functions?

Just one: #include"ExcelCom.au3"

What Op system and Windows version.

The windows version doesnt really matter when using the ExcelCOM functions as far as I know. That only thing that might effect it is if you were using a very old version of excel.

Are there help files?

If you edit the excelcom.au3 it gives you some basic information about command syntax, etc. There are also some examples provided in the ExcelCOM.

Why dont you PM me your MSN or AIM name, then I'll help you get something thrown together. Then we can post the end result here for people to see.

Edited April 15, 2006 by Simucal