Jump to content
Sign in to follow this  
smellyfingers

Excel, Active Cell in Edit mode crash script

Recommended Posts

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

Share this post


Link to post
Share on other sites
water

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


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (2018-06-01 - Version 1.4.9.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2018-09-01 - Version 1.3.4.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
PowerPoint (2017-06-06 - Version 0.0.5.0) - Download - General Help & Support
Excel - Example Scripts - Wiki
Word - Wiki
 
Tutorials:

ADO - Wiki

 

Share this post


Link to post
Share on other sites
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?

Share this post


Link to post
Share on other sites
water

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


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (2018-06-01 - Version 1.4.9.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2018-09-01 - Version 1.3.4.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
PowerPoint (2017-06-06 - Version 0.0.5.0) - Download - General Help & Support
Excel - Example Scripts - Wiki
Word - Wiki
 
Tutorials:

ADO - Wiki

 

Share this post


Link to post
Share on other sites
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

Share this post


Link to post
Share on other sites
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.


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (2018-06-01 - Version 1.4.9.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2018-09-01 - Version 1.3.4.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
PowerPoint (2017-06-06 - Version 0.0.5.0) - Download - General Help & Support
Excel - Example Scripts - Wiki
Word - Wiki
 
Tutorials:

ADO - Wiki

 

Share this post


Link to post
Share on other sites
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

Share this post


Link to post
Share on other sites
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 by water

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (2018-06-01 - Version 1.4.9.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2018-09-01 - Version 1.3.4.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
PowerPoint (2017-06-06 - Version 0.0.5.0) - Download - General Help & Support
Excel - Example Scripts - Wiki
Word - Wiki
 
Tutorials:

ADO - Wiki

 

Share this post


Link to post
Share on other sites
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"

Share this post


Link to post
Share on other sites
water

I will post an example tomorrow.


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (2018-06-01 - Version 1.4.9.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2018-09-01 - Version 1.3.4.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
PowerPoint (2017-06-06 - Version 0.0.5.0) - Download - General Help & Support
Excel - Example Scripts - Wiki
Word - Wiki
 
Tutorials:

ADO - Wiki

 

Share this post


Link to post
Share on other sites
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.

#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

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (2018-06-01 - Version 1.4.9.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2018-09-01 - Version 1.3.4.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
PowerPoint (2017-06-06 - Version 0.0.5.0) - Download - General Help & Support
Excel - Example Scripts - Wiki
Word - Wiki
 
Tutorials:

ADO - Wiki

 

Share this post


Link to post
Share on other sites
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!

Share this post


Link to post
Share on other sites
water

:D


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (2018-06-01 - Version 1.4.9.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2018-09-01 - Version 1.3.4.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
PowerPoint (2017-06-06 - Version 0.0.5.0) - Download - General Help & Support
Excel - Example Scripts - Wiki
Word - Wiki
 
Tutorials:

ADO - Wiki

 

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  

  • Similar Content

    • Skeletor
      By Skeletor
      Hi Guys,
      Can anyone point me in the right direction. I'm trying to accomplished conditional formatting with arrows, but the code I have is wrong... 
      .Range("=$A3:$A4000").FormatConditions.Add = (xlCellValue, xlGreater, "=$A:$A") .IconSet(xl3Arrows) ;ActiveWorkbook.iconsets I also tried it like this:
      .Range("=$A3:$A4000").FormatConditions _ .Add(xlCellValue, xlGreater, "=$A:$A") .IconSet(xl3Arrows) ;ActiveWorkbook.iconsets  
    • Skeletor
      By Skeletor
      Hi Guys,
      How would you use _Excel_FilterSet to filter the excel sheet from largest number to smallest number?
      _Excel_FilterSet($oWorkbook, "Sheet1", "A2:I2", 1, ">20", 1, "<40") ; OR _Excel_FilterSet($oWorkbook, "Sheet1", "A2:I2", 1, ">", 1, "<") Excel Sheet attached. Trying to filter on the last column "I2"
      Inventory.xlsx
    • Skeletor
      By Skeletor
      Hi All,
      Has anyone come across this before?
      Code below:
      $SheetList = _Excel_SheetList($oWorkbook) _FileWriteFromArray("C:\" & $SheetListOutput, $SheetList, 1) Result:
      ResultABC| ResultDEF| ResultGHI| ResultJKL| ResultMNO| It created these "|" vertical bars?
    • Skeletor
      By Skeletor
      Hi All,

      I would like to know how you would take a FileLineRead and insert it into an array which then inserts it into Excel?
      One thing to know is the files content is broken up, so I only use half of the content within $FileRead1.
      So its imperative that the $value1, $value2, etc variables be used. 
      Code below:
      $FileRead1 = FileReadLine("C:\temp\sample.txt",1) For $count = 1 To _FileCountLines($FileRead1) Step 1 $string = FileReadLine($FileRead1, $count) $input = StringSplit($string, ",", 1) $value1 = $input[1] $value2 = $input[2] $value3 = $input[3] $value4 = $input[4] _Excel_RangeWrite($oWorkbook, $oWorkbook.Activesheet, $value1, "A1") _Excel_RangeWrite($oWorkbook, $oWorkbook.Activesheet, $value2, "B1") _Excel_RangeWrite($oWorkbook, $oWorkbook.Activesheet, $value3, "C1") _Excel_RangeWrite($oWorkbook, $oWorkbook.Activesheet, $value4, "D1") Next  
    • AnonymousX
      By AnonymousX
      Hello,
      I'm trying to write a script that moves copies excel cells into an array. I'll than manipulate the values and send array into another program. 
      I don't want range to be specific to a workbook, or sheet, or set of cells.
      I want user to be able to highlight desired cells and to copy either normally ("Ctrl+C") or by a hotkey ("Alt+C"). 
      Could someone help me with this?
      Thank you,
      I've tried to write the framework: (edited)
      #include <MsgBoxConstants.au3> #include <Array.au3> #include <Excel.au3> HotKeySet("!v", "Pastedata") While True Sleep(1000) WEnd func Makearray() local $bArray ;User has cells already copied ;Convert clipboard into an array ;I don;t know how excel stores data to clipboard so don;t know how to bring it into array _Arraydisplay($bArray) MsgBox(0,0,$bArray) return $bArray endfunc func Pastedata() Local $aArray MsgBox(0,0,"wait",1) ;make array based on assumption user has already copied a range to clipboard $aArray = Makearray() ;paste code ;don;t worry about this I got the rest endfunc  
×