"ControlGetText" Help with Excel

[redrum]

Hello,

I cannot seem to get the ControlGetText to read from the Fx line at the top of an Excel sheet. Have searched both present and archived areas on the Forum and did not find anything that helps me on this.

I know I have the Window and Control elements correct because I can write text to the Fx window, Yet, the ControlGetText command with the same Window and Control elements does not work - returns an error and null string as the text.

Any help on this would be greatly appreciated.

Regards,

redrum

TestExcel_fq.au3

[water]

Why do you want to read from/write to the Fx line?

There is an Excel UDF available that should let you do what you need.

[jdelaney]

Rather than read the value of the cell, he probably wants the function that produced it...not too familiar, but there is probably a way in the UDF to change the format of the cell to be function instead of value, so you can collect info from the cell

googled it...VBA:

Function FTEXT(f As Range)

If f.HasFormula Then

FTEXT = f.Formula

Else: FTEXT = f

End If

End Function

Edited August 24, 2012 by jdelaney

[BrewManNH]

I modified this from the _ExcelWriteFormula function to read the formula used in the B1 cell and return it. There's a ConsoleWrite of the returned value(s).

; ***************************************************************
; Example 1 - Write to a Cell using a Loop, after opening a workbook and returning its object identifier.  Then enters a Forumula.
; *****************************************************************

#include <Excel.au3>

Local $oExcel = _ExcelBookNew() ;Create new book, make it visible

For $i = 0 To 20 ;Loop
    _ExcelWriteCell($oExcel, $i, $i, 1) ;Write to the Cell
Next

_ExcelWriteFormula($oExcel, "=Average(R1C1:R20C1)", 1, 2) ;Uses R1C1 referencing
ConsoleWrite(_ExcelReadFormula($oExcel, 1, 2) & @CRLF) ;Uses R1C1 referencing
ConsoleWrite(_ExcelReadFormula($oExcel, "B1") & @CRLF)

MsgBox(0, "Exiting", "Press OK to Save File and Exit")
_ExcelBookSaveAs($oExcel, @TempDir & "Temp.xls", "xls", 0, 1) ; Now we save it into the temp directory; overwrite existing file if necessary
_ExcelBookClose($oExcel) ; And finally we close out
Func _ExcelReadFormula($oExcel, $sRangeOrRow, $iColumn = 1)
    If Not IsObj($oExcel) Then Return SetError(1, 0, 0)
    If Not StringRegExp($sRangeOrRow, "[A-Z,a-z]", 0) Then
        If $sRangeOrRow < 1 Then Return SetError(2, 0, 0)
        If $iColumn < 1 Then Return SetError(2, 1, 0)
        Return $oExcel.Activesheet.Cells($sRangeOrRow, $iColumn).FormulaR1C1
    Else
        Return $oExcel.Activesheet.Range($sRangeOrRow).Formula
    EndIf
EndFunc   ;==>_ExcelWriteFormula

EDIT: There was an extra consolewrite in the function that I was using for debugging, it's been removed.

Edited August 24, 2012 by BrewManNH

[redrum]

Thanks for suggestions.

My goal is to read a specific cell from an Excel sheet repetitively, in a loop. The loop is exited when a specific value is read, or possibly some other way.

I initially was using the UDF to read the cell, in the script loop, and all was working well, except for one thing: Whenever I typed values into the Excel sheet (typing into cells other than the cell that is being read by the script loop), the Script fails and exits. In addition, while the script is running I would like to select other sheets in the Excel workbook temporarily, but selecting another sheet that is a chart, also causes the script to exit.

The attempts to read the Fx line was an alternative due to the above described primary issue that I have.

BOTTOM LINE: I would like to repetitively read a specific single cell from a sheet in an Excel workbook, and I would like to read it for a specific sheet, whether that sheet is active or not. Not sure if this is possible, or how to accomplish.

Looking for any suggestions or some direction to pursue to achieve this.

[jdelaney]

you can modify the _excelreadcell to specify a specific sheet...currently, it's set to ActiveSheet...google how to specify sheet:

Func _ExcelReadCell($oExcel, $sRangeOrRow, $iColumn = 1)
If Not IsObj($oExcel) Then Return SetError(1, 0, 0)
If Not StringRegExp($sRangeOrRow, "[A-Z,a-z]", 0) Then
If $sRangeOrRow < 1 Then Return SetError(2, 0, 0)
If $iColumn < 1 Then Return SetError(2, 1, 0)
Return $oExcel.Activesheet.Cells($sRangeOrRow, $iColumn).Value
Else
Return $oExcel.Activesheet.Range($sRangeOrRow).Value
EndIf
EndFunc ;==>_ExcelReadCell

something like:

Sub ReadCell()

msgbox Sheets("Sheet2").Range("A1").Value

End Sub

Edited August 24, 2012 by jdelaney

[redrum]

Thanks!, it looks like this is exactly the solution I needed - I didn't think of trying to modify the UDF to read the specific sheet I want.

Going to try this, but expecting good results.

redrum

[redrum]

UPDATE:

This worked perfectly and solved all my issues with reading the Excel cell.

Your help is greatly appreciated - I would not have thought of that solution, didn't even know where to find the code/details of the UDF's

Thanks again,

redrum