Excel 2010 ribbon command Paste not activated by Ctrl-V

Add-in Express™ Support Service
That's what is more important than anything else

Excel 2010 ribbon command Paste not activated by Ctrl-V
Paste from keyboard and ribbon not the same 
Johan Stap




Posts: 25
Joined: 2011-03-27
Hello

To start with: I read all blogs concerning handling of Copy/Paste but it did not help me...

What is the purpose:
Catching the pasting of a string, possibly containing a predefined marker string.
Then taking action if it is marked:
- formatting the string and placing it in the current cell.
- cancelling the regular paste
If it is not marked:
- just do a normal paste in the current cell.

What the add-in does:
Paste by ribbon button: as expected
Paste by Ctrl-V: executes normal paste in the current cell, always.

As the tooltip when hovering over the ribbon paste button says: "Paste (Ctrl+V)" I would expect
that Ctrl-V does the same as pressing the button, but it does not.

My question
Is this by design or am I doing something wrong?

Thanks for any help, it is appreciated.

Johan Stap

PS: I added a catch for keyboardshortcut but this gives all kinds of unwanted side-effects.
Posted 23 Nov, 2017 08:46:35 Top
Andrei Smolin


Add-in Express team


Posts: 18830
Joined: 2006-05-11
Hello Johan,

You need to use an ADXKeyboardShortcut to intercept pressing Ctrl+V; this is by Office design. What side effects are you getting?


Andrei Smolin
Add-in Express Team Leader
Posted 23 Nov, 2017 09:06:00 Top
Johan Stap




Posts: 25
Joined: 2011-03-27
Hello Andrei

Yes, I could have expected that question ofcourse.


During normal usage: workbook open and then Ctrl-V, perfect.

After release of the add-in this was reported to me and I reproduced it:
If you block Excel e.g. by saving the workbook with "Save as" you are prompted with
the Save-As window, then when you paste e.g. a filepath in the Filename-field (which maybe copied from an Explorer window) - this paste is caught by the keyboard intercept!
The intercept code runs and does things it should not do because at this point the workbook is not accessible.
Even an Excel item like the Statusbar - which is used to display the result of the paste - is not accessible and fails with an exception.

So that explains the error being reported.
But the question is now: how to circumvent this?
Maybe check on availability of the statusbar, but then the normal Paste in the Save-As window will not occur.

This is what I mean by side-effects...
I am used to solving problems in MS stuff (solve one problem, run into the next) with workarounds but this really puzzles me.


Regards,
Johan
Posted 23 Nov, 2017 09:27:12 Top
Johan Stap




Posts: 25
Joined: 2011-03-27
Hello Andrei


BTW: Ctrl-V in Excel is not caught by the ribbon button handler,
but in Word it is.

Regards
Johan

PS
Maybe I should change the subjects' description in "Unexpected result when handling Ctrl-V if Excel is busy"?

I hope you have time to look at this. Thanks.
Posted 23 Nov, 2017 10:14:33 Top
Andrei Smolin


Add-in Express team


Posts: 18830
Joined: 2006-05-11
Hello Johan,

Johan Stap writes:
BTW: Ctrl-V in Excel is not caught by the ribbon button handler, but in Word it is.


This is an interesting observation. I confirm it. PowerPoint triggers the Ribbon command when you press Ctrl-V as well.

Johan Stap writes:
But the question is now: how to circumvent this?


You need to use the OnKeyDown event instead of using the ADXKeyboardShortcut component; make sure HandleShortcut is set to true on the add-in module.

private const int WM_USER = 0x0400;
private const int WM_CtrlV_PRESSED_IN_EXCEL = WM_USER + 1112;
private const int WM_CtrlV_PRESSED_IN_FORMULABAR = WM_USER + 1113;
private const int WM_CtrlV_PRESSED_IN_CELLEDITOR = WM_USER + 1114;

private void AddinModule_OnKeyDown(object sender, ADXKeyDownEventArgs e) {

    // you must not use the Excel object model in this method
    // use AddinModule_OnSendMessage instead; see below

    if (e.Ctrl && !e.Shift && e.VirtualKey == ( int )Keys.V) {
        IntPtr focusedWindow = NativeMethods.GetFocus();
        string focusedWindowClassName = NativeMethods.GetClassName(focusedWindow);
        bool doesAddinHandleThis = false;
        System.Diagnostics.Debug.WriteLine("!!! focusedWindowClassName=" + focusedWindowClassName);
        if (focusedWindowClassName == "EXCEL7") {
            // Excel itself
            doesAddinHandleThis = true;
            this.SendMessage(WM_CtrlV_PRESSED_IN_EXCEL);
        } else if (focusedWindowClassName == "EXCEL<") {
            // Formula bar window
            //doesAddinHandleThis = true;
            //this.SendMessage(WM_CtrlV_PRESSED_IN_FORMULABAR);
        } else if (focusedWindowClassName == "EXCEL6") {
            // in-place cell editor window
            //doesAddinHandleThis = true;
            //this.SendMessage(WM_CtrlV_PRESSED_IN_CELLEDITOR);
        } else if (focusedWindowClassName == "EXCEL=") {
            // sheet name editor window
        }
        e.Handled = doesAddinHandleThis;
    }
}

private void AddinModule_OnSendMessage(object sender, ADXSendMessageEventArgs e) {
    if (e.Message == WM_CtrlV_PRESSED_IN_EXCEL) {
        // you can use the Excel object model here
    }
}
...
using System;
using System.Text;
using System.Runtime.InteropServices;
...
class NativeMethods {
    [DllImport("user32.dll")]
    public static extern IntPtr GetFocus();

    [DllImport("user32.dll")]
    public static extern int GetClassName(IntPtr hWnd, StringBuilder buf, int nMaxCount);

    public static string GetClassName(IntPtr hWnd) {
        string result = String.Empty;
        int MaxCharsCount = 256;
        StringBuilder className = new StringBuilder(MaxCharsCount);
        if (GetClassName(hWnd, className, MaxCharsCount) > 0) {
            result = className.ToString();
        }
        return result;
    }
}



Andrei Smolin
Add-in Express Team Leader
Posted 24 Nov, 2017 07:10:06 Top
Johan Stap




Posts: 25
Joined: 2011-03-27
Wow Andrei

I really thought I was doing something wrong.
So I repaired Office, deleted some Windows update, and more but it didn't help...

Just to let you know:
I started a session on a (VM) machine with Office 2013/2016 and there everything was ok!

I'm impressed by your solution using W32, the last plumbing resort when everything else fails and you must get the job done. Thanks.
I will have a go with it and let you know the outcome.

My observation
I see the code actually checks which is the active/focussed window, so that solves the problem when Excel is blocked by Save-As or another window.

Question
It looks like this specific solution is only necessary in Excel 2010, the lowest version for the add-in.
Could an additional check be implemented which bypasses the code with Office > 14.0?


Kind regards, and hooray for your solution!
Johan

BTW
This is a nice example of the workarounds I mentioned earlier.
Posted 24 Nov, 2017 08:39:58 Top
Andrei Smolin


Add-in Express team


Posts: 18830
Joined: 2006-05-11
Hello Johan,

Johan Stap writes:
This is a nice example of the workarounds I mentioned earlier.


Yes. Workarounds, workarounds, and again workarounds. Being cooked in this with no interruption.

Johan Stap writes:
Question: It looks like this specific solution is only necessary in Excel 2010, the lowest version for the add-in. Could an additional check be implemented which bypasses the code with Office > 14.0?


You can check ADXAddinModule.HostMajorVersion. But could you please explain your reasoning? I don't understand it.


Andrei Smolin
Add-in Express Team Leader
Posted 24 Nov, 2017 08:52:47 Top
Johan Stap




Posts: 25
Joined: 2011-03-27
Hello Andrei


I added this check in the addins' constructor:
//Excel 2010: handling Ctrl-V paste
if (HostType == ADXOfficeHostApp.ohaExcel && HostMajorVersion <= 14)
{
  this.OnKeyDown += ReportMakerAddIn_OnKeyDown;
}


You can check ADXAddinModule.HostMajorVersion. But could you please explain your reasoning? I don't understand it



Just to let you know:
I started a session on a (VM) machine with Office 2013/2016 and there everything was ok!


So it looks like above 2010 Excel behaves like the rest of Office.
Or am I missing something?


Regards from West-Friesland-NL
Johan
Posted 24 Nov, 2017 09:29:18 Top
Johan Stap




Posts: 25
Joined: 2011-03-27
Hi Andrei

This is a summary of the final solution.
1: my remark the paste deviation applies only to Excel 2010 is wrong: all versions are crippled.
2: I used my existing pasting function instead of the SendMessage, with some modification.
3: Thanks alot for your help with this, as it always is like you said: workaround of workarounds!
4: Tested in 2010,2013,2016 => OK

The important part of the OnKeyDown event handler:


        /// <summary>
        /// Handle OnKeyDown event
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        void ReportMakerAddIn_OnKeyDown(object sender, ADXKeyDownEventArgs e)
        {
            // you must not use the Excel object model in this method 
            // use AddinModule_OnSendMessage instead; see below 

            bool doesAddinHandleThis = false;
            if (e.Ctrl && !e.Shift && e.VirtualKey == (int)Keys.V)
            {
                IntPtr focusedWindow = Win32Natives.GetFocus();
                string focusedWindowClassName = Win32Natives.GetClassName(focusedWindow);
                System.Diagnostics.Debug.WriteLine("!!! focusedWindowClassName=" + focusedWindowClassName);
                if (focusedWindowClassName == "EXCEL7")
                {
                    // Excel itself 
                    //doesAddinHandleThis = true;
                    //this.SendMessage(WM_CtrlV_PRESSED_IN_EXCEL);

                    doesAddinHandleThis = true;
                    bool mustCancel = true; // Not used
                    XLRibbon.XLHandlePaste(out mustCancel, PasteSelfIfNotCancelled: true);
                }
                else if (focusedWindowClassName == "EXCEL<")
                { ... the other windows ...


The important part of the XLHandlePaste handler:


        /// <summary>
        /// Handle the Paste
        /// </summary>
        /// <param name="DoCancel"></param>
        /// <param name="PasteSelfIfNotCancelled"></param>
        public void XLHandlePaste(out bool DoCancel, bool PasteSelfIfNotCancelled= false)
        {
            string thePastedDescriptor = "";
            string Message = "";
            DoCancel = false;
            try
            {
                // If this fails, Excel is busy so cancel it
                appExcel.StatusBar = " "; // Clear
                // Check for marker text in clipboard content
                if (Globals.ClipBoardOffice.GetClipboardText(out thePastedDescriptor, ""Application": "SP_SaaS""))
                {
                    // Already marked?
                    if (WordExcelUtils.AskDocMarkedRMReport(this.workbook, out DoCancel))
                    {
                        // Do insert, continue
                        DoCancel = true; // always true
                    }
                    else
                    {
                        if (PasteSelfIfNotCancelled && !DoCancel)
                        {
                            // Paste clipboard content as it is
                            this.worksheet.Paste();
                        }
                        // Stop
                        return;
                    }

                    // ... Construction of insertion string and the insertion itself ...


Posted 24 Nov, 2017 14:38:28 Top
Andrei Smolin


Add-in Express team


Posts: 18830
Joined: 2006-05-11
Thank you very much!


Andrei Smolin
Add-in Express Team Leader
Posted 27 Nov, 2017 08:58:39 Top