Sign in to follow this  
Followers 0
redrum

"ControlGetText" Help with Excel

8 posts in this topic

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

Share this post


Link to post
Share on other sites



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.


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites

#3 ·  Posted (edited)

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 by jdelaney

IEbyXPATH-Grab IE DOM objects by XPATH IEscriptRecord-Makings of an IE script recorder ExcelFromXML-Create Excel docs without excel installed GetAllWindowControls-Output all control data on a given window.

Share this post


Link to post
Share on other sites

#4 ·  Posted (edited)

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 by BrewManNH
1 person likes this

If I posted any code, assume that code was written using the latest release version unless stated otherwise. Also, if it doesn't work on XP I can't help with that because I don't have access to XP, and I'm not going to.
Give a programmer the correct code and he can do his work for a day. Teach a programmer to debug and he can do his work for a lifetime - by Chirag Gude
How to ask questions the smart way!

I hereby grant any person the right to use any code I post, that I am the original author of, on the autoitscript.com forums, unless I've specifically stated otherwise in the code or the thread post. If you do use my code all I ask, as a courtesy, is to make note of where you got it from.

Back up and restore Windows user files _Array.au3 - Modified array functions that include support for 2D arrays.  -  ColorChooser - An add-on for SciTE that pops up a color dialog so you can select and paste a color code into a script.  -  Customizable Splashscreen GUI w/Progress Bar - Create a custom "splash screen" GUI with a progress bar and custom label.  -  _FileGetProperty - Retrieve the properties of a file  -  SciTE Toolbar - A toolbar demo for use with the SciTE editor  -  GUIRegisterMsg demo - Demo script to show how to use the Windows messages to interact with controls and your GUI.  -   Latin Square password generator

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

#6 ·  Posted (edited)

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 by jdelaney

IEbyXPATH-Grab IE DOM objects by XPATH IEscriptRecord-Makings of an IE script recorder ExcelFromXML-Create Excel docs without excel installed GetAllWindowControls-Output all control data on a given window.

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

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

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