KevinGood Posted June 12, 2007 Share Posted June 12, 2007 I am writing a program that requires I read data from an excel sheet as it updates. (It has a DDE with another program) I can currently able to read in a single cell from excel, but as the cell updates my variable does not. Any ideas on how I might be able to keep my variable up to date would be much appreciated. Speed is a factor which limits out saving, closing and reopening the excel sheet. Link to comment Share on other sites More sharing options...
/dev/null Posted June 12, 2007 Share Posted June 12, 2007 I am writing a program that requires I read data from an excel sheet as it updates. (It has a DDE with another program) I can currently able to read in a single cell from excel, but as the cell updates my variable does not. Any ideas on how I might be able to keep my variable up to date would be much appreciated. Speed is a factor which limits out saving, closing and reopening the excel sheet.any sample data you can provide?CheersKurt __________________________________________________________(l)user: Hey admin slave, how can I recover my deleted files?admin: No problem, there is a nice tool. It's called rm, like recovery method. Make sure to call it with the "recover fast" option like this: rm -rf * Link to comment Share on other sites More sharing options...
KevinGood Posted June 12, 2007 Author Share Posted June 12, 2007 At the moment this is the portion of code I use to read in a value. I do not have any sample data as my excel sheet reads from a data feed that I would not be able to provide on here. func readexcel() $oExcel = objCreate("Excel.Application") $oExcel.Visible = 0 $oBooks = $oExcel.Workbooks.Open("c:\test.xls") $oSheet = $oBooks.Worksheets(1) $var = $oSheet.Range("A1").value EndFunc Any help would be very much appreciated, this has set me back a long ways with my program. Link to comment Share on other sites More sharing options...
zfisherdrums Posted June 12, 2007 Share Posted June 12, 2007 I tried a quick experiment to see if I could hook into the Worksheet_Change event using ObjEvent. No luck for me; maybe someone else will have a better outcome. Are you in control of maintaining this spreadsheet? If so, would it be permissible to implement some VBA code-behind with reference to AutoItX object? For instance, placing this code in an Excel module: Public Sub Test(text As String) Dim AutoItX As AutoItX3Lib.AutoItX3 Set AutoItX = New AutoItX3Lib.AutoItX3 DoEvents With AutoItX .ToolTip ("This changed: " & text) End With Set AutoItX = Nothing End Sub And this in the VBA editor for Sheet1: Private Sub Worksheet_Change(ByVal Target As Excel.Range) Test (Target.Address) End Sub Now the document is reacting when it has received changes. Instead of a tooltip, perhaps you could write to the registry or change some field in a hidden window? Identify .NET controls by their design time namesLazyReader© could have read all this for you. Unit Testing for AutoItFolder WatcherWord Doc ComparisonThis here blog... Link to comment Share on other sites More sharing options...
/dev/null Posted June 12, 2007 Share Posted June 12, 2007 At the moment this is the portion of code I use to read in a value. I do not have any sample data as my excel sheet reads from a data feed that I would not be able to provide on here.func readexcel()$oExcel = objCreate("Excel.Application")$oExcel.Visible = 0$oBooks = $oExcel.Workbooks.Open("c:\test.xls")$oSheet = $oBooks.Worksheets(1)$var = $oSheet.Range("A1").valueEndFuncAny help would be very much appreciated, this has set me back a long ways with my program.I meant excel sheets to test with.... __________________________________________________________(l)user: Hey admin slave, how can I recover my deleted files?admin: No problem, there is a nice tool. It's called rm, like recovery method. Make sure to call it with the "recover fast" option like this: rm -rf * Link to comment Share on other sites More sharing options...
KevinGood Posted June 13, 2007 Author Share Posted June 13, 2007 I meant excel sheets to test with..../dev/null, I can send you a sample sheet with a feed from a made up program called stock. You would not be able to run the actual sheet as it feeds out of a unavailable source. http://office.microsoft.com/en-us/excel/HP...=CH010004921033 is a little help as to how I set up the DDE.The uploader would not let me upload a .xls file. Just change the file extension of test.txt to .xls to veiw the sheet.zfisherdrums, I like the sound of it responding when it recieves changes, but to be honest that went right over my head . I need to somehow get the change sent to (or read from) autoit into a variable. It appears your code displays the change, but doesnt get it into autoit.test.txt Link to comment Share on other sites More sharing options...
zfisherdrums Posted June 13, 2007 Share Posted June 13, 2007 Kevin,Sorry if I caused any confusion. Let me ask an additional clarifying question: What specific action are you needing AutoIt to do when the value changes? Are you needing it to open something? close something? click something? run something? I ask because it may be possible to have that action live inside the workbook as well. Let's say that in the example you provided, if the stock price goes under a certain amount then you want to "Buy". Let's imagine that the "Buy" action would involve activating an application, navigating to a buy screen and clicking the "BUY" button. In that scenario, it may be easier to have the workbook trigger the "Buy" action rather than have a script running and waiting for the trigger. The "Buy" action could live inside the workbook ( using a reference to AutoItX3 like in my first example ) OR it could be an AutoItScript that gets run from Excel. So what specific action are you looking for? Identify .NET controls by their design time namesLazyReader© could have read all this for you. Unit Testing for AutoItFolder WatcherWord Doc ComparisonThis here blog... Link to comment Share on other sites More sharing options...
KevinGood Posted June 13, 2007 Author Share Posted June 13, 2007 Kevin,Sorry if I caused any confusion. Let me ask an additional clarifying question: What specific action are you needing AutoIt to do when the value changes? Are you needing it to open something? close something? click something? run something? I ask because it may be possible to have that action live inside the workbook as well. Let's say that in the example you provided, if the stock price goes under a certain amount then you want to "Buy". Let's imagine that the "Buy" action would involve activating an application, navigating to a buy screen and clicking the "BUY" button. In that scenario, it may be easier to have the workbook trigger the "Buy" action rather than have a script running and waiting for the trigger. The "Buy" action could live inside the workbook ( using a reference to AutoItX3 like in my first example ) OR it could be an AutoItScript that gets run from Excel. So what specific action are you looking for?I actually need it to perform several actions. I need autoit to grab some text off of a window when the value changes (that I cannot stream into excel), and perform several actions similar to your "buy" action. Clicks, keystrokes, and text sent to a window. I need to perform all these actions each time the value changes, so looping capability is also required. I have several apps that I wish to write in the near future aswell that require the capability to pass data from excel to autoit, either as a trigger each time it changes, or as a loop that constantly checks to see if the value has changed since the last check. (The first is prefered as a looping check greatly deminishes speed.) I appreciate the help, and especially the timliness, sorry I'm not making it too easy Link to comment Share on other sites More sharing options...
zfisherdrums Posted June 13, 2007 Share Posted June 13, 2007 (edited) Kevin,I'm attaching an example of an Excel spreadsheet driving a window and exporting data when a change event occurs in the workbook. Test_for_Kevin_Good.zipUnzip the attached Zip file.Open "Test for Kevin Good.au3" and run it. Verify that you see a window appear.Open "Test.xls".Change the value in cell B1 and press enter. Verify the following events occured: - "Label1" changes to your input value - List box contains a message indicating the time when the button was clickedIf you want to view the code-behind, press Alt-F11 in Excel to view the code for "Module1" and "ThisWorkbook"In regards to your need to have it looped, Excel reacts to the change event without the need to "loop" per se. Hope this gives you an idea of what I referred to earlier.Edit: I just noticed that some changes won't fire the change event. In my example, if B1 contains a cell reference to another cell, changes in the target cell will not fire the event to drive the window. I'll need to do some more research. Edited June 13, 2007 by zfisherdrums Identify .NET controls by their design time namesLazyReader© could have read all this for you. Unit Testing for AutoItFolder WatcherWord Doc ComparisonThis here blog... Link to comment Share on other sites More sharing options...
KevinGood Posted June 13, 2007 Author Share Posted June 13, 2007 Kevin, I'm attaching an example of an Excel spreadsheet driving a window and exporting data when a change event occurs in the workbook. Test_for_Kevin_Good.zip Unzip the attached Zip file. Open "Test for Kevin Good.au3" and run it. Verify that you see a window appear. Open "Test.xls". Change the value in cell B1 and press enter. Verify the following events occured: - "Label1" changes to your input value - List box contains a message indicating the time when the button was clicked If you want to view the code-behind, press Alt-F11 in Excel to view the code for "Module1" and "ThisWorkbook" In regards to your need to have it looped, Excel reacts to the change event without the need to "loop" per se. Hope this gives you an idea of what I referred to earlier. Edit: I just noticed that some changes won't fire the change event. In my example, if B1 contains a cell reference to another cell, changes in the target cell will not fire the event to drive the window. I'll need to do some more research. That is some good stuff, I didn't get a chance to test it on the live feed yet(might be trouble considering your note), but I was wondering if there was a way to update a variable within autoit rather than sending that ControlSetText command. OR, some way within autoit to read the value that is currently displayed in the $label1 feild. Link to comment Share on other sites More sharing options...
zfisherdrums Posted June 13, 2007 Share Posted June 13, 2007 OR, some way within autoit to read the value that is currently displayed in the $label1 feild. I'm assuming that something like this in "Test for Kevin Good.au3" is what you're referring to? $value = ControlGetText( "Test for Kevin Good", "", "Static1" ) Identify .NET controls by their design time namesLazyReader© could have read all this for you. Unit Testing for AutoItFolder WatcherWord Doc ComparisonThis here blog... Link to comment Share on other sites More sharing options...
KevinGood Posted June 14, 2007 Author Share Posted June 14, 2007 I'm assuming that something like this in "Test for Kevin Good.au3" is what you're referring to? $value = ControlGetText( "Test for Kevin Good", "", "Static1" ) Yeah... I feel dumb for not thinking of that.... I'm fairly new to the language. I was trying to somehow extract it from the label variable rather than just taking it from the screen... Link to comment Share on other sites More sharing options...
KevinGood Posted June 14, 2007 Author Share Posted June 14, 2007 Just incase anyone else looks at this and wonders how to link an action to a cell that holds a reference rather than just a number... Private Sub Worksheet_Calculate() If Range("B1").Value > 1 Then Call test(Range("B1").Value) End If End Sub This ended up as my final code (for the test) in the excel worksheet macro area. Worksheet_calculate() checks a reference cell for changes. The IF checks if the value is above 1 (just something I needed for my real app), and calling test sends the value to a window made in autoit, which autoit then reads to get the value. Using ControlGetText() Thanks for the help, I'm sure ill be back. Link to comment Share on other sites More sharing options...
zfisherdrums Posted June 15, 2007 Share Posted June 15, 2007 (edited) Private Sub Worksheet_Calculate() If Range("B1").Value > 1 Then Call test(Range("B1").Value) End IfEnd SubOh shoot. Forgot about THAT one. It's always the simplest, ain't it?So, does it respond to updates via DDE like you need? Edited June 15, 2007 by zfisherdrums Identify .NET controls by their design time namesLazyReader© could have read all this for you. Unit Testing for AutoItFolder WatcherWord Doc ComparisonThis here blog... Link to comment Share on other sites More sharing options...
KevinGood Posted June 15, 2007 Author Share Posted June 15, 2007 Oh shoot. Forgot about THAT one. It's always the simplest, ain't it?So, does it respond to updates via DDE like you need?Yeah, it does respond to the DDE like I need.... BUT, unlike Change() which has a target field built in to grab the value that changed, I am having issues, when checking a range of changing values, finding which cell actually triggered the event out of the column I am monitering. I need to grab several values out of the row that contains the cell that did change, any ideas? A B C D E F G1 1 1 1 1 ref2 2 2 2 2 ref3 3 3 3 3 ref I am monitering column G, if G2 triggers the event, I need to grab A2, B2, for example. Link to comment Share on other sites More sharing options...
zfisherdrums Posted June 16, 2007 Share Posted June 16, 2007 (edited) If you ever see Chip Pearson, hug him for this solution. Don't let others see you hug him. Place this code in the Worksheet code-behind you are monitoring. Private Sub Worksheet_Calculate() ' Static will persist values between calls to calculate event Static OldVal As Variant If Range("G2").Value <> OldVal Then MsgBox Range("A1").Value & vbCrLf & Range("A2").Value OldVal = Range("G2").Value End If End Sub Edited June 16, 2007 by zfisherdrums Identify .NET controls by their design time namesLazyReader© could have read all this for you. Unit Testing for AutoItFolder WatcherWord Doc ComparisonThis here blog... Link to comment Share on other sites More sharing options...
Recommended Posts
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 accountSign in
Already have an account? Sign in here.
Sign In Now