Excel, Active Cell in Edit mode crash script

[smellyfingers]

Hi, i'm trying to build a little script that "suppose" to work like this, when it finds a excel book active, get the active cell content to clipboard, it works (kinda not optimized) but crash if the cell is in edit mode, i want to somehow get the state of the active cell so the script doesn't crash just wait for the "normal" active cell state, is that possible?, here's the code so far:

#include <Excel.au3>
#include <File.au3>
#include <String.au3>


Global $lastSelection,$oExcel,$oRange,$replaced,$selected,$title,$oSheet,$count

While 1
    Do
$title = WinGetTitle("")
Until StringInStr($title,"Microsoft Excel -")
While 1
    If StringInStr($title,"Microsoft Excel") Then
        $replaced = StringReplace($title,"Microsoft Excel - ","")
        $oExcel = _ExcelBookAttach(@ScriptDir & "\" & $replaced)
        If Not @error Then;?? I don't know how to check this
        $oRange = $oExcel.Application.ActiveCell
        $selected = $oRange.Value
        ClipPut($selected)
        Sleep(1000)
    Else
        ExitLoop
        EndIf
    EndIf

WEnd
ExitLoop
WEnd

And the error on exit is when i doubleclick in a cell so it becomes editable:

C:\Users\Admin\Desktop\copyExcelCell.au3 (17) : ==> The requested action with this object has failed.:
$oRange = $oExcel.Application.ActiveCell
$oRange = $oExcel.Application^ ERROR
->23:05:06 AutoIT3.exe ended.rc:1
>Exit code: 1    Time: 24.515

/ Smelly

Thanx

[water]

You can't process a cell by COM that is in edit mode by a user.

[smellyfingers]

Yes but is there a way to know that it is in edit mode somehow so it doesn't make the call to object?

[water]

Is it possible to block the user from editing the Worksheet?

[smellyfingers]

I thought of that also but it needs to be closed and opened in read only mode for that to happen?, or is it possible to make it read only after attach?

Thanx

[water]

Use

$oExcel.Application.Interactive = False

to block all mouse/keyboard activity by the user. When you've finished processing the workbook you can activate user activity again.

I don't know what happens if a cell is already in edit mode while you set the Interactive property to False.

[smellyfingers]

Nope, i still need to click on a cell but not make it editable, the only way i guess is to make it read only from the beginning, the scripts function is based on a user moving around from cells and automatically gets the cells info copied for further use in diffrent programs :/, well i'm too tired to think right now anyways gonna get to work again in a few hours so better jump to bed anyways , thanx /Smelly

[water]

Opening a Workbook readonly still permits the user to edit cells. He is just unable to save the modified Workbook.

Another possible solution would be to use events. When a user edits a cell a event is triggered. When processing such an event you can do whatever you like without interfering with the user.

Good night

I will go to bed too!

Edited September 30, 2013 by water

[smellyfingers]

Sounds like a plan , just need to dig into the deep about excel events then and learn how to actually write it in autoit the "write way"

[water]

I will post an example tomorrow.

[water]

This example script handles the Excel worksheet change event. Every time a cell has been changed the event is triggered.

The example then colors the cell in red.

expandcollapse popup

#AutoIt3Wrapper_AU3Check_Stop_OnWarning=Y
#AutoIt3Wrapper_AU3Check_Parameters=-d -w 1 -w 2 -w 3 -w 4 -w 5 -w 6

#include <Excel Rewrite.au3>
#include <Constants.au3>

; *****************************************************************************
; Example Script
; Handle Excel worksheet change event when a cell has been changed and set the
; color of the cell to red.
; This script loops until Shift-Alt-E is pressed to exit.
; *****************************************************************************
HotKeySet("+!e", "_Exit") ;Shift-Alt-E to Exit the script
MsgBox(64, "Excel UDF: _Excel_Example_Events Example", "Hotkey to exit the script: 'Shift-Alt-E'!")

; Create application object and open a workbook
Global $oAppl = _Excel_Open()
If @error <> 0 Then Exit MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_Example_Events Example", "Error creating the Excel application object." & @CRLF & "@error = " & @error & ", @extended = " & @extended)
Global $oWorkbook = _Excel_BookOpen($oAppl, @ScriptDir & "\_Excel2.xls")
If @error <> 0 Then
    MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_Example_Events Example", "Error opening workbook '_Excel2.xls'." & @CRLF & "@error = " & @error & ", @extended = " & @extended)
    _Excel_Close($oAppl)
    Exit
EndIf
ObjEvent($oWorkbook.Activesheet, "Worksheet_")
While 1
    Sleep(10)
WEnd
Exit

; Excel - Worksheet change event - http://msdn.microsoft.com/en-us/library/ff839775(v=office.14).aspx
Func Worksheet_Change($oRange)
    $oRange.Interior.ColorIndex = 3
EndFunc   ;==>Worksheet_Change

Func _Exit()
    Exit
EndFunc   ;==>_Exit

[smellyfingers]

Very nice and clean example, works great with my old office 2007 too, i  made another version today with the mailslot and excelevent includes that copies any active cell to clipboard and waits when before doubleclick and after sheetchange so it doesn't crash, this is great stuff to learn, thanx water!

[water]