ExcelDNA with WPF - Textbox Input

212 views
Skip to first unread message

A Parmar

unread,
Apr 23, 2024, 5:24:54 AMApr 23
to Excel-DNA
Hi Govert/Team,

ExcelDNA Version (latest): 1.8.0-alpha3
Target: NET 6.0-Windows

When launching a WPF page from ExcelDNA - the Keyboard input is going to the Sheet and not into the TextBox.

If I launch the WPF Project directly, the TextBox is working.


I'm using .Show() - which gives focus to both Excel and the WPF page.
If I use .ShowDialog() - WPF/TextBox is working, but then I'm unable to focus on Excel until WPF page closed.

Any ideas on this one?



Govert van Drimmelen

unread,
Apr 23, 2024, 6:03:32 AMApr 23
to exce...@googlegroups.com

Showing non-modal WPF forms from Excel is tricky.

I think by default Excel captures all keyboard messages from child windows, and WPF does not have built-in behaviour to override this.

 

This is not something I’ve dealt with myself, so I’m only suggesting from what I’ve seen elsewhere.

 

There are three things you can try:

  1. This keyboard problem should not be the case if your WPF form is hosted in a Custom Task Pane.

The CTP can be floated too, and I think if that works it’s the easiest integration.

(If needed you might need to set up a call to EnableModelessKeyboardInterop somehow ?)

 

  1. You can run the WPF form on a separate thread, but then you have to be careful not to interact with the Excel COM object model from that thread – always call ExcelAscynUtil.QueueAsMacro to run code that calls Excel on the main thread.

See this answer for a snippet on how to start the extra thread: https://stackoverflow.com/a/5884085/44264

 

  1. You can try to set up a Windows Forms host for your window, again maybe calling EnableModelessKaybaordInterop is needed (somehow?).

 

Please let us know if you make any progress.

 

-Govert

--
You received this message because you are subscribed to the Google Groups "Excel-DNA" group.
To unsubscribe from this group and stop receiving emails from it, send an email to exceldna+u...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/exceldna/20ed42bb-c4f1-4b99-9d8f-de10c0210a9en%40googlegroups.com.

Message has been deleted
Message has been deleted

Brian Satola

unread,
Apr 23, 2024, 7:11:26 AMApr 23
to Excel-DNA
From what I understand, Excel and WPF operate their own message loops so they compete for focus. You will need to create and launch the WPF form in a new thread so that it can manage its own message loop independently of Excel.

You can try something like this:

    LaunchForm(() =>
    {
        return _formFactory.CreateAsync(choice, title);
    });

    private void LaunchForm(
        Func<Window> formFactory)
    {
        // Retrieve Excel's main window handle in the main thread to avoid cross-thread calls
        var excelHwnd = new IntPtr((int)ExcelDnaUtil.WindowHandle);

        // Start a new STA thread to create and show the WPF Window
        var newWindowThread = new Thread(new ThreadStart(() =>
        {
            // Initialize the Dispatcher at the beginning of the thread
            SynchronizationContext.SetSynchronizationContext(
                new DispatcherSynchronizationContext(Dispatcher.CurrentDispatcher));

            Dispatcher.CurrentDispatcher.Invoke(async () =>
            {
                try
                {
                    // Call the factory method to create the form in the new thread
                    var form = formFactory();

                    // Once the form is created, set its properties and show it
                    _ = new WindowInteropHelper(form)
                    {
                        Owner = excelHwnd // Set the owner of the WPF window to Excel's main window
                    };

                    form.WindowStartupLocation = WindowStartupLocation.CenterScreen;
                    form.Show();

                    // When the form closes, shut down the dispatcher
                    form.Closed += (sender, e) =>
                        Dispatcher.CurrentDispatcher.BeginInvokeShutdown(DispatcherPriority.Background);
                }
                catch (Exception ex)
                {
                    // Since we're now in a different thread, we need to handle exceptions here.
                    MessageBox.Show($"An error occurred: {ex.Message}");
                }
            });

            // Start the Dispatcher Processing
            Dispatcher.Run();
        }));

        // Set the thread's apartment state to STA (Single Thread Apartment)
        newWindowThread.SetApartmentState(ApartmentState.STA);

        // Important: Set the thread to be a background thread so it automatically closes when the application closes
        newWindowThread.IsBackground = true;

        // Start the thread
        newWindowThread.Start();

Brian Satola

unread,
Apr 23, 2024, 7:22:06 AMApr 23
to Excel-DNA
Note: My first two replies got deleted somehow, so not sure what happened there and I got a little lazy on the 3rd try. In my case, my WPF forms have some async init methods that must be called during creation, which is why I'm calling the LaunchForm with an async factory call even though I modified the LaunchForm argument to be non-async to be closer to what you were asking about... Sorry for any confusion. In my case, this seems to work but no guarantees.

A Parmar

unread,
Apr 23, 2024, 12:39:18 PMApr 23
to Excel-DNA
Thank you Govert and Brian for your replies.

For now, what I've tried - adding a button on the initial WPF Window which launches an Input Dialog using .ShowDialog, with the result back to the initial Window.
As I'm using ShowDialog all focus is on the Input Dialog and I'm capturing all the Keyboard strokes too. It's working well in this way for now.

Govert - in the past I have used Windows Forms Hosting WPF - but ideally wanted to only try and use WPF this time round. I'll come back to your suggestions if I need to try something different.

Brian - appreciate your attempts and yes, I can see your first two attempts have been deleted too. I'll take a look at your method if I need to revisit the method I'm currently using.

James Stevenson

unread,
May 2, 2024, 11:27:11 AMMay 2
to Excel-DNA
I have followed this and can get it running, entering data in textboxes and between excel cells works great.

However, the result is my excel add in keeps getting disabled. I think this is in part to a zombie thread. When I look in processes after closing down excel it is still listed.

Once I reopen Microsoft Excel my add-in becomes disabled. I have tested by running the add-in within the main excel thread (albeit without the inputting of data functionality) and my add-in loads ok when excel is opened/closed.Screenshot 2024-05-02 232041.png

Here is my code for clarity.

        private void ShowCalculationScreen(CalculationViewModel calcViewModel)
        {

            var excelHwnd = new IntPtr((int)ExcelDnaUtil.WindowHandle);

            var newWindowThread = new Thread(new ThreadStart(() =>
            {
                SynchronizationContext.SetSynchronizationContext(
                    new DispatcherSynchronizationContext(Dispatcher.CurrentDispatcher));

                Dispatcher.CurrentDispatcher.Invoke(async () =>
                {
                    try
                    {
                        Calculation calcScreen = new Calculation { DataContext = calcViewModel };
                        _ = new WindowInteropHelper(calcScreen)
                        {
                            Owner = excelHwnd
                        };

                        calcScreen.WindowStartupLocation = WindowStartupLocation.CenterScreen;
                        calcScreen.Show();

                        calcScreen.Closed += (sender, e) => {
                            calcScreen.Calculate(excel_application);
                            calcScreen.Close();
                            Dispatcher.CurrentDispatcher.BeginInvokeShutdown(DispatcherPriority.Background);
                        };
                    }
                    catch (Exception ex)
                    {
                        AddInApplication.LogEntry(ex.ToString());
                    }
                });

                Dispatcher.Run();
            }));

            newWindowThread.SetApartmentState(ApartmentState.STA);
            newWindowThread.IsBackground = true;
            newWindowThread.Start();

Brian Satola

unread,
May 2, 2024, 4:07:05 PMMay 2
to Excel-DNA
My thoughts:
- The code I posted was modified from async, and I missed a couple of things:
  • Change .Invoke(async () => to .Invoke(() =>
  • Change .BeginInvokeShutdown(DispatcherPriority.Background); to .InvokeShutdown();
If that does not work:
- Comment out the Calculate and Close lines added to the closed event to see if those are causing an issue. At the very least, the added Close call seems redundant as the window is already in the process of closing.
- Add a log entry after InvokeShutdown to make sure that it reached this point.

And if that does not work, no clue.

James Stevenson

unread,
May 3, 2024, 7:18:42 AMMay 3
to Excel-DNA
Hello,

Thaks for your response but the suggested changes didnt work.

I will keep looking and trying other suggestions. At this point it seems the only option is to only have my WPF window as the active one and disable input into excel until my window is closed.

Govert van Drimmelen

unread,
May 3, 2024, 7:42:02 AMMay 3
to exce...@googlegroups.com

Hi James,

 

The problem is likely that you are talking to the Excel COM object model from your new WPF thread.

You might need to change what happens inside here:

                            calcScreen.Calculate(excel_application);

 

Maybe you could publish a small sample project that we can try to fix up to get the patterns right.

 

-Govert

 

From: exce...@googlegroups.com <exce...@googlegroups.com> On Behalf Of James Stevenson


Sent: Friday, May 3, 2024 1:19 PM
To: Excel-DNA <exce...@googlegroups.com>

Subject: [ExcelDna] Re: ExcelDNA with WPF - Textbox Input

 

Hello,

Thaks for your response but the suggested changes didnt work.

I will keep looking and trying other suggestions. At this point it seems the only option is to only have my WPF window as the active one and disable input into excel until my window is closed.

On Friday, May 3, 2024 at 4:07:05AM UTC+8 Brian Satola wrote:

My thoughts:
- The code I posted was modified from async, and I missed a couple of things:

  • Change .Invoke(async () => to .Invoke(() =>
  • Change .BeginInvokeShutdown(DispatcherPriority.Background); to .InvokeShutdown();

If that does not work:
- Comment out the Calculate and Close lines added to the closed event to see if those are causing an issue. At the very least, the added Close call seems redundant as the window is already in the process of closing.
- Add a log entry after InvokeShutdown to make sure that it reached this point.

 

And if that does not work, no clue.



On Thursday, May 2, 2024 at 11:27:11AM UTC-4 James Stevenson wrote:

I have followed this and can get it running, entering data in textboxes and between excel cells works great.

However, the result is my excel add in keeps getting disabled. I think this is in part to a zombie thread. When I look in processes after closing down excel it is still listed.

Once I reopen Microsoft Excel my add-in becomes disabled. I have tested by running the add-in within the main excel thread (albeit without the inputting of data functionality) and my add-in loads ok when excel is opened/closed.

--

You received this message because you are subscribed to the Google Groups "Excel-DNA" group.
To unsubscribe from this group and stop receiving emails from it, send an email to exceldna+u...@googlegroups.com.

Message has been deleted
Message has been deleted

James Stevenson

unread,
May 8, 2024, 6:27:06 AMMay 8
to Excel-DNA
Yes that appears to be the problem, as soon as excel_application gets called to do anything the problem occurs. The code below fails.


        private void ShowCalculationScreen(CalculationViewModel calcViewModel)
        {
            var excelHwnd = new IntPtr((int)ExcelDnaUtil.WindowHandle);

            var newWindowThread = new Thread(new ThreadStart(() =>
            {
                Dispatcher.CurrentDispatcher.Invoke(() =>

                {
                    // Initialize the Dispatcher at the beginning of the thread
                    SynchronizationContext.SetSynchronizationContext(
                        new DispatcherSynchronizationContext(Dispatcher.CurrentDispatcher));

                    try
                    {
                        Calculation calcScreen = new Calculation { DataContext = calcViewModel };
                        _ = new WindowInteropHelper(calcScreen)
                        {
                            Owner = excelHwnd
                        };

                        CalculationViewModel viewModel = (CalculationViewModel)calcScreen.DataContext;
                        calcScreen.WindowStartupLocation = WindowStartupLocation.CenterScreen;

                        calcScreen.Closed += (sender, e) =>
                        {
                            Dispatcher.CurrentDispatcher.InvokeShutdown();
                        };

                        viewModel.CloseViewEvent += new CalculationViewModel.CloseViewEventHandler(() =>
                        {
                            calcScreen.Close();
                            Dispatcher.CurrentDispatcher.InvokeShutdown();
                        });

                        calcScreen.ShowDialog();

                        // add text into cell for testing purposes
                        Range cell = excel_application.Range["A3"];
                        cell.Value2 = "James";

                    }
                    catch (Exception ex)
                    {
                        AddInApplication.LogEntry(ex.ToString());
                    }
                });
            }));

            newWindowThread.SetApartmentState(ApartmentState.STA);
            newWindowThread.IsBackground = true;
            newWindowThread.Start();
        }

But if I simply open and close the dialog it works fine. I need to use excel_application to get values from excel cells and also return the outputs to the cells as well.

How should I be calling the functions in - Application excel_application = (Application)ExcelDnaUtil.Application;

Govert van Drimmelen

unread,
May 8, 2024, 6:44:54 AMMay 8
to exce...@googlegroups.com

The main rule is to ensure all access to the Excel COM object model is from the main Excel thread, is a safe context.

 

You can try something like this:

 

ExcelAsyncUtil.QueueAsMacro( () =>

{

                        // add text into cell for testing purposes

                        var app = (Microsoft.Office.Interop.Excel.Application)ExcelDnaUtil.Application;
                        Range cell = app.Range["A3"];
                        cell.Value2 = "James";

});

 

The inner code will run on the main thread, when Excel is ‘ready’.

I’m not sure where your captured excel_application variable came from – it might be fine as well.

 

Also remember that the user might have changed to a different workbook or worksheet behind your form, so the exact meaning of app.Range etc. or any access back to the sheet is a bit fragile if you intend for it to be the sheet that was active when the non-modal dialog was launched.

Trevor Philips

unread,
May 14, 2024, 6:04:41 AMMay 14
to Excel-DNA
There's a workaround: temporarily setting 'Application.Interactive = false' can disable Excel from receiving input, but remember to change it back to true promptly, otherwise Excel may appear unresponsive.

Michael

unread,
May 18, 2024, 1:21:28 AMMay 18
to Excel-DNA
The second solution worked for my requirements. I initiated a thread and encapsulated all Excel COM interactions within ExcelAsyncUtil.QueueAsMacro. 
Reply all
Reply to author
Forward
0 new messages