Password prompt when closing Excel

Dec 19, 2013 at 11:13 AM
Hi,

I have a custom addin registered in my excel application (2007).
My vba project is protected.
When closing excel, a password prompt is displayed.
It happens only when I call ExcelDnaUtil.Application.

Here is some code to reproduce the problem:

xll:
    [ComVisible(true)]
    public class Test: IExcelAddIn
    {
        ...
        private object app;
        public Test()
        {
                app = ExcelDnaUtil.Application;
        }
       ...
    }
vba:
Private Sub Workbook_Open()
    Application.RegisterXLL ("D:\...\Test-AddIn-packed.xll")
    UserForm1.Show vbModeless
    Unload UserForm1
End Sub
I think it has someting to do with this: http://support.microsoft.com/kb/280454, so I tried to release app using Marshal.ReleaseComObject, but it doesn't change anything.

Any idea?

Thanks
Coordinator
Dec 22, 2013 at 7:40 PM
I tried to reproduce the behaviour you describe with my Excel 2013, but could not see any problems.
I'll also try on Excel 2010 when I get a chance.

Are you able to test on a newer Excel version?

-Govert
Dec 23, 2013 at 1:10 PM
Yes, I tried on Excel 2010, it's ok. But unfortunately, I have to use Excel 2007...
Dec 23, 2013 at 7:10 PM
Edited Dec 23, 2013 at 7:11 PM
Password prompts as stated in your scenario occur because the underlying Excel workbook is password-protected; this behavior is unrelated to Excel DNA or other API's.

I suggest that once you have a reference to the application and then the desired workbook (and worksheets), to call the Unprotect method that prompts the user to enter the password. I typically wrap such one-time workbook management operations in a single Register method and then associate it with an event callback into the VSTO object provided by ExcelDnaUtil.Application once my Excel DNA add-in has loaded.
if (Application != null)
{
  Application.WorkbookOpen += new AppEvents_WorkbookOpenEventHandler(excel_WorkbookOpen);
  Application.WorkbookBeforeClose += new AppEvents_WorkbookBeforeCloseEventHandler(excel_WorkbookBeforeClose);
  Application.WorkbookActivate += new AppEvents_WorkbookActivateEventHandler(excel_WorkbookActivate);

   foreach (Workbook workbook in workbooks)
   {
     Register(workbook);
   }
}

void Register(Workbook workbook)
{
 // register workbook and sheet level callbacks

 // check for password 
 foreach(Worksheet worksheet in workbook.Worksheets)
 {
   worksheet.Unprotect(Type.Missing);
 }
}
Then your password-protected workbooks with Excel DNA features (UDF's, macros) should function as desired. Hope this helps.

-BT-
Dec 27, 2013 at 2:00 PM
It occurs because the Vb Project is password protected.
I found a solution: unlock the vb project using sendkeys just before closing the application... It's not ideal, but it works.

So if somedy has a better solution... :)