Jump to content
Sign in to follow this  
smellyfingers

Excel, Active Cell in Edit mode crash script

Recommended Posts

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

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


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2020-10-10 - Version 1.5.2.1) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2020-06-30 - Version 1.6.2.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX_GUI (NEW 2020-06-27 - Version 1.3.2.0) - Download
Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - 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
Task Scheduler (2019-12-03 - Version 1.5.1.0) - Download - General Help & Support - Wiki

Tutorials:
ADO - Wiki, WebDriver - Wiki

 

Share this post


Link to post
Share on other sites

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


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2020-10-10 - Version 1.5.2.1) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2020-06-30 - Version 1.6.2.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX_GUI (NEW 2020-06-27 - Version 1.3.2.0) - Download
Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - 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
Task Scheduler (2019-12-03 - Version 1.5.1.0) - Download - General Help & Support - Wiki

Tutorials:
ADO - Wiki, WebDriver - Wiki

 

Share this post


Link to post
Share on other sites

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 (NEW 2020-10-10 - Version 1.5.2.1) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2020-06-30 - Version 1.6.2.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX_GUI (NEW 2020-06-27 - Version 1.3.2.0) - Download
Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - 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
Task Scheduler (2019-12-03 - Version 1.5.1.0) - Download - General Help & Support - Wiki

Tutorials:
ADO - Wiki, WebDriver - Wiki

 

Share this post


Link to post
Share on other sites

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

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 (NEW 2020-10-10 - Version 1.5.2.1) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2020-06-30 - Version 1.6.2.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX_GUI (NEW 2020-06-27 - Version 1.3.2.0) - Download
Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - 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
Task Scheduler (2019-12-03 - Version 1.5.1.0) - Download - General Help & Support - Wiki

Tutorials:
ADO - Wiki, WebDriver - Wiki

 

Share this post


Link to post
Share on other sites

I will post an example tomorrow.


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2020-10-10 - Version 1.5.2.1) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2020-06-30 - Version 1.6.2.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX_GUI (NEW 2020-06-27 - Version 1.3.2.0) - Download
Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - 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
Task Scheduler (2019-12-03 - Version 1.5.1.0) - Download - General Help & Support - Wiki

Tutorials:
ADO - Wiki, WebDriver - Wiki

 

Share this post


Link to post
Share on other sites

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 (NEW 2020-10-10 - Version 1.5.2.1) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2020-06-30 - Version 1.6.2.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX_GUI (NEW 2020-06-27 - Version 1.3.2.0) - Download
Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - 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
Task Scheduler (2019-12-03 - Version 1.5.1.0) - Download - General Help & Support - Wiki

Tutorials:
ADO - Wiki, WebDriver - Wiki

 

Share this post


Link to post
Share on other sites

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

:D


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2020-10-10 - Version 1.5.2.1) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2020-06-30 - Version 1.6.2.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX_GUI (NEW 2020-06-27 - Version 1.3.2.0) - Download
Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - 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
Task Scheduler (2019-12-03 - Version 1.5.1.0) - Download - General Help & Support - Wiki

Tutorials:
ADO - Wiki, WebDriver - 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  

  • Recently Browsing   0 members

    No registered users viewing this page.

  • Similar Content

    • By Cristin
      Dear all,
      first of all hi.
      I`m in trouble, again 😕
      I can`t find right solution, tried all possible combinations generated by my mind and nothing.
      I need to copy range of values depending by values from two different cells in excel sheet.
      I put an example in attachment.

      I have two values which is never changed (is changed only TEXT). I need to copy all the rows from VALUE 1 to VALUE 2 in new Sheets (Output 1, Output 2, ... , Output X).
      It is possible to do this using Autoit? 🤔
      Also in attachment you can find last example which I have tried and it`s something near, it is copying by _Excel_RangeFind row with needed VALUE 1 but I need to copy whole range from VALUE 1 to VALUE 2 😵 ( see in attachment examples).
      Thank you very much, in advance, for your support, solution really will make my life much easier 🙃
      Have a nice day all of you!
      Best Regards,
      Cristin
      Book1.xlsx something.au3
    • By sudeepjd
      I was looking for a UDF using which I could Add and Update Pivot tables and Pivot Charts in Excel easily and could not find one that I could use. So I build this UDF. 
      It has the following functions :
      _ExcelPivot_CreateCache ; Easily Create a pivot table data cache from a Sheet _ExcelPivot_CreateTable ; Create a table from a cache at a specified location on the sheet _ExcelPivot_RefreshTable ; Refresh the datatable data with a new cache _ExcelPivot_AddField ; Add a Field and Aggregate function to the Datatable _ExcelPivot_AddFilter ; Adds in the Filter to a specific field _ExcelPivot_ClearFilter ; Removes the filter for a field or all the filters in the table _ExcelPivot_GetRange ; Get specific areas of a Pivot as a Range Object _ExcelPivot_AddChart ; Add a Pivot Chart linked to a specific Pivot table Attached the UDF to this post.
      Please do let me know if I can improve or add additional functions to it.
      A detailed example on the usage is below. The excel file and the example can be downloaded from the Example.zip file attached.
      #include "ExcelPivot.au3" $oExcel = _Excel_Open() $oBook = _Excel_BookOpen($oExcel, @ScriptDir & "\TestPivot.xlsx") ;Create a Sheet to put the pivot into $pSheet = _Excel_SheetAdd($oBook, -1, False, 1) $pSheet.Name = "Pivot" ;Get the cache for the pivot table $pCache = _ExcelPivot_CreateCache($oBook, "Data") ;Add in the Pivot Table from the Cache _ExcelPivot_CreateTable($pCache, $pSheet, "A1", "FruitsPivot") ;Add in the Fields into the Pivot _ExcelPivot_AddField($pSheet, "FruitsPivot", "Category", "Filter") _ExcelPivot_AddField($pSheet, "FruitsPivot", "Product", "Row") _ExcelPivot_AddField($pSheet, "FruitsPivot", "Amount", "Value", "Sum", 1) ;Add in a Running total to the Pivot _ExcelPivot_AddField($pSheet, "FruitsPivot", "Amount", "Value", "Sum", 2, "PercentageRunningTotal", "Product") ;Filter only the fruits _ExcelPivot_Filter($pSheet, "FruitsPivot", "Category", "Fruit") ;Draw a Paretto Chart $chart = _ExcelPivot_AddChart($oBook, $pSheet, "FruitsPivot", "ColumnClustered", "Paretto", "E2", 570) $chart.Chart.FullSeriesCollection(1).ApplyDataLabels $chart.Chart.FullSeriesCollection(2).ChartType = 4 ;Change the percentage to a line graph $chart.Chart.FullSeriesCollection(2).AxisGroup = 2 ;Move it to secondary axis $chart.Chart.Axes(2, 2).MaximumScale = 1 ;Adjust to scale to 100% max  
       
      ExcelPivot.au3 Example.zip
    • By Page2PagePro
      Excel VBA's IDE registers a Control-y as "cut this line of code".
      For those prone to Undo/Redo (Ctrl+Z/Ctrl+Y) you may find frustration when your code in the editor does not redo, but in fact clears your active line of code while killing redo history.
      Though not perfect, I keep this tool running in background on startup.
      The purpose is to allow Cltr+Y to act normally throughout Windows and Office and only interact *differently* with the "Microsoft Visual Basic for Applications" window that is active.
      If the Standard Menu bar exists, it'll try to click the ReDo (Blue Arrow to the right), else "Alt+e, r" keystrokes (less desired).
       
      Here's the code:
      Opt('MustDeclareVars', 1) Opt("WinTitleMatchMode", 1) HotKeySet("^y", "TriggerRedo") While 1 Sleep(10) WEnd Func TriggerRedo() ConsoleWrite("TriggerRedo()" & @CRLF) Local $title = "Microsoft Visual Basic for Applications - " Local $hWnd If WinExists($title) And WinActive($title) Then ;~ Parent Window Handle $hWnd = WinGetHandle($title) Local $aWindowPos = WinGetPos($hWnd) ;~ Control Bar Handle, Position and If Visible Local $sControlID = "[CLASS:MsoCommandBar; TEXT:Standard;]" Local $hStandardBar = ControlGetHandle($hWnd, "", $sControlID) Local $bIsVisible = ControlCommand($hWnd, "", $sControlID, "IsVisible") If $hStandardBar And $bIsVisible Then ConsoleWrite("Using Mouse Click." & @CRLF) ;~ Determine Redo button location on visible Control Bar Local $aBarPos = ControlGetPos($hWnd, "", $sControlID) Local $mX = $aWindowPos[0] + $aBarPos[0] + 217 + Int(23/2) Local $mY = $aWindowPos[1] + $aBarPos[1] + 27 + Int(22/2) MouseClick("Left", $mX, $mY, 1, 0) Else ConsoleWrite("Using VBA Send Keys." & @CRLF) $sControlID = "[CLASS:MsoCommandBar; TEXT:Menu Bar;]" Local $hMenuBar = ControlGetHandle($hWnd, "", $sControlID) ControlSend($hWnd, "", $hMenuBar, "!e") ;~ Send("r") $sControlID = "[CLASS:MsoCommandBarPopup; TEXT:Edit;]" Local $hPopupBar = ControlGetHandle($hWnd, "", $sControlID) ControlSend($hWnd, "", $hPopupBar, "r") EndIf Else ConsoleWrite("Using NATIVE Send Keys." & @CRLF) HotKeySet("^y") Send("^y") ;~ may cause "yyy..." when held HotKeySet("^y", "TriggerRedo") EndIf EndFunc ;==>TriggerRedo Hope this inspires someone.
       
       
    • By siawpo
      Hi,
      I'd like to change different colors for different portion of text in same cell of Excel application.
      Neither character length nor cell might not fixed.
      Here's the code I've tried to put together but not manage to pull it off.
      I'm appreciate it for any suggestion, thank you.
      $oExcel = ObjCreate("Excel.Application") With $oExcel ; open new workbook .Visible = True .WorkBooks.Add .ActiveWorkbook.Sheets(1).Select() EndWith $oExcel.Cells.Font.Color = 0x000000 $oExcel.ActiveFont.Color = -16776961 Send ("'I'd like this sentence to be red'") Sleep(100) Send ("{AltDown}{Enter}{AltUp}") Sleep(100) $oExcel.ActiveCell.Selection.Font.Color = 0x000000 Send ("'I like this sentence to be black'") Sleep(100) Send ("{AltDown}{Enter}{AltUp}") Sleep(100) $oExcel.ActiveFont.Color = -16776961 Send ("'I'd like this sentence to be red again'") Sleep(100) Send ("{AltDown}{Enter}{AltUp}") Sleep(100) Send("{ENTER}")  
    • By Langmeister
      Hi, I'm sure that this is a simple question regarding webdriver udf but my search on the forum did not result in the exact same scenario that I need. I will adapt it to my corporate needs but at first I want it to work with an example everybody can reproduce if needed.
      I navigate to https://www.daysoftheyear.com/ and want to find out what special day today is. Using the following Code returns the wanted text that today is 'shark awareness day' in the console but it is not pasted in notepad as it is not copied as it probably should be.
      #include "wd_core.au3" #include "wd_helper.au3" Local $sDesiredCapabilities, $sSession, $sID _WD_Option("Driver", "C:\Program Files (x86)\AutoIt3\chromedriver.exe") _WD_Option('Port', 9515) _WD_Option('DriverParams', '--log-path="' & @ScriptDir & '\chrome.log"') $sDesiredCapabilities = '{"capabilities": {"alwaysMatch": {"goog:chromeOptions": {"w3c": true }}}}' _WD_Startup() $sSession = _WD_CreateSession($sDesiredCapabilities) _WD_Navigate($sSession, "https://www.daysoftheyear.com/?timezone_offset=nan") _WD_LoadWait($sSession) Sleep(3000) Local $sElement = _WD_FindElement($sSession, $_WD_LOCATOR_ByXPath, "/html/body/div[2]/header/div/div[1]/div/div[2]/h3/a") _WD_ElementAction($sSession, $sElement, 'text') ClipGet() Run("notepad.exe") Sleep(500) Send("^v") Scite returns this, therefore the text has been found but the last mile to paste it to notepad or excel does not work for me.
      __WD_Get: URL=HTTP://127.0.0.1:9515/session/b72166b774d7fefb258b3721fc1d4306/element/657e6b5d-31a9-46a8-85c4-acb7f72bf659/text __WD_Get: StatusCode=200; $iResult = 0; $sResponseText={"value":"Shark Awareness Day"}... _WD_ElementAction: {"value":"Shark Awareness Day"}... Thanks in advance! 
       
×
×
  • Create New...