XlCall.Excel inside application_WorkbookAfterSave

[Terry Aney]

Inside a Application_WorkbookAfterSave event handler, is it surprising that I can not use XlCall.Excel( XlCall.xlfSelection )?

[Govert van Drimmelen]

No, using the C API in a COM callback is probably not a good idea. Just use the COM object model there.

-Govert

On Fri, 7 Feb 2025, 23:06 Terry Aney, <terry...@gmail.com> wrote:

Inside a Application_WorkbookAfterSave event handler, is it surprising that I can not use XlCall.Excel( XlCall.xlfSelection )?

--
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 visit https://groups.google.com/d/msgid/exceldna/57c0248a-6f33-489f-b484-b64db097d87dn%40googlegroups.com.

[Terry Aney]

Well, I have an extension against an ExcelReference object that gives me 'full' address (book, sheet, cell) which I need.  Could write one as well for Range, but I originally was forced (due to async) to call this code inside a QueueAsMacro and it worked.  I was able to get rid of async requirement so just dropped code inside handler and then got this error.  So, to resolve, I just dropped in a QueueAsMacro.  That's ok?

[Govert van Drimmelen]

Yes, that's OK.

-Govert

To view this discussion visit https://groups.google.com/d/msgid/exceldna/6758139e-9911-4479-89b8-10d011020127n%40googlegroups.com.

[Terry Aney]

Hmm.  Periodically getting the attached error.  So my code looks like following:

    private void Application_WorkbookAfterSave( MSExcel.Workbook wb, bool Success )

    {

        ExcelAsyncUtil.QueueAsMacro( () => {

            try

            {

                ProcessSaveHistory( wb );

            }

            catch ( Exception ex )

            {

                LogException( ex, "Unable to process save history." );

            }

        } );

    }

Then, inside ProcessSaveHistory, I have my call to XlCall.Excel(), but first flow of that function shows a Windows.Forms.Form dialog to gather some info...

    private void ProcessSaveHistory( MSExcel.Workbook workbook )

    {

        using var saveHistory = new SaveHistory(

            this,

            workbook,

            WorkbookState,

            apiService,

            GetWindowConfiguration( nameof( SaveHistory ) )

        );

        var owner = new NativeWindow();

        owner.AssignHandle( new IntPtr( application.Hwnd ) );

        var info = saveHistory.GetInfo( owner );

And finally, one of the first lines of GetInfo() is:

var sheets = workbook.Worksheets.Cast<MSExcel.Worksheet>();

Which is ultimately what is throwing the exception shown in the attached image.

1) Any ideas what might be causing this and a resolution/workaround?

2) Any idea why it **appears** to have bypassed my try/catch and instead was directly handled from ExcelIntegration.RegisterUnhandledExceptionHandler( UnhandledExceptionHandler )? ( could be wrong on that, but would have expected to see my message "Unable to process SaveHistory".

As usage as picked up on my addin, seeing more and more strange (presummably threading issue) behaviors - like the addin simply not responding to Excel events and showing Windows.Forms.Form dialogs when it normally does/should.  Any suggestions on chasing those down are appreciated, but going to start by trying to wire in Serilog/structured logging that will do some tracing hopefully outside any constraints proper execution thread.

[Terry Aney]

After typing my message, I forgot to attach the error.

[Govert van Drimmelen]

Hi Terry,

The error you see might be happening is your COM code is running on a thread that is not the main thread.

I suggest adding a check before the problem location to confirm that "Thread.CurrentThread.ManagedThreadId == 1).

If you're not on the main thread, reliable COM interop is not possible.

The fact that your exception is not being caught by your exception handler in the QueueAsMacro delegate in Application_WorkbookAfterSave makes me also think it is no longer running on the main thread (which the QueueAsMacro call would ensure). 

A different possible source of trouble, if the threading is right, is that your workbook is no longer 'alive' by the time the QueueAsMacro code runs.

For example, if the event handler might be called as part of closing the workbook, and QueueAsMacro will only be scheduled after the current work (closing the workbook) is complete.

So even though you have a reference to a Workbook COM object, that backing workbook has actually been closed by the time the QueueAsMacro runs, and accessing it will fail.

Anyway, those are some ideas from what you have shown.

-Govert

[Terry Aney]

Right.  As you said I'd expect QueueAsMacro to get me on the right thread.  Is there a way I can 'check' if `wb` is valid by the time my macro runs?  Just look in 'current' workbooks for the actual name string that I capture before QueueAsMacro?  Is that the best course of action?

I'll check both suggestions and report back.

[Govert van Drimmelen]

QueueAsMacro run the delegate on the main thread, but if you show a dialog or something, that might not be running on the main thread.

But in your case I think the problem is the workbook being closed already.

 

Capturing the name string and then looking for it in the COM collections again when the delegate runs is a good plan.

 

-Govert

To view this discussion visit https://groups.google.com/d/msgid/exceldna/83de5778-6ade-4324-985b-a19eb7b6d578n%40googlegroups.com.