Excel Trapping Copy and paste. - Add-in Express .NET forum

Dan Evans

Posts: 27
Joined: 2014-06-18

Hope that maybe someone can help with this one. I have built a worksheet audit system that stores past values along with other meta data, when a range is cut and pasted in the same workbook the stored meta data needs to follow the paste. The problem I am having is trapping the copy and paste events from the add in.

I have found a way of detecting a paste by simply checking the "&Undo" control from the "standard" commandbar to see if the tooltip text starts with "&undo paste". as this is being trapped in the WorksheetChange event I get the range where the values were pasted. not a terribly wonderful way but a way none the less.

The remaining issue I have is how to detect if cells have been cut or copied. I suppose I could check the content of the clipboard but what I need is the range address that has been copied which I don't believe is in the clipboard.

As always any pointers welcome.

Regards
Dan.

Andrei Smolin

Add-in Express team

Posts: 18830
Joined: 2006-05-11

Hello Dan,

This is an interesting task.

You need to handle the Commandbars.OnUpdate event in order to detect the changes that occur in Excel and workbook while the user performs the cut/copy operation. In Add-in Express, this event is mapped to the CommandbarsUpdate event of the Excel Events component; you need to put such a component onto the add-in module.

I expect that the cut/copy operation starts with Application.CutCopyMode changing from zero to xlCut or xlCopy. When you detect this change, you need to get selection and be prepared for paste and cancel. You detect the paste by checking the caption of the Undo CommandBarControl; if CutCopyMode is xlCopy, the paste does NOT mean the end of the operation. The operation is cancelled if Application.CutCopyMode becomes zero without pasting e.g. your typing in another cell cancels the cut/copy operation.

That is, you intercept the CommandbarsUpdate event and check the above-mentioned properties to detect the stages of the cut/copy operation. I used this approach to detect user actions on selected shapes in Excel, see http://www.add-in-express.com/creating-addins-blog/2012/02/21/excel-shapes-events/.

Please pay special attention to this scenario:
- you select a cell and press Ctrl+C (Ctrl+Insert)
- you select some other cell and press Ctrl+C

I wonder if pressing Ctrl+C in the last step generates two CommandbarsUpdate events (the first lets you find CutCopyMode changed from xlCopy to 0 zero and the second lets you find CutCopyMode set to xlCopy) or just one. If it is one CommandbarsUpdate event, you'll also need to intercept pressing Ctrl+C (and probably Ctrl+X, Shift+Delete) using an ADXKeyboardShortcut component. Note that doing this prevents the host application from getting the key press, so you'll need to analyze the selection and invoke the Copy/Cut method on the corresponding object programmatically.

Please keep me notified about your progress. I'll try to help.

Andrei Smolin
Add-in Express Team Leader

Dan Evans

Posts: 27
Joined: 2014-06-18

Thankyou for your insight in to this problem.
I will let you know how I get on shortly .
Regards Dan.

Dan Evans

Posts: 27
Joined: 2014-06-18

Yes, your suspicions were correct. When in copy (or cut) mode and the range being copied (or cut) changes by a second copy being issued, the mode does not cancel then re-apply so the setting of the second range is not captured.

So long as the copy operation doesn't change the below code works great.. Unfortunately there is a hole in the logic so I will need to extend. Currently looking at intercepting onAction of copy and paste command bars.

     
        private static Microsoft.Office.Interop.Excel.XlCutCopyMode _CutCopyMode  = new XlCutCopyMode();
        private static Range _CutCopyRange = null;
        
        static void adxExcelEvents_CommandBarsUpdate(object sender, EventArgs e)
        {
            //Check for changes to CutCopyMode
            if (_CutCopyMode != ModuleExcel.App.Application.CutCopyMode)
            {
                _CutCopyMode = ModuleExcel.App.Application.CutCopyMode;
                _CutCopyRange = (_CutCopyMode == XlCutCopyMode.xlCopy || _CutCopyMode == XlCutCopyMode.xlCut)
                    ?ModuleExcel.App.ActiveCell 
                    :null;
            }
        }

        static void adxExcelEvents_SheetChange(object sender, object sheet, object range)
        {
            Worksheet _sheet = (sheet as Worksheet);
            Range _range = (range as Range);

            var undolist = ModuleExcel.App.Application.CommandBars["Standard"].Controls["&Undo"];

            if (undolist.accChildCount > 0 && undolist.TooltipText.StartsWith("&Undo Paste"))
            {
                if (_CutCopyRange != null)
                {
                    //Values Pasted from CutCopyRange. Handle event here... 
                    Debug.WriteLine("Values Pasted from " + _CutCopyRange.get_Address() + " to " + _range.get_Address());
                    
                    
                    _CutCopyRange = null;
                }
            }
        }

Andrei Smolin

Add-in Express team

Posts: 18830
Joined: 2006-05-11

Dan,

ModuleExcel.App.ActiveCell returns the active cell only. You may copy a range, not just a cell. So you need to retrieve the selection and check it is an Excel.Range.

Andrei Smolin
Add-in Express Team Leader

Dan Evans

Posts: 27
Joined: 2014-06-18

Edit: Thankyou for the feedback, I have updated to use selection in place of Active cell.
I have also noticed that a cut operation somehow swaps the from and to ranges which is pretty bizarre.

Adding the below code to a pair of ADX Ribbon command handlers resolves the loss of cut copy address when a 2nd copy or cut occurs when clicking the ribbon button. Also covers the context menu which is nice.


               static void adxRibbonCommandCut_OnAction(object sender, AddinExpress.MSO.IRibbonControl control, bool pressed, AddinExpress.MSO.ADXCancelEventArgs e)
        {
            //idMso = Cut
            if (_CutCopyMode == XlCutCopyMode.xlCut)
            {
                //already in copy mode update, reset selection. 
                _CutCopyRange = ModuleExcel.App.Selection as Range;
            }
        }

        static void adxRibbonCommandEditCopy_OnAction(object sender, AddinExpress.MSO.IRibbonControl control, bool pressed, AddinExpress.MSO.ADXCancelEventArgs e)
        {
            //idMso = Copy
            if (_CutCopyMode == XlCutCopyMode.xlCopy)
            {
                //already in copy mode update, reset selection. 
                _CutCopyRange = ModuleExcel.App.Selection as Range;
            }
        }

        private static XlCutCopyMode _CutCopyMode  = new XlCutCopyMode();
        private static Range _CutCopyRange = null;
        
        static void adxExcelEvents_CommandBarsUpdate(object sender, EventArgs e)
        {
            //Check for copy paste events. 
            if (_CutCopyMode != ModuleExcel.App.Application.CutCopyMode)
            {
                _CutCopyMode = ModuleExcel.App.Application.CutCopyMode;
                _CutCopyRange = (_CutCopyMode == XlCutCopyMode.xlCopy || _CutCopyMode == XlCutCopyMode.xlCut)
                    ? ModuleExcel.App.Selection as Range
                    :null;
            }
        }

        private static void adxExcelEvents_SheetChange(object sender, object sheet, object range)
        {
            Worksheet _sheet = (sheet as Worksheet);
            Range _range = (range as Range);

            var undolist = ModuleExcel.App.Application.CommandBars["Standard"].Controls["&Undo"];

            if (undolist.accChildCount > 0 && undolist.TooltipText.StartsWith("&Undo Paste"))
            {
                if (_CutCopyRange != null)
                {
                    //Values Pasted from CutCopyRange. Handle event here... 
                    if (_CutCopyMode == XlCutCopyMode.xlCut)
                    {
                        Debug.WriteLine("Values Cut from " + _range.get_Address() + " to " + _CutCopyRange.get_Address());
                        _CutCopyRange = null;
                    }
                    else
                    {
                        Debug.WriteLine("Values Copied from " + _CutCopyRange.get_Address() + " to " +
                                        _range.get_Address());
                    }
                }
            }
        }

Dan Evans

Posts: 27
Joined: 2014-06-18

OK not as bad as I thought it might end up.
The key hooks as you suggested use the adxKeyboardShortcut component and work pretty much the same way as the RibbonCommands resetting the _CutCopyRange to the selection.

I am invoking the command bar directly, Is this what you meant when you said "and invoke the Copy/Cut method on the corresponding object programmatically. "?


  static void adxKeyboardShortcutCut_Action(object sender)
        {
            if (_CutCopyMode == XlCutCopyMode.xlCut)
            {
                //already in copy mode update, reset selection. 
                _CutCopyRange = ModuleExcel.App.Selection as Range;
            }

            //Pass the cut command on. 
            ModuleExcel.App.CommandBars["Edit"].Controls["&Cut"].Execute();
        }

        static void adxKeyboardShortcutCopy_Action(object sender)
        {
            if (_CutCopyMode == XlCutCopyMode.xlCopy)
            {
                //already in copy mode update, reset selection. 
                _CutCopyRange = ModuleExcel.App.Selection as Range;
            }

            //Pass the copy command on. 
            ModuleExcel.App.CommandBars["Edit"].Controls["&Copy"].Execute();
        }

Thank you for the direction it has proved invaluable.

Andrei Smolin

Add-in Express team

Posts: 18830
Joined: 2006-05-11

Hello Dan,

Dan Evans writes:
I am invoking the command bar directly, Is this what you meant when you said "and invoke the Copy/Cut method on the corresponding object programmatically. "?

In fact, no. I meant you get Excel.Selection, find out the type of the selected object(s), cast the selection to that type and call the Copy method if that type provides it. You call Microsoft.VisualBasic.Information.TypeName(obj) to get the type name of the obj parameter.

Note that you need to check the selection because you may press Ctrl+C when e.g. renaming a worksheet or editing a formula. Besides, there's a few unlikely scenarios in which your approach won't work: an add-in or VBA macro deletes the Copy button (the Standard commandbar) or moves the Copy button to another toolbar. A higher probability is that the next Office version will stop supporting commandbar things. Plus, you would need to deal with all scenarios in which the user presses the keyboard shortcut but the corresponding button is disabled (e.g. due to the current context).

Andrei Smolin
Add-in Express Team Leader

Dan Evans

Posts: 27
Joined: 2014-06-18

thank you. Valid points taken
I've updated my code. (I've only shown the copy as the cut is pretty much the same) When getting the type of the selection, I can only see system.__ComObject. after checking via a watch and editing different parts of excel (formula, sheet tab) the selection object was always showing as a Range. The below appears to work, the only odd thing occurs when editing a named range, I'm sure copy and paste would work in the Name box..


        static void adxKeyboardShortcutCopy_Action(object sender)
        {
            if (ModuleExcel.App.Selection is Range)
            {
                Range selectedRange = (ModuleExcel.App.Selection as Range);
                if (_CutCopyMode == XlCutCopyMode.xlCopy)
                {
                    //already in copy mode update, reset selection. 
                    _CutCopyRange = selectedRange;
                }
                selectedRange.Copy();
            }
            else
            {
                //Pass the cut command on. 
                Debug.WriteLine("Copy Type Exception");
            }
        }

Andrei Smolin

Add-in Express team

Posts: 18830
Joined: 2006-05-11

Dan,

Here's a citation from the manual:

When debugging the add-in we recommend that you find the actual type name of the context object by using Microsoft.VisualBasic.Information.TypeName(). This requires that your project reference Microsoft.VisualBasic.dll.

You use this approach to find out the type name of the selection. I expect that every type from the Excel object model that provides the Select method can be returned when you call ExcelApp.Selection.

Dan Evans writes:
the only odd thing occurs when editing a named range

Odd?

Whenever I hear "odd" or "strange" I remember about unreleased COM objects.

Note that every call ModuleExcel.App.Selection in your code above creates a new COM object. And you'd better release all of them; the result of leaving a COM object unreleased depends on the COM object implementation. Although Excel looks liberal in this respect, you don't know how this or that COM object is implemented. In our practice there were situations requiring that we release every COM object created.

This approach requires that you rewrite the code as follows below; this is a raw sketch:

    object selection = ModuleExcel.App.Selection;
    if (selection != null) {
        if (selection is Range) {
            Range selectedRange = (selection as Range);
            ... 
        } else {
            ...
        }
        Marshal.ReleaseComObject(selection);
    }

Andrei Smolin
Add-in Express Team Leader