Reading from excel

[KevinGood]

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.

[/dev/null]

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?

Cheers

Kurt

[KevinGood]

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.

[zfisherdrums]

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?

[/dev/null]

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.

I meant excel sheets to test with....

[KevinGood]

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

[zfisherdrums]

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?

[KevinGood]

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

[zfisherdrums]

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.

Edited June 13, 2007 by zfisherdrums

[KevinGood]

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.

[zfisherdrums]

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" )

[KevinGood]

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...

[KevinGood]

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.

[zfisherdrums]

Private Sub Worksheet_Calculate()

If Range("B1").Value > 1 Then

Call test(Range("B1").Value)

End If

End Sub

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?

Edited June 15, 2007 by zfisherdrums

[KevinGood]

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 G

1 1 1 1 1 ref

2 2 2 2 2 ref

3 3 3 3 3 ref

I am monitering column G, if G2 triggers the event, I need to grab A2, B2, for example.

[zfisherdrums]

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