Excel Event Trigger

[treniets]

(I inadvertantly posted this in Scripts and Scraps and could not find a way to move it. I re-posted here.)

I'm new to AutoIt. I managed to write some useful scripts, but I am lost on this one.

I have a Spreadsheet that uses a timer to read a Text File and analyze the File. When there is sufficient information to act on, one Cell is set to "Yes" (it could be set to anything, including different colors, etc.).

I would like AutoIT to monitor the "Yes" Cell and start to run it's commands when the Cell changes from "No" to "Yes". I have the AutoIT script (import Excel data, send the data to a third program, get the output from the third program and send it back to Excel) but I have to initate AutoIt manually.

I've looked through the GUI event commands, but they seem to work only for GUIs created in AutoIt. Is that correct, or is it possible to use them for an Excel event? How?

I could probably set up a simple Spreadsheet that only reads the "Yes" Cell through a link to the main Spreadsheet. The simple Spreadsheet should only change when the linked Cell changes to/from Yes/No. Is it possible that any change in this Spreadsheet could be used to trigger AutoIt.

Any help is appreciated.

Bob

[Simucal]

Would it be possible to allow autoit to open the excel file with the "Yes/No" cell in it?

If you would let AutoIt open it from the very begining.. it would be possible to use ExcelCom UDF's to check the value in a loop, and when it changed to a certain value, act on it. It simply has to be the one who initiated opening the file.

Let me know if this is possible for your setup and I'll write something for you.

-Simucal

[CyberSlug]

Is your timer part of a VB macro?

[treniets]

Would it be possible to allow autoit to open the excel file with the "Yes/No" cell in it?

If you would let AutoIt open it from the very begining.. it would be possible to use ExcelCom UDF's to check the value in a loop, and when it changed to a certain value, act on it. It simply has to be the one who initiated opening the file.

Let me know if this is possible for your setup and I'll write something for you.

-Simucal

Simucal:

I have been looking at the ExcelCOM UDF but I am a bit confused how to set it up: where to put the files, etc. I downloaded the latest Beta Version and assume I have to use it, but I am still cofused. I'll try to figure it out but any simple instructions for simple people would be appreciated. (I am a self taught newbie programmer and get lost easily.)

Thanks for your help.

Bob

[treniets]

Is your timer part of a VB macro?

Yes. It s a simple Macro I wrote.

A timer is run. It runs a second timer that triggers the Macro to import the Text. The second timer then triggers the first timer, creating a timed loop.

All Help Appreciated.

Bob

[Simucal]

After some struggling with ExcelCom opening the excel file in read only mode I simply called the "Excel.Application" object within the script I wrote for you. So you dont need to include anything.

This will open an excel file of your choice and check cell "A1" every second to see if its value = "Yes", if it is yes, then it will give you a MsgBox. You should be able to easily convert this to whatever your purposes are. Please note that the excel file MUST be opened via the script, the obj must be created by AutoIt. I dont see how this would be a problem.

Also, if you dont want the excel file to be visible while it is doing it's checking change $ObjExcel.Visible to false.

Cheers!

-Simucal

MsgBox(0,"Demo", 'First select the location of your excel file with the "Yes/No" cell.')
$FilePath = FileOpenDialog("Excel File", @ScriptDir, "Excel Files (*.xls)")
If @error Then
    MsgBox(48,"Demo","You must select an Excel File!")
    Exit
EndIf

$ObjExcel = ObjCreate("Excel.Application")
$ObjExcel.Visible = True
$ObjWorkbook = $ObjExcel.Workbooks.Open($FilePath, 0, "false")

While 1 
    $ObjExcel.ActiveWorkbook.Save()
    $CellString = $ObjExcel.Cells(1,"A").Value
    If $CellString = "Yes" Then; checking the cell A1 to see if it is yes. Note, Column goes after row in this setup
    ; Run your code that would execute if the cell was YES here
        MsgBox(0,"Demo", "Cell A1 has been changed to YES! Value: "&$CellString)
        ExitLoop
    EndIf
    MsgBox(0,"Demo",$CellString)
    Sleep(1000); check the cell every 1 seconds
Wend

[treniets]

After some struggling with ExcelCom opening the excel file in read only mode I simply called the "Excel.Application" object within the script I wrote for you. So you dont need to include anything.

This will open an excel file of your choice and check cell "A1" every second to see if its value = "Yes", if it is yes, then it will give you a MsgBox. You should be able to easily convert this to whatever your purposes are. Please note that the excel file MUST be opened via the script, the obj must be created by AutoIt. I dont see how this would be a problem.

Also, if you dont want the excel file to be visible while it is doing it's checking change $ObjExcel.Visible to false.

Cheers!

-Simucal

WOW! Looks like it is just what I need. I should be able put in my own file paths, etc, and maybe some Bells and Whistles if I read the instructions.

I'll have to install the ExcelCOM UDF and Beta Version. It should work on my Desktop (XP and Win 2000).

I'll try to use AutoIt to copy the Text File and paste it into a separate Spreadsheet linked to my main Spreadsheet until I figure out how to open the correct WorkSheet in my main WorkBook.

Thanks,

Bob

[Simucal]

You dont need to install the ExcelCOM UDF for this example. It makes the obj calls on its own by hand. It has doesnt need anything, except maybe beta (Not sure ObjCreate is beta or not).

However, if you want help with the rest of your script.. shoot me an AIM or MSN message.

-Simucal

[DaleHohm]

If you really want Event Driven processing, the Excel.Application object provides a lot of them that you can tie into with ObjEvent

More inforamtion on MSDN here

Dale

[Simucal]

If you really want Event Driven processing, the Excel.Application object provides a lot of them that you can tie into with ObjEvent

More inforamtion on MSDN here

Dale

If you would check what I wrote for him, it uses the Excel.Application object.

[DaleHohm]

If you would check what I wrote for him, it uses the Excel.Application object.

Sorry, wasn't trying to detract from what you did. ObjEvent and the events that you can subscribe to in Excel would have to be used with code like what you wrote.

In your code example you are polling the value of a cell every second I believe... the event model allows you to trigger an action based on when an event actually occurs without the overhead of polling.

Dale

[Simucal]

Really? That sounds interesting... I'll look into that!

Sorry for misunderstanding your post.