Excel Functions

[anusha]

Hi I have jus started using auto-it . Please correct me if I'm wrong.

I need to read data from an input in text box and search in excel file and return value in next column of matched cell on GUI.

I have written below code but i cannot use variable which has data stored. it works only when search string is hard coded.

Please help out.

 

Example()

Func Example()

Local $GuiMain = GUICreate("EXCEL TEST", 399, 180) ;creates main GUI
;~ Local $idOK = GUISetOnEvent($GUI_EVENT_CLOSE, "Close")

Local $iWidthCell = 70

Local $idLabel = GUICtrlCreateLabel("PART NUMBER", 10, 30, $iWidthCell,50)
Local $RUN_1 = GUICtrlCreateButton("OK", 70, 70, 85, 25)

Local $Input_1 = GUICtrlCreateInput("PART NUMBER", 100, 20, 120, 20)
Local $sMenutext = GUICtrlRead($Input_1, 1)
GUISetState(@SW_SHOW, $GuiMain)

    While 1
    $MSG = GUIGetMsg()
    Select
        Case $MSG = $GUI_EVENT_CLOSE
            Exit
        Case $MSG = $RUN_1
            Local $oAppl = _Excel_Open()

Local $sFilePath1 = "D:\Anu_WorkFolder\Components.xlsx"
Local $oWorkbook = _Excel_BookOpen($oAppl, $sFilePath1, Default, Default, True)
Local $aResult = _Excel_RangeFind($oWorkbook, $sMenutext , Default, Default, $xlWhole)

[water]

Welcome to AutoIt and the forum!

You have to use GUICtrlRead in a "GUI-loop" after GUIGetMsg. Example:

#include <GUIConstantsEx.au3>
#include <MsgBoxConstants.au3>
#include <WindowsConstants.au3>

Example()

Func Example()
    GUICreate("Example", 320, 120, @DesktopWidth / 2 - 160, @DesktopHeight / 2 - 45, -1)
    $idFile = GUICtrlCreateInput("", 10, 35, 300, 20)
    Local $idBtn = GUICtrlCreateButton("Ok", 40, 75, 60, 20)
    GUISetState(@SW_SHOW)
    ; Loop until the user exits.
    While 1
        Switch GUIGetMsg()
            Case $GUI_EVENT_CLOSE
                ExitLoop
            Case $idBtn
                $sInput = GUICtrlRead($idFile)
                MsgBox(0, "", "Data entered: " & @CRLF & $sInput)
        EndSwitch
    WEnd

EndFunc   ;==>Example

 

[anusha]

Thanks Water.

This is only for reading a control like text box.

How about searching this data in excel file.

Edited December 14, 2017 by anusha

[water]

Something like this:

expandcollapse popup

#include <GUIConstantsEx.au3>
#include <Excel.au3>

$oExcel = _Excel_Open()
Example()

Func Example()
    GUICreate("Example", 320, 120, @DesktopWidth / 2 - 160, @DesktopHeight / 2 - 45, -1)
    $idFile = GUICtrlCreateInput("", 10, 35, 300, 20)
    Local $idBtn = GUICtrlCreateButton("Ok", 40, 75, 60, 20)
    GUISetState(@SW_SHOW)
    ; Loop until the user exits.
    While 1
        Switch GUIGetMsg()
            Case $GUI_EVENT_CLOSE
                _Excel_Close($oExcel)
                ExitLoop
            Case $idBtn
                $sInput = GUICtrlRead($idFile)
                If StringStripWS($sInput, $STR_STRIPLEADING + $STR_STRIPTRAILING) <> "" Then _ExcelSearch($sInput)
        EndSwitch
    WEnd

EndFunc   ;==>Example

Func _ExcelSearch($sSearchString)

    Local $sFilePath = "C:\temp\test.xlsx" ; "D:\Anu_WorkFolder\Components.xlsx"
    Local $oWorkbook = _Excel_BookOpen($oExcel, $sFilePath, Default, Default, True)
    Local $aResult = _Excel_RangeFind($oWorkbook, $sSearchString, Default, Default, $xlWhole)
    For $i = 0 To UBound($aResult, 1) - 1
        ConsoleWrite("Address: " & $aResult[$i][2] & ", value: " & $aResult[$i][3] & @CRLF) ; Cell that fits the search string
        ConsoleWrite("  " & $oWorkbook.Activesheet.Range($aResult[$i][2]).Offset(0, 1).value & @CRLF) ; Value of the cell right to the found cell
    Next
    _Excel_BookClose($oWorkbook)

EndFunc   ;==>_ExcelSearch

 

[JLogan3o13]

Moved to the appropriate forum.

[anusha]

On 12/14/2017 at 5:45 PM, water said:

Something like this:

expandcollapse popup

#include <GUIConstantsEx.au3>
#include <Excel.au3>

$oExcel = _Excel_Open()
Example()

Func Example()
    GUICreate("Example", 320, 120, @DesktopWidth / 2 - 160, @DesktopHeight / 2 - 45, -1)
    $idFile = GUICtrlCreateInput("", 10, 35, 300, 20)
    Local $idBtn = GUICtrlCreateButton("Ok", 40, 75, 60, 20)
    GUISetState(@SW_SHOW)
    ; Loop until the user exits.
    While 1
        Switch GUIGetMsg()
            Case $GUI_EVENT_CLOSE
                _Excel_Close($oExcel)
                ExitLoop
            Case $idBtn
                $sInput = GUICtrlRead($idFile)
                If StringStripWS($sInput, $STR_STRIPLEADING + $STR_STRIPTRAILING) <> "" Then _ExcelSearch($sInput)
        EndSwitch
    WEnd

EndFunc   ;==>Example

Func _ExcelSearch($sSearchString)

    Local $sFilePath = "C:\temp\test.xlsx" ; "D:\Anu_WorkFolder\Components.xlsx"
    Local $oWorkbook = _Excel_BookOpen($oExcel, $sFilePath, Default, Default, True)
    Local $aResult = _Excel_RangeFind($oWorkbook, $sSearchString, Default, Default, $xlWhole)
    For $i = 0 To UBound($aResult, 1) - 1
        ConsoleWrite("Address: " & $aResult[$i][2] & ", value: " & $aResult[$i][3] & @CRLF) ; Cell that fits the search string
        ConsoleWrite("  " & $oWorkbook.Activesheet.Range($aResult[$i][2]).Offset(0, 1).value & @CRLF) ; Value of the cell right to the found cell
    Next
    _Excel_BookClose($oWorkbook)

EndFunc   ;==>_ExcelSearch

 

Excel instance is not closing when i click close button of GUI. 

Excel instance is still open after script is finished even when _Excel_Close($oExcel) is used.

[water]

Works as expected here (Office 2016).
Are you sure Excel istn't already running when you start your script?

[anusha]

Excel is running. but excel instance is not closing even GUI is closed

[water]

To close an Excel instance that wasn't created by _Excel_Open you need to call _Excel_Close with parameter $bForceClose set to True:

_Excel_Close($oExcel, False, True) ; Close Excel even when it was running at _Excel_Open time and do not saved opened workbooks

 

[anusha]

thank you. as am automating excel, moving forward i am having query is autoit suitable for this type of automation.

Mainly my purpose is i will search for a variable from excel and reading its value and edit value based on consumption and all this will be protect only a single user can do this.

Further few more improvement would be done.

 

Is autoit preferable for such automations

[JLogan3o13]

@anusha obviously on an AutoIt forum you're going to be told that AutoIt is the tool to use, just as you would be told to use VBScript on a VBS forum - it's just common sense. That said, water has done great things with creating a library to automate Excel; many of us on this forum use it heavily on a daily basis. As to what it can and can't do, that just comes down to you learning how to use the UDF.

[water]

Excel (and other MS products) provide a object model for automation which can be used by AutoIt.
The Excel UDF only uses a subset ob this object model. The main goal of an UDF is to make automation as easy as possible, to have proper error handling and to cover those objects, methods and properties of a MS product useful for the vast majority of users.

Searching, reading and changing Excel should easily be covered by the Excel UDF as it is
 

 

 

[Earthshine]

I don't know, I would have told him to use VBA or VBScript.. lol. use the tool best suited for the job. If the AutoIt UDFs don't cover it, you have to look to other means. But I bet Water has you covered.

Edited January 17, 2018 by Earthshine

[anusha]

Thanks alot. 

Water can you please tell me is there a way to authenticate user to proceed to run script further and writing text to adjacent cell where value is read. 

[water]

What exactly do you mean by authenticate?