AutoIt in Excel

[Kiti]

Here's my script:

#include <ExcelCOM_UDF.au3> 
Local $oExcel = _ExcelBookNew(1)
_ExcelWriteCell($oExcel, "1", "B1")
$Cell = Func _ExcelFindInRange($oExcel, $sFindWhat = "1" , $sRangeOrRowStart = 1, $iColStart = 1, $iRowEnd = 2, $iColEnd = 2, $iDataType = 0, $iWholeOrPart = 2, $fMatchCase = False, $fMatchFormat = "")
_ExcelWriteCell($oExcel, "Well Done!", $Cell[0], $Cell [1]-1)

I've used locodarwin's UDF. The script should look for the "1" number in the first grid of 2*2 cells (A1:B2), and after it finds it on B1, it should write "Well Done!" one cell to the left, on A1 (I've used R1C1 system to specify the one cell to the left). But this doesn't happen. I only get "1" on B1. Can anyone spot the problem? Thank you!

[Kerros]

First it appears that you copied the function directly from the UDF. That's going to include all the names from the function. You don't need to do this, just include what you want them to be.

Second thing in your write you are trying to access a 2-dimension array with with the syntax of a 1 dimension array.

Try this code.

#include <_ExcelCOM_UDF.au3>
Local $oExcel = _ExcelBookNew(1)
_ExcelWriteCell($oExcel, "1", "B1")
$Cell = _ExcelFindInRange($oExcel,1,1,1,5,5)
If $Cell = 0  Or $Cell = '' Then 
    MsgBox(0,'Error','ExcelCOM_UDF returned and error of: '&@error)
Else
    _ExcelWriteCell($oExcel, "Well Done!", $cell[1][2]+1,1)
EndIf
Sleep(5000)
_ExcelBookClose($oExcel)

Edited June 11, 2008 by Kerros

[Kiti]

Yeey! It worked! Thank you very much!

[Kiti]

Now, instead of the program writing "1" , I want to write it myself, and then click on the "Validate" Button of the GUI, to display the "Well Done!" message next to the spot where I've wrote 1. But when I click om "Validate" nothing happens.

expandcollapse popup

#include <GUIConstantsEx.au3>
#include <ExcelCOM_UDF.au3> 

Opt('MustDeclareVars', 1)

_Main()

Func _Main()
    Local $ValidateID, $ExitID, $msg

    GUICreate("Custom Msgbox", 210, 80)

    GUICtrlCreateLabel("Please click a button!", 10, 10)
    $ValidateID = GUICtrlCreateButton("Validate!", 25, 40, 100, 30)

    $ExitID = GUICtrlCreateButton("Exit", 150, 50, 50, 20)

    GUISetState(); display the GUI

Local $oExcel = _ExcelBookNew(1)

Do
        $msg = GUIGetMsg()

        Select
            Case $msg = $ValidateID
$Cell = _ExcelFindInRange($oExcel, "1", 1, 1, 2, 2, 0, 2, False, False) 
If $Cell[0][0] > 0 Then 
     _ExcelWriteCell($oExcel, "Well Done!", $Cell[1][2] -1, $Cell[1][3]) 
Else 
     _ExcelWriteCell($oExcel, "Not found!", 4, 4) 
EndIf           
            Case $msg = $ExitID
                MsgBox(0, "You clicked on", "Bye-Bye!")
            Case $msg = $GUI_EVENT_CLOSE
                MsgBox(0, "You clicked on", "Bye-Bye!")
        EndSelect
    Until $msg = $GUI_EVENT_CLOSE Or $msg = $ExitID
EndFunc;==>_Main

Also, how can I make the GUI to stay always on top?

Edited June 11, 2008 by Kiti

[cartman380]

This isn't tested but it should work for you.

EDIT: added the topmost exStyle to the GUI

#include <GUIConstantsEx.au3>
#include <ExcelCOM_UDF.au3> 

Opt('MustDeclareVars', 1)

GUICreate("Custom Msgbox", 210, 80, "", "", "", $WS_EX_TOPMOST)
GUICtrlCreateLabel("Please click a button!", 10, 10)
$YesID = GUICtrlCreateButton("Validate!", 25, 40, 100, 30)
$ExitID = GUICtrlCreateButton("Exit", 150, 50, 50, 20)
GUISetState(); display the GUI

Local $YesID, $ExitID, $msg
Local $oExcel = _ExcelBookNew(1)
    
While 1
    $msg = GUIGetMsg()
    Switch $msg
        Case $YesID         
            $Cell = _ExcelFindInRange($oExcel, "1", 1, 1, 2, 2, 0, 2, False, False) 
            If $Cell[0][0] > 0 Then 
                 _ExcelWriteCell($oExcel, "Well Done!", $Cell[1][2] -1, $Cell[1][3]) 
            Else 
                 _ExcelWriteCell($oExcel, "Not found!", 4, 4) 
            EndIf
        Case $ExitID
            MsgBox(0, "You clicked on", "Bye-Bye!")
        Case $msg = $GUI_EVENT_CLOSE
            MsgBox(0, "You clicked on", "Bye-Bye!")
    EndSwitch
    Sleep(100)
WEnd

Edited June 11, 2008 by cartman380

[Kiti]

This was intended to be a joke? Because when I run it displayed me endless messagess saying "Bye-Bye!"

[cartman380]

Sorry that was my fault. I forgot to remove one thing from your code when I modified it I changed all the others from Case $msg = $something except the last one that's what the problem was:

#include <GUIConstantsEx.au3>
#include <ExcelCOM_UDF.au3> 

Opt('MustDeclareVars', 1)

GUICreate("Custom Msgbox", 210, 80, "", "", "", $WS_EX_TOPMOST)
GUICtrlCreateLabel("Please click a button!", 10, 10)
$YesID = GUICtrlCreateButton("Validate!", 25, 40, 100, 30)
$ExitID = GUICtrlCreateButton("Exit", 150, 50, 50, 20)
GUISetState(); display the GUI

Local $YesID, $ExitID, $msg
Local $oExcel = _ExcelBookNew(1)
    
While 1
    $msg = GUIGetMsg()
    Switch $msg
        Case $YesID         
            $Cell = _ExcelFindInRange($oExcel, "1", 1, 1, 2, 2, 0, 2, False, False) 
            If $Cell[0][0] > 0 Then 
                 _ExcelWriteCell($oExcel, "Well Done!", $Cell[1][2] -1, $Cell[1][3]) 
            Else 
                 _ExcelWriteCell($oExcel, "Not found!", 4, 4) 
            EndIf
        Case $ExitID
            MsgBox(0, "You clicked on", "Bye-Bye!")
        Case $GUI_EVENT_CLOSE
            MsgBox(0, "You clicked on", "Bye-Bye!")
    EndSwitch
    Sleep(100)
WEnd

Edited June 11, 2008 by cartman380

[Kiti]

I had to replace GUICreate("Custom Msgbox", 210, 80, "", "", "", $WS_EX_TOPMOST) with GUICreate("Custom Msgbox", 210, 80), otherwise it was telling it's an error. I've also changed a little the order of a few lines, because of variables used before declaration. I came up to this :

#include <GUIConstantsEx.au3>
#include <ExcelCOM_UDF.au3>

Opt('MustDeclareVars', 1)
Local $YesID, $ExitID, $msg
Local $oExcel = _ExcelBookNew(1)
GUICreate("Custom Msgbox", 210, 80)
GUICtrlCreateLabel("Please click a button!", 10, 10)
$YesID = GUICtrlCreateButton("Validate!", 25, 40, 100, 30)
$ExitID = GUICtrlCreateButton("Exit", 150, 50, 50, 20)
GUISetState(); display the GUI

While 1
    $msg = GUIGetMsg()
    Switch $msg
        Case $YesID
            $Cell = _ExcelFindInRange($oExcel, "1", 1, 1, 2, 2, 0, 2, False, False)
            If $Cell[0][0] > 0 Then
                _ExcelWriteCell($oExcel, "Well Done!", $Cell[1][2] - 1, $Cell[1][3])
            Else
                _ExcelWriteCell($oExcel, "Not found!", 4, 4)
            EndIf
        Case $ExitID
            MsgBox(0, "You clicked on", "Bye-Bye!")
        Case $GUI_EVENT_CLOSE
            MsgBox(0, "You clicked on", "Bye-Bye!")
    EndSwitch
    Sleep(100)
WEnd

But it's still not working. I write "1" on B1, I click Validate, and nothing happens.

[Kiti]

How can I make it to always stay on top, and how can I make it not to close after I press Validate, so I can press it several times?

[Kerros]

This should be the working code you are looking for. Still does not stay on top though.

#include <ExcelCOM_UDF.au3>
#include <GUIConstantsEx.au3>

Local $YesID, $ExitID, $msg
Local $oExcel = _ExcelBookNew(1)
$gui = GUICreate("Custom Msgbox", 210, 80)

GUICtrlCreateLabel("Please click a button!", 10, 10)
$YesID = GUICtrlCreateButton("Validate!", 25, 40, 100, 30)
$ExitID = GUICtrlCreateButton("Exit", 150, 50, 50, 20)
GUISetState(@SW_SHOW); display the GUI
;~ GUISetState(

While 1
    $msg = GUIGetMsg()
    Switch $msg
        Case $YesID
            $Cell = _ExcelFindInRange($oExcel, '1', 1, 1, 2, 2, 0, 2, False, False)
            If $Cell[0][0] > 0 Then
                _ExcelWriteCell($oExcel, "Well Done!", $Cell[1][2] - 1, $Cell[1][3])
            Else
                _ExcelWriteCell($oExcel, "Not found!", 4, 4)
            EndIf
        Case $ExitID
            MsgBox(0, "You clicked on", "Bye-Bye!")
            Exit
        Case $GUI_EVENT_CLOSE
            MsgBox(0, "You clicked on", "Bye-Bye!")

            Exit
    EndSwitch
    Sleep(100)
WEnd

            _ExcelBookClose($oExcel); cleanup

[Kiti]

Hurray!!! Thanks alot, Kerros.

Now, I've got only one last major (?) problem. I want it to tell "Well Done!" 3 times if I have the number 1 three times in the searching area. Can this be possible ? Can it write well done as many times as 1 appears, but with only one press of the validate button?

[cartman380]

This should be the working code you are looking for. Still does not stay on top though.

#include <ExcelCOM_UDF.au3>
#include <GUIConstantsEx.au3>

Local $YesID, $ExitID, $msg
Local $oExcel = _ExcelBookNew(1)
$gui = GUICreate("Custom Msgbox", 210, 80)

GUICtrlCreateLabel("Please click a button!", 10, 10)
$YesID = GUICtrlCreateButton("Validate!", 25, 40, 100, 30)
$ExitID = GUICtrlCreateButton("Exit", 150, 50, 50, 20)
GUISetState(@SW_SHOW); display the GUI
;~ GUISetState(

While 1
    $msg = GUIGetMsg()
    Switch $msg
        Case $YesID
            $Cell = _ExcelFindInRange($oExcel, '1', 1, 1, 2, 2, 0, 2, False, False)
            If $Cell[0][0] > 0 Then
                _ExcelWriteCell($oExcel, "Well Done!", $Cell[1][2] - 1, $Cell[1][3])
            Else
                _ExcelWriteCell($oExcel, "Not found!", 4, 4)
            EndIf
        Case $ExitID
            MsgBox(0, "You clicked on", "Bye-Bye!")
            Exit
        Case $GUI_EVENT_CLOSE
            MsgBox(0, "You clicked on", "Bye-Bye!")

            Exit
    EndSwitch
    Sleep(100)
WEnd

            _ExcelBookClose($oExcel); cleanup

You just have to loop for however many times Cell[0][0] is

expandcollapse popup

#include <ExcelCOM_UDF.au3>
#include <GUIConstantsEx.au3>

Local $YesID, $ExitID, $msg
Local $oExcel = _ExcelBookNew(1)
$gui = GUICreate("Custom Msgbox", 210, 80)

GUICtrlCreateLabel("Please click a button!", 10, 10)
$YesID = GUICtrlCreateButton("Validate!", 25, 40, 100, 30)
$ExitID = GUICtrlCreateButton("Exit", 150, 50, 50, 20)
GUISetState(@SW_SHOW); display the GUI
;~ GUISetState(

While 1
    $msg = GUIGetMsg()
    Switch $msg
        Case $YesID
            $Cell = _ExcelFindInRange($oExcel, '1', 1, 1, 2, 2, 0, 2, False, False)
            If $Cell[0][0] > 0 Then
                For $i = 1 to $Cell[0][0]
                    _ExcelWriteCell($oExcel, "Well Done!", $Cell[1][2] - 1, $Cell[1][3])
                Next
            Else
                _ExcelWriteCell($oExcel, "Not found!", 4, 4)
            EndIf
        Case $ExitID
            MsgBox(0, "You clicked on", "Bye-Bye!")
            Exit
        Case $GUI_EVENT_CLOSE
            MsgBox(0, "You clicked on", "Bye-Bye!")

            Exit
    EndSwitch
    Sleep(100)
WEnd

            _ExcelBookClose($oExcel); cleanup

[Kiti]

I've spotted 2 bugs:(

The former, if you write "1" on a cell, and you don't select another cell, the script terminates.

And the latter (which can easily be solved, but I don't really understand arrays 100% to be able to solve it), when I put "1" on a different spot of the B column, the "Well Done!" is displayed in other (random?) cells. I want it to be displayed strictly one cell left from the cell where "1" is found.

$Cell = _ExcelFindInRange($oExcel, '1', 1, 2, 5, 2, 0, 2, False, False)
_ExcelWriteCell($oExcel, "Well Done!", $Cell[1][2]-1, $Cell[1][3])

Edited June 11, 2008 by Kiti

[Kiti]

I've spotted 2 bugs:(

The former, if you write "1" on a cell, and you don't select another cell, the script terminates.

And the latter (which can easily be solved, but I don't really understand arrays 100% to be able to solve it), when I put "1" on a different spot of the B column, the "Well Done!" is displayed in other (random?) cells. I want it to be displayed strictly one cell left from the cell where "1" is found.

$Cell = _ExcelFindInRange($oExcel, '1', 1, 2, 5, 2, 0, 2, False, False)
_ExcelWriteCell($oExcel, "Well Done!", $Cell[1][2]-1, $Cell[1][3])

Edit: The For loop doesn't seem to work for me... Probably because it just writes the same message in the same spot for as many times the "1" appears. It should be a loop which also has a "next" for the cells found, so it won't do the same thing multiple times. Edited June 11, 2008 by Kiti

[cartman380]

heh doing too many things at once atm busy coding something for work and just quick put that together. Not sure about the not selecting another cell problem but I can do the Array Fix.

expandcollapse popup

#include <ExcelCOM_UDF.au3>
#include <GUIConstantsEx.au3>

Local $YesID, $ExitID, $msg
Local $oExcel = _ExcelBookNew(1)
$gui = GUICreate("Custom Msgbox", 210, 80)

GUICtrlCreateLabel("Please click a button!", 10, 10)
$YesID = GUICtrlCreateButton("Validate!", 25, 40, 100, 30)
$ExitID = GUICtrlCreateButton("Exit", 150, 50, 50, 20)
GUISetState(@SW_SHOW); display the GUI
;~ GUISetState(

While 1
    $msg = GUIGetMsg()
    Switch $msg
        Case $YesID
            $Cell = _ExcelFindInRange($oExcel, '1', 1, 1, 2, 2, 0, 2, False, False)
            If $Cell[0][0] > 0 Then
                For $i = 1 to $Cell[0][0]
                    _ExcelWriteCell($oExcel, "Well Done!", $Cell[$i][2] - 1, $Cell[$i][3])
                Next
            Else
                _ExcelWriteCell($oExcel, "Not found!", 4, 4)
            EndIf
        Case $ExitID
            MsgBox(0, "You clicked on", "Bye-Bye!")
            Exit
        Case $GUI_EVENT_CLOSE
            MsgBox(0, "You clicked on", "Bye-Bye!")

            Exit
    EndSwitch
    Sleep(100)
WEnd

            _ExcelBookClose($oExcel); cleanup

[Kiti]

heh doing too many things at once atm busy coding something for work and just quick put that together. Not sure about the not selecting another cell problem but I can do the Array Fix.

Ok, I've fixed it. Now it works so smooth and perfect I'm very proud of it.

#include <ExcelCOM_UDF.au3>
#include <GUIConstantsEx.au3>

Local $YesID, $ExitID, $msg
Local $oExcel = _ExcelBookNew(1)
$gui = GUICreate("Custom Msgbox", 210, 80)

GUICtrlCreateLabel("Please click a button!", 10, 10)
$YesID = GUICtrlCreateButton("Validate!", 25, 40, 100, 30)
$ExitID = GUICtrlCreateButton("Exit", 150, 50, 50, 20)
GUISetState(@SW_SHOW); display the GUI
;~ GUISetState(

While 1
    $msg = GUIGetMsg()
    Switch $msg
        Case $YesID
            $Cell = _ExcelFindInRange($oExcel, '1', 1, 2, 25, 2, 0, 2, False, False)
            If $Cell[0][0] > 0 Then
                For $i = 1 To $Cell[0][0]
                    _ExcelWriteCell($oExcel, "Well Done!", $Cell[$i][3], $Cell[$i][2] - 1)
                Next
            Else
                _ExcelWriteCell($oExcel, "Not found!", 4, 4)
            EndIf
        Case $ExitID
            MsgBox(0, "You clicked on", "Bye-Bye!")
            _ExcelBookClose($oExcel)
            Exit
        Case $GUI_EVENT_CLOSE
            _ExcelBookClose($oExcel)
            MsgBox(0, "You clicked on", "Bye-Bye!")
            Exit
    EndSwitch
    Sleep(100)
WEnd

The problem was that the coordinates were inverted. So instead of

$Cell[$i][2], $Cell[$i][3]

it should've been

$Cell[$i][3], $Cell[$i][2]

so the "1"-s would be replaced with "Well done!" and

$Cell[$i][3], $Cell[$i][2] - 1

with a "-1", so the "Well done!" will apear one cell to the left. I've also changed the area for the search.

Now, anywhere where you put an 1 on the B column, the script will add "Well done!" next to every "1", on the A column!

Edited June 11, 2008 by Kiti

[Kiti]

:) I wanna do one more thing

...
Case $YesID
    $Cell = _ExcelFindInRange($oExcel, '1', 1, 2, 25, 2, 0, 2, False, False)
    If $Cell[0][0] > 0 Then
        For $i = 1 To $Cell[0][0]
            $something = $Cell[$i][3], $Cell[$i][2] - 1
            If $something = "Step 1" Then
                _ExcelWriteCell($oExcel, "Step 2", $Cell[$i][3], $Cell[$i][2] - 1)
            EndIf
            If $something = "Step 2" Then
                _ExcelWriteCell($oExcel, "Step 3", $Cell[$i][3], $Cell[$i][2] - 1)
            EndIf
;and so on
    Next
...

Don't try to run this script, beacuse it's not working. I've posted it just for you to understand easier what I want to do: My excel file has many different let's say toys, whcih can be in different production steps. When a step is completed, the user put's a 1 (exactly like a checkbox) and then clicks on "validate". This will increment the step. But I don't know how to declare that $something. So, if he sees Step 1, he will change it with step 2. If he sees step 20, he will change it into step 21, and so on. How can I specify the script that that $something is the cell to the left of every cell containing "1" ?

THANKS ALOT FOR ALL YOUR TIME AND HELP !!!

Edited June 11, 2008 by Kiti

[Kiti]

Look at Case $YesID.

On column A (in excel), I have cells containing "Step" & a number from 1 to 3. What I want my script to do, is when the user puts an "x" on column B and after it presses "Validate" the script should increment the step from column A. Pretty simple, but my script don't really do it. Can anyone spot the problem, or give me an advice on how to make my script working?

Thanks!

Edit: Yaay!!! I've finaly make it work, and I've fixed the bug which was exiting the GUI if you were not selecting another cell after writing "x" on one of them. It works great now!

expandcollapse popup

#include <ExcelCOM_UDF.au3>
#include <GUIConstantsEx.au3>

Local $YesID, $ExitID, $msg
Local $oExcel = _ExcelBookNew(1)
$gui = GUICreate("Custom Msgbox", 210, 80)
WinSetOnTop("Custom Msgbox", "", 1)

GUICtrlCreateLabel("Please click a button!", 10, 10)
$YesID = GUICtrlCreateButton("Validate!", 25, 40, 100, 30)
$ExitID = GUICtrlCreateButton("Exit", 150, 50, 50, 20)
GUISetState(@SW_SHOW); display the GUI
$Note_win = WinGetHandle("Custom Msgbox", "")
WinMove($Note_win, "", 700, 10)
While 1
    $msg = GUIGetMsg()
    Switch $msg
        Case $YesID
            WinActivate("[CLASS:XLMAIN]", "")
            Send("{DOWN}")
            $Cell = _ExcelFindInRange($oExcel, 'x', 1, 2, 25, 2, 0, 2, False, False)
            For $i = 1 To $Cell[0][0]
                $Stare = _ExcelReadCell($oExcel, $Cell[$i][3], $Cell[$i][2] - 1)
                If $Stare = "Step 1" Then
                    _ExcelWriteCell($oExcel, "Step 2", $Cell[$i][3], $Cell[$i][2] - 1)
                EndIf
                If $Stare = "Step 2" Then
                    _ExcelWriteCell($oExcel, "Step 3", $Cell[$i][3], $Cell[$i][2] - 1)
                EndIf
                If $Stare = "Step 3" Then
                    _ExcelWriteCell($oExcel, "Step 4", $Cell[$i][3], $Cell[$i][2] - 1)
                EndIf
                If $Stare = "Step 4" Then
                    _ExcelWriteCell($oExcel, "Step 5", $Cell[$i][3], $Cell[$i][2] - 1)
                EndIf
                If $Stare = "Step 5" Then
                    _ExcelWriteCell($oExcel, "Step 6", $Cell[$i][3], $Cell[$i][2] - 1)
                EndIf
                If $Stare = "Step 6" Then
                    _ExcelWriteCell($oExcel, "Step 7", $Cell[$i][3], $Cell[$i][2] - 1)
                EndIf
                
            Next
            _ExcelReplaceInRange($oExcel, "x", "", 1, 2, 25, 2, 2, False, "", False)
        Case $ExitID
            _ExcelBookClose($oExcel)
            Exit
        Case $GUI_EVENT_CLOSE
            _ExcelBookClose($oExcel)
            Exit
    EndSwitch
    Sleep(100)
WEnd

Edited June 13, 2008 by Kiti