Excel.WorkbookBeforeClose behaviour

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Tim Marsden - 25 Sep 2003 17:33 GMT

Hello,

I have written a Addin for Excel using VB.NET.
I am trapping the WorkbookBeforeClose event and setting the Cancel parameter
to True, to prevent the Workbook from closing. However the Workbook still
closes.

Private Sub mobjExcel_WorkbookBeforeClose(ByVal Wb As Excel.Workbook, ByRef
Cancel As Boolean) Handles mobjExcel.WorkbookBeforeClose

Cancel = True

End Sub

Any suggestions.

Tim

Reply to this Message

Tim Marsden - 25 Sep 2003 17:43 GMT

Sorry, forgot to say I am using EXCEL 2000 and framework 1.1

> Hello,
>
[quoted text clipped - 13 lines]
>
> Tim

Reply to this Message

Jian-Shen Lin[MS] - 26 Sep 2003 03:32 GMT

Hi Tim,

There is detail sample in following KB when we handle events for Excel by
using Visual Basic .NET.You can refer to the article for details.

HOW TO: Handle Events for Excel by Using Visual Basic .NET ID: 302814
http://support.microsoft.com/default.aspx?scid=kb;EN-US;Q302814

There also an article for the following error message:
An unhandled exception of type 'System.InvalidCastException' occurred in
interop.excel.dll
Additional information: No such interface supported

Q316653 - PRB: Error Handling Excel Events from VB .NET or C# .NET Client
To get this article from the Web - click link on the next line
http://support.microsoft.com/default.aspx?scid=kb;EN-US;Q316653

Thanks

Jian Shen

This posting is provided "AS IS" with no warranties, and confers no rights.

Reply to this Message

Tim Marsden - 26 Sep 2003 10:50 GMT

Hi Thanks for the reply

I am aware of the articles you have suggested.
I have followed all the recommendations in them.
My Addin use to work, since then I have upgraded to .NET 1.1 and to VS 2003.
I don't know if this has effected it.

The excel events did not use to fire, until I amended to interop.excel.dll.
Now they fire will the auto created interop.excel.dll. Has something
changed?

The Cancel parameter does not work with the amended or the standard
ionterop.excel.dll.

I am a bit lost, can anyone recommend good information on excel interop and
.NET. MSDN is a little vague.

regards Tim

> Hi Tim,
>
[quoted text clipped - 18 lines]
>
> This posting is provided "AS IS" with no warranties, and confers no rights.

Reply to this Message

Tim Marsden - 29 Sep 2003 19:44 GMT

Hello, thanks

I am aware of the articles mentioned. The Event does fire but setting the
cancel property to True still closes the workbook.

Tim

> Hi Tim,
>
[quoted text clipped - 18 lines]
>
> This posting is provided "AS IS" with no warranties, and confers no rights.

Reply to this Message

Tim Marsden - 29 Sep 2003 20:31 GMT

HI heres some code that does not work

Imports Microsoft.Office.Core

imports Extensibility

imports System.Runtime.InteropServices

#Region " Read me for Add-in installation and setup information. "

' When run, the Add-in wizard prepared the registry for the Add-in.

' At a later time, if the Add-in becomes unavailable for reasons such as:

' 1) You moved this project to a computer other than which is was originally
created on.

' 2) You chose 'Yes' when presented with a message asking if you wish to
remove the Add-in.

' 3) Registry corruption.

' you will need to re-register the Add-in by building the MyAddin2Setup
project

' by right clicking the project in the Solution Explorer, then choosing
install.

#End Region

<GuidAttribute("DD27A750-213D-4BC5-9803-11A637801A0F"),
ProgIdAttribute("MyAddin2.Connect")> _

Public Class Connect

Implements Extensibility.IDTExtensibility2

Dim WithEvents applicationObject As Excel.Application

dim addInInstance as object

Public Sub OnBeginShutdown(ByRef custom As System.Array) Implements
Extensibility.IDTExtensibility2.OnBeginShutdown

End Sub

Public Sub OnAddInsUpdate(ByRef custom As System.Array) Implements
Extensibility.IDTExtensibility2.OnAddInsUpdate

End Sub

Public Sub OnStartupComplete(ByRef custom As System.Array) Implements
Extensibility.IDTExtensibility2.OnStartupComplete

End Sub

Public Sub OnDisconnection(ByVal RemoveMode As
Extensibility.ext_DisconnectMode, ByRef custom As System.Array) Implements
Extensibility.IDTExtensibility2.OnDisconnection

End Sub

Public Sub OnConnection(ByVal application As Object, ByVal connectMode As
Extensibility.ext_ConnectMode, ByVal addInInst As Object, ByRef custom As
System.Array) Implements Extensibility.IDTExtensibility2.OnConnection

applicationObject = application

addInInstance = addInInst

End Sub

Private Sub applicationObject_WorkbookBeforeClose(ByVal Wb As
Excel.Workbook, ByRef Cancel As Boolean) Handles
applicationObject.WorkbookBeforeClose

If MsgBox("Close", MsgBoxStyle.YesNo) = MsgBoxResult.No Then

Cancel = True

End If

End Sub

End Class

> Hello, thanks
>
[quoted text clipped - 26 lines]
> > This posting is provided "AS IS" with no warranties, and confers no
> rights.

Reply to this Message

Jian-Shen Lin[MS] - 01 Oct 2003 08:17 GMT

Hi Tim,

Microsoft supplies several primary interop assemblies (PIAs) that contain
the official description of the most commonly used Microsoft Office XP
type libraries. These Microsoft Office XP PIAs make interoperability
easier between managed code and Office XP COM type libraries.

You can download new component if you use Offfice XP.

Office XP Primary Interop Assemblies
http://msdn.microsoft.com/library/default.asp?url=/downloads/list/office.asp

Thanks

Jian Shen

This posting is provided "AS IS" with no warranties, and confers no rights.

Reply to this Message

Tim Marsden - 02 Oct 2003 15:12 GMT

Thanks
However I must use Excel 2000

Tim

> Hi Tim,
>
[quoted text clipped - 6 lines]
>
> Office XP Primary Interop Assemblies

http://msdn.microsoft.com/library/default.asp?url=/downloads/list/office.asp

> Thanks
>
> Jian Shen
>
> This posting is provided "AS IS" with no warranties, and confers no rights.

Reply to this Message

"Lori Turner [MSFT]" - 08 Oct 2003 23:25 GMT

Hi Tim,

The problem with the cancel parameter being ignored is a known issue that
has surfaced with VS.NET 2003. The problem is due to the Office type
libraries and how these Cancel parameters are marked in the type libraries.
The olb's mark these Cancel parameters as [in] parameters (rather than
[in][out]) and CLR 1.1 enforces this. CLR 1.0 did not enforce this so we
didn't have the problem with 1.0.

The only workaround at this time is to create your own override for the
event class. I've got an example for Word (it has the same problem);
hopefully, you can examine this sample code and see how it should look for
Excel.

private Word.Application oWord;
private WordAppEvents10.WordAppEventHelper oEvents;

private void button1_Click(object sender, System.EventArgs e)
{
    oWord = new Word.Application();
    oWord.Visible=true;
    // Call Advise to sink up the connection.
    oEvents = new WordAppEvents10.WordAppEventHelper();
    oEvents.SetupConnection(oWord);
}

WordEventHelper.cs:
====================

using System;
using System.Runtime.InteropServices;
using Word = Microsoft.Office.Interop.Word;

namespace WordAppEvents10
{
    [InterfaceType(ComInterfaceType.InterfaceIsIDispatch),
    GuidAttribute("000209FE-0000-0000-C000-000000000046")]
    public interface DWordApplicationEvents10
    {
        [DispId(0x00000001)] void Startup();
        [DispId(0x00000002)] void Quit();
        [DispId(0x00000003)] void DocumentChange();
        [DispId(0x00000004)] void DocumentOpen(Word.Document doc);
        [DispId(0x00000006)] void DocumentBeforeClose(Word.Document doc, ref bool
Cancel);
        [DispId(0x00000007)] void DocumentBeforePrint(Word.Document doc, ref bool
Cancel);
        [DispId(0x00000008)] void DocumentBeforeSave(Word.Document doc, ref bool
SaveAsUI, ref bool Cancel);
        [DispId(0x00000009)] void NewDocument(Word.Document doc);
        [DispId(0x0000000a)] void WindowActivate(Word.Document doc, Word.Window
wn);
        [DispId(0x0000000b)] void WindowDeactivate(Word.Document doc, Word.Window
wn);
        [DispId(0x0000000c)] void WindowSelectionChange(Word.Selection sel);
        [DispId(0x0000000d)] void WindowBeforeRightClick(Word.Selection sel, ref
bool Cancel);
        [DispId(0x0000000e)] void WindowBeforeDoubleClick(Word.Selection sel, ref
bool Cancel);
        [DispId(0x0000000f)] void EPostagePropertyDialog(Word.Document doc);
        [DispId(0x00000010)] void EPostageInsert(Word.Document doc);
        [DispId(0x00000011)] void MailMergeAfterMerge(Word.Document doc,
Word.Document DocResult);
        [DispId(0x00000012)] void MailMergeAfterRecordMerge(Word.Document doc);
        [DispId(0x00000013)] void MailMergeBeforeMerge(Word.Document doc, int
StartRecord, int EndRecord, ref bool Cancel);
        [DispId(0x00000014)] void MailMergeBeforeRecordMerge(Word.Document doc,
ref bool Cancel);
        [DispId(0x00000015)] void MailMergeDataSourceLoad(Word.Document doc);
        [DispId(0x00000016)] void MailMergeDataSourceValidate(Word.Document doc,
ref bool Handled);
        [DispId(0x00000017)] void MailMergeWizardSendToCustom(Word.Document doc);
        [DispId(0x00000018)] void MailMergeWizardStateChange(Word.Document doc,
ref int FromState, ref int ToState, ref bool Handled);
        [DispId(0x00000019)] void WindowSize(Word.Document doc, Word.Window wn);
    }

    public class WordAppEventHelper : DWordApplicationEvents10, IDisposable
    {
        public WordAppEventHelper()
        {
            m_oConnectionPoint = null;
            m_Cookie = 0;
        }

        public void Startup()
        {System.Diagnostics.Debug.WriteLine("Startup");}

        public void Quit()
        {System.Diagnostics.Debug.WriteLine("Quit");}

        public void DocumentChange()
        {System.Diagnostics.Debug.WriteLine("DocumentChange");}

        public void DocumentOpen(Word.Document doc)
        {System.Diagnostics.Debug.WriteLine("DocumentOpen");}

        public void DocumentBeforeClose(Word.Document doc, ref bool Cancel)
        {
            System.Diagnostics.Debug.WriteLine("DocumentBeforeClose");
            Cancel = true;
        }

        public void DocumentBeforePrint(Word.Document doc, ref bool Cancel)
        {System.Diagnostics.Debug.WriteLine("DocumentBeforePrint");}

        public void DocumentBeforeSave(Word.Document doc, ref bool SaveAsUI, ref
bool Cancel)
        {System.Diagnostics.Debug.WriteLine("DocumentBeforeSave");}

        public void NewDocument(Word.Document doc)
        {System.Diagnostics.Debug.WriteLine("NewDocument");}

        public void WindowActivate(Word.Document doc, Word.Window wn)
        {System.Diagnostics.Debug.WriteLine("WindowActivate");}

        public void WindowDeactivate(Word.Document doc, Word.Window wn)
        {System.Diagnostics.Debug.WriteLine("WindowDeactivate");}

        public void WindowSelectionChange(Word.Selection sel)
        {System.Diagnostics.Debug.WriteLine("WindowSelectionChange");}

        public void WindowBeforeRightClick(Word.Selection sel, ref bool Cancel)
        {System.Diagnostics.Debug.WriteLine("WindowBeforeRightClick");}

        public void WindowBeforeDoubleClick(Word.Selection sel, ref bool Cancel)
        {System.Diagnostics.Debug.WriteLine("WindowBeforeDoubleClick");}

        public void EPostagePropertyDialog(Word.Document doc)
        {System.Diagnostics.Debug.WriteLine("EPostagePropertyDialog");}

        public void EPostageInsert(Word.Document doc)
        {System.Diagnostics.Debug.WriteLine("EPostageInsert");}

        public void MailMergeAfterMerge(Word.Document doc, Word.Document
DocResult)
        {System.Diagnostics.Debug.WriteLine("MailMergeAfterMerge");}

        public void MailMergeAfterRecordMerge(Word.Document doc)
        {System.Diagnostics.Debug.WriteLine("MailMergeAfterRecordMerge");}

        public void MailMergeBeforeMerge(Word.Document doc, int StartRecord, int
EndRecord, ref bool Cancel)
        {System.Diagnostics.Debug.WriteLine("MailMergeBeforeMerge");}

        public void MailMergeBeforeRecordMerge(Word.Document doc, ref bool Cancel)
        {System.Diagnostics.Debug.WriteLine("MailMergeBeforeRecordMerge");}

        public void MailMergeDataSourceLoad(Word.Document doc)
        {System.Diagnostics.Debug.WriteLine("MailMergeDataSourceLoad");}

        public void MailMergeDataSourceValidate(Word.Document doc, ref bool
Handled)
        {System.Diagnostics.Debug.WriteLine("MailMergeDataSourceValidate");}

        public void MailMergeWizardSendToCustom(Word.Document doc)
        {System.Diagnostics.Debug.WriteLine("MailMergeWizardSendToCustom");}

        public void MailMergeWizardStateChange(Word.Document doc, ref int
FromState, ref int ToState, ref bool Handled)
        {System.Diagnostics.Debug.WriteLine("MailMergeWizardStateChange");}

        public void WindowSize(Word.Document doc, Word.Window wn)
        {System.Diagnostics.Debug.WriteLine("WindowSize");}

        private UCOMIConnectionPoint m_oConnectionPoint;
        private int m_Cookie;

        public void SetupConnection(Word.Application app)
        {
            if (m_Cookie != 0) return;

            // GUID of the DIID_ApplicationEvents dispinterface.
            Guid guid = new Guid("{000209FE-0000-0000-C000-000000000046}");

            // QI for IConnectionPointContainer.
            UCOMIConnectionPointContainer oConnPointContainer =
(UCOMIConnectionPointContainer)app;

            // Find the connection point and advise...
            oConnPointContainer.FindConnectionPoint(ref guid, out
m_oConnectionPoint);
            m_oConnectionPoint.Advise(this, out m_Cookie);
        }

        public void RemoveConnection()
        {
            if (m_Cookie != 0)
            {
                m_oConnectionPoint.Unadvise(m_Cookie);
                m_oConnectionPoint = null;
                m_Cookie = 0;
            }
        }

        public void Dispose(){RemoveConnection();}
    }
}

Note that the GUID used for this event helper is the same as the GUID used
for Word's ApplicationEvents2 dispinterface; you can look up the GUIDs
using the OLE COM Object Viewer (from the Tools menu in VS.NET).

Lori Turner
Microsoft Developer Support

This posting is provided "AS IS" with no warranties, and confers no rights.

--------------------

>> From: "Tim Marsden" <TM@UK.COM>
>> Subject: Excel.WorkbookBeforeClose behaviour
[quoted text clipped - 7 lines]
>> Newsgroups: microsoft.public.dotnet.framework.interop
>> NNTP-Posting-Host: host213-122-10-48.in-addr.btopenworld.com

213.122.10.48

>> Path: cpmsftngxa06.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFTNGP11.phx.gbl
>> Xref: cpmsftngxa06.phx.gbl

microsoft.public.dotnet.framework.interop:19067

>> X-Tomcat-NG: microsoft.public.dotnet.framework.interop
>>
>> Hello,
>>
>> I have written a Addin for Excel using VB.NET.
>> I am trapping the WorkbookBeforeClose event and setting the Cancel

parameter

>> to True, to prevent the Workbook from closing. However the Workbook still
>> closes.
>>
>> Private Sub mobjExcel_WorkbookBeforeClose(ByVal Wb As Excel.Workbook,

ByRef

>> Cancel As Boolean) Handles mobjExcel.WorkbookBeforeClose
>>
[quoted text clipped - 5 lines]
>>
>> Tim

Reply to this Message

Excel.WorkbookBeforeClose behaviour

Free Magazines