Sign in to follow this  
Followers 0
treniets

Excel Event Trigger

12 posts in this topic

(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

Share this post


Link to post
Share on other sites



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


AutoIt Scripts:Aimbot: Proof of Concept - PixelSearching Aimbot with several search/autoshoot/lock-on techniques.Sliding Toolbar - Add a nice Sliding Toolbar to your next script. Click the link to see an animation of it in action!FontInfo UDF - Get list of system fonts, or search to see if a particular font is installed.Get Extended Property UDF - Retrieve a files extended properties (e.g., video/image dimensions, file version, bitrate of song/video, etc)

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

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

AutoIt Scripts:Aimbot: Proof of Concept - PixelSearching Aimbot with several search/autoshoot/lock-on techniques.Sliding Toolbar - Add a nice Sliding Toolbar to your next script. Click the link to see an animation of it in action!FontInfo UDF - Get list of system fonts, or search to see if a particular font is installed.Get Extended Property UDF - Retrieve a files extended properties (e.g., video/image dimensions, file version, bitrate of song/video, etc)

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

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


AutoIt Scripts:Aimbot: Proof of Concept - PixelSearching Aimbot with several search/autoshoot/lock-on techniques.Sliding Toolbar - Add a nice Sliding Toolbar to your next script. Click the link to see an animation of it in action!FontInfo UDF - Get list of system fonts, or search to see if a particular font is installed.Get Extended Property UDF - Retrieve a files extended properties (e.g., video/image dimensions, file version, bitrate of song/video, etc)

Share this post


Link to post
Share on other sites

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


Free Internet Tools: DebugBar, AutoIt IE Builder, HTTP UDF, MODIV2, IE Developer Toolbar, IEDocMon, Fiddler, HTML Validator, WGet, curl

MSDN docs: InternetExplorer Object, Document Object, Overviews and Tutorials, DHTML Objects, DHTML Events, WinHttpRequest, XmlHttpRequest, Cross-Frame Scripting, Office object model

Automate input type=file (Related)

Alternative to _IECreateEmbedded? better: _IECreatePseudoEmbedded  Better Better?

IE.au3 issues with Vista - Workarounds

SciTe Debug mode - it's magic: #AutoIt3Wrapper_run_debug_mode=Y Doesn't work needs to be ripped out of the troubleshooting lexicon. It means that what you tried did not produce the results you expected. It begs the questions 1) what did you try?, 2) what did you expect? and 3) what happened instead?

Reproducer: a small (the smallest?) piece of stand-alone code that demonstrates your trouble

Share this post


Link to post
Share on other sites

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

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


AutoIt Scripts:Aimbot: Proof of Concept - PixelSearching Aimbot with several search/autoshoot/lock-on techniques.Sliding Toolbar - Add a nice Sliding Toolbar to your next script. Click the link to see an animation of it in action!FontInfo UDF - Get list of system fonts, or search to see if a particular font is installed.Get Extended Property UDF - Retrieve a files extended properties (e.g., video/image dimensions, file version, bitrate of song/video, etc)

Share this post


Link to post
Share on other sites

:think: 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


Free Internet Tools: DebugBar, AutoIt IE Builder, HTTP UDF, MODIV2, IE Developer Toolbar, IEDocMon, Fiddler, HTML Validator, WGet, curl

MSDN docs: InternetExplorer Object, Document Object, Overviews and Tutorials, DHTML Objects, DHTML Events, WinHttpRequest, XmlHttpRequest, Cross-Frame Scripting, Office object model

Automate input type=file (Related)

Alternative to _IECreateEmbedded? better: _IECreatePseudoEmbedded  Better Better?

IE.au3 issues with Vista - Workarounds

SciTe Debug mode - it's magic: #AutoIt3Wrapper_run_debug_mode=Y Doesn't work needs to be ripped out of the troubleshooting lexicon. It means that what you tried did not produce the results you expected. It begs the questions 1) what did you try?, 2) what did you expect? and 3) what happened instead?

Reproducer: a small (the smallest?) piece of stand-alone code that demonstrates your trouble

Share this post


Link to post
Share on other sites

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

Sorry for misunderstanding your post.


AutoIt Scripts:Aimbot: Proof of Concept - PixelSearching Aimbot with several search/autoshoot/lock-on techniques.Sliding Toolbar - Add a nice Sliding Toolbar to your next script. Click the link to see an animation of it in action!FontInfo UDF - Get list of system fonts, or search to see if a particular font is installed.Get Extended Property UDF - Retrieve a files extended properties (e.g., video/image dimensions, file version, bitrate of song/video, etc)

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
Sign in to follow this  
Followers 0