none
Excel 2003 doc-level customization with Excel 2007 RRS feed

  • Question

  •  

    Hello. I have developed an Excel 2003 document-level customization using VSTO 2005. In our scenario, the customization is only used in-house to process Excel spreadsheets edited by end-users -- the end users don't actually use the customization. Because of this, I developed a program that will add/remove the document-level customization to specified Excel documents. This part of the project works great. Our problems occur when an end-user using Excel 2007 tries to open the document. I understand that Excel 2003 document-level customizations aren't guaranteed to work with Excel 2007 as explained here. In my situation though, the customization has already been removed, so I assumed that it would work fine with Excel 2007.

     

    Let me explain exactly what happens:

     

    1. One of our in-house bookkeepers uses the add/remove program to remove the document-level customization from a correctly working Excel 2003 workbook.

    2. The bookkeeper then emails the Excel 2003 workbook (without customization) to an end-user who then edits the document using Excel 2007 (in compatibility mode). 

    3. When the end-user tries to save the workbook, he/she is notified of the following: "Uninitialized ActiveX controls cannot be transferred to the selected file format. The controls will be lost if you continue. Location: 'Sheet1'". The end-user is given the choice to 'Continue' or 'Cancel'.

    4. When the end-user selects 'Continue', the Excel workbook is successfully saved as a Microsoft Office Excel 97-2003 Worksheet. The end-user then emails the Excel document back to our in-house bookkeeper.

    5. Upon receiving the document, the bookkeeper then adds the customization back to the Excel document, which seems to complete successfully.

    6. When the bookkeeper tries to open the Excel document (now with document-level customization added), he/she receives the following error: "The customization assembly could not be found or loaded. You can still edit and save the document. Contact your administrator or the author of this document for further assistance." When I click the 'Details' button to find out more, it's completely blank.

     

    I should also mention that I tried saving the end-user document as an Excel 2007 workbook, but that did not work as well.

     

    I know this is a relatively lengthy and complex problem, but I would greatly appreciate anyone who is willing to take the time to help me figure out how to proceed.

     

    Thanks a ton,

    Kit 

     

    Thursday, April 17, 2008 3:04 AM

Answers

  •  

    Well, after much frustration and prayer, I figured out a solution. Here's what I did:

     

    Initially, I managed to delete every "shape" from the workbook. After doing that, I was able to save the workbook in Excel 2007 without any errors. But after adding the customization back to the workbook, I received the same error: "Element not found." I realized this was due to the fact that I had deleted every shape from the document - including the windows form controls. The error was a result of a failure to initialize the controls.

     

    Then, I remembered reading about being able to add windows form controls dynamically at runtime. So I tried it out: I added the required buttons during runtime (this required quite a bit of re-coding) and then called the Workbook.RemoveCustomization method to remove the rutime storage control. I also realized (through trial and error), that I needed to remove the dynamic controls during runtime as well, because Excel 2007 didn't like them either. Once I implemented all of that, I was able to save & edit the workbook in Excel 2007, add the customization back to the workbook, and retain all of the customization's functionality. Yeah!!!

     

    Thank you Mary and Cindy for your help!

     

    Kit

    Thursday, May 8, 2008 8:50 AM

All replies

  • Kit, could you show us the code that's removing the customization, please? I'm guessing, from the message the end-user is reporting, that the runtime storage control is not being removed from the worksheet...

     

    Thursday, April 17, 2008 7:48 AM
    Moderator
  • Cindy,

     

    Here is the code that I'm using to remove the customization:

     

    if (ServerDocument.IsCustomized(file))

    {

    ServerDocument.RemoveCustomization(file);

    }

     

    What more should I be doing to remove the runtime storage control?

     

    Thanks,

    Kit

    Thursday, April 17, 2008 8:14 AM
  • According to the article here, "The Microsoft.Office.Tools.Word.Document.RemoveCustomization and Microsoft.Office.Tools.Excel.Workbook.RemoveCustomization methods also remove the Runtime Storage Control from the document."

     

    Anyone have anymore thoughts regarding my issue?

    Monday, April 21, 2008 2:06 AM
  •  

    Does anyone have any ideas???
    Monday, April 28, 2008 2:46 AM
  • I found the following post interesting: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=271894&SiteID=1

     

    In the above post, Ben had to do a lot more than use the "ServerDocument.RemoveCustomization" function to remove the Runtime Storage Control from his Excel worbook. I tried using his code but received an exception when I tried to do the following:

     

    _workbookData.VBProject.References.Remove(_workbookData.VBProject.References.Item(i));

     

    The exception I received was "Can't remove default reference."I had hoped that this would be my solution, but it doesn't look very promising.

     

    It blows my mind that Microsoft doesn't provide a better way to remove the managed code extension from a document (or that no one else seems to know how).

    Tuesday, April 29, 2008 7:42 AM
  • hi Kit,

     

    the document at http://msdn2.microsoft.com/en-us/library/8fza57cs.aspx is correct.  you use the Microsoft.Office.Tools.Word.Document.RemoveCustomization (for Word) or Microsoft.Office.Tools.Excel.Workbook.RemoveCustomization (for Excel) methods when you remove a customization from inside of a document.  these methods remove the runtime storage control.

     

    if you create an external app to add/remove VSTO customizations, you use Microsoft.VisualStudio.Tools.Applications.Runtime.ServerDocument.RemoveCustomization which does not remove the runtime storage control.

     

    did you create a console app or WinForms app which calls Microsoft.VisualStudio.Tools.Applications.Runtime.ServerDocument.RemoveCustomization or does your bookkeeper use a VSTO Office solution to add/remove the customization?

     

    what happens if the bookkeeper removes the customization and then immediately adds the customization back?  does the customization work? 

     

    what happens if the end users work with Excel 2003 to make their changes before handing it back to the bookkeeper.  does your add/remove customization code work in that scenario? 

     

    m.

    Tuesday, April 29, 2008 10:30 PM
    Moderator
  • Mary,

     

    Thank you very much for the reply.

     

    You are correct -- after a re-reading of the Microsoft document, the Microsoft.Office.Tools.Excel.Workbook.Remove Customization is the only method that removes the runtime storage control, and not the ServerDocument.RemoveCustomization. Unfortunately, the Microsoft.Office.Tools.Excel.Workbook.RemoveCustomization method must be called within the ThisWorkbook class, and I don't see how that could me implemented in my situation. Please let me know otherwise.

     

    I created a console app that calls the Microsoft.VisualStudio.Tools.Application.Runtime.ServerDocument.RemoveCustomization method. If the bookkeeper removes and immediately adds the the customization back, it works perfectly. If an end user edits the workbook with Excel 2003, hands it in to the bookkeeper, and then the bookkeeper adds the customization, it also works perfectly. The problem only occurs when an end user tries to work with the document in Excel 2007.

     

    Thank you for your help. Any advice is greatly appreciated.

     

    Kit

     

     

    Wednesday, April 30, 2008 2:48 AM
  • hi Kit,

     

    unfortunately, the VSTO2005 runtime was never designed for Office 2007, because the software didn't exist at the time.  it's clear that Excel 2007 in compatibility mode isn't truly compatible. 

     

    the next best step is to go back to the Word object model (where i'm well outside of my area).  you would need to delete the runtime storage control through Word.  the runtime storage control is an ActiveX control which you access as through the Shapes collection inside of Word.  a good place to start might be Using ActiveX Controls on Word Documents, Shapes Collection Object, and Shape.Delete Method.

     

    m.

    Wednesday, April 30, 2008 5:04 AM
    Moderator
  • Mary, thanks again for the quick reply. As much as I wish it weren't true, I understand what you're saying. I've tried to remove the shapes as detailed here, but with no success thus far. I will try to do more research.

     

    This leads me to a couple of questions:

    1.) I also have VS2008 with VSTO 3 -- is there any way for me to implement VSTO 3 in a way that could work with both Excel 2003 and Excel 2007?

    2.) Back to the Microsoft.Office.Tools.Excel.Workbook.RemoveCustomization method -- is there a way for me to call this in a way that automatically removes the customization and runtime storage control every time that the document-level customization is used? In other words, the customization would automatically remove itself after every time that one of our bookkeepers used it. If feasible, this might be an option, because most times the bookkeepers only need to run it once. 

     

    Kit

    Wednesday, April 30, 2008 6:40 AM
  • 1. you can create a solution for both Office 2003 and Office 2007, but you have to separate the code that is specific to a certain version.  you can find more information at Creating Solutions for Multiple Versions of Microsoft Office or through a downloadable podcast at http://blogs.msdn.com/vsto/archive/2008/04/17/office-development-audio-series-1-is-now-available-harry-miller.aspx.

    your deployment also becomes twice as complicated: Office 2003 solutions work with the VSTO 2.0 runtime from VSTO 2005 SE and .NET FX 2.0, while Office 2007 solutions created with VS2008 use the VSTO 3.0 runtime and .NET FX 3.5.  Office 2003 solutions are deployed with Windows Installer and trusted with caspol scripts, while Office 2007 solutions created with VS2008 use ClickOnce to secure their solutions (regardless of whether you use Windows Installer or ClickOnce to distribute your solution).

     

    2. the Workbook.RemoveCustomization method is designed to be called from inside a VSTO solution. there are two exception messages that you can catch in case things go awry.

     

    m.

    Wednesday, April 30, 2008 5:01 PM
    Moderator
  •  

    Mary, thanks for the info.

     

    Currently, I'm not so sure that developing separate solutions for Office 2003 and Office 2007 is really a viable option for me. That being said, I need to figure out how to remove the runtime storage controls from the excel document. Here's where I'm at in that process:

     

    a.) I tried to remove the customization internally by calling the Worbook.RemoveCustomization method from within ThisWorkbook_Shutdown, but this had no effect. Where else can I call the Workbook.RemoveCustomization from? Is there anything else I need to do to have this work?

     

    b.) I tried to remove the customization externally by using the following code:

     

    ExcelApplication _excel;

    Workbook _workbookData;

    _excel = new ExcelApplication();

    _workbookData = _excel.Workbooks.Open(file, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing,

    Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);

    foreach (Microsoft.Office.Interop.Excel._Worksheet y in _workbookData.Application.Worksheets)

    {

    Shapes shapes = y.Shapes;

    if (shapes.Count == 0) break;

    for (int i = 1; i <= shapes.Count; i++)

    {

    try

    {

    object i_obj = i;

    shapes.Item(i_obj).Delete();

    }

    catch

    {

    //do nothing. This is not a Runtime Storage Control

    }

    }

    }

    _workbookData.Save();

    _workbookData.Close(false, Type.Missing, Type.Missing);

    _excel.Application.Quit();

    _workbookData = null;

    _excel = null;

    GC.Collect();

     

    This doesn't do the trick either. I've also tried using the above code in conjunction with the ServerDocument.RemoveCustomization method - placing it before and after the above code. Neither fully removed the runtime storage controls.

     

    Help with either of my methods (a or b) would be greatly appreciated.

     

    Thanks,

    Kit

     

    Friday, May 2, 2008 3:50 AM
  • Hi Kit

     

    FWIW I think you're on the right track with removing/adding the customization at the bookkeeper "node". Would it make sense to give that person a button that essentially says "Prepare and send to the next person in line", with a corresponding "hook it up again for me" button? The first could take care of removing the customization context, saving and closing the document. Not quite as "transparent" as you might hope, but it should certainly make sense to the bookkeeper and shouldn't be any more effort (numbers of button clicks) than is currently being used.

     

    Other than that, you might want to try the BeforeClose event rather than Shutdown, see if that works any better.

     

     Kingglorifier wrote:

    I need to figure out how to remove the runtime storage controls from the excel document. Here's where I'm at in that process:

     

    a.) I tried to remove the customization internally by calling the Worbook.RemoveCustomization method from within ThisWorkbook_Shutdown, but this had no effect. Where else can I call the Workbook.RemoveCustomization from? Is there anything else I need to do to have this work?

    Friday, May 2, 2008 2:36 PM
    Moderator
  • Cindy,

     

    Thanks for the input. I implemented what you suggested above - I created a button that calls the ThisWorbook.RemoveCustomization method and then saves and closes the workbook. After clicking the button and removing the customization, I tried working with the document using Excel 2007. When I tried to save the workbook in Excel 2007, I received the same error as before: "Uninitialized ActiveX controls cannot be transferred to the selected file format. The controls will be lost if you continue. Location: 'Sheet1'".  I selected Continue and it saved the workbook. I then added the customization back to the workbook using the following code in a console application:

     

    string assemblyName = @\\server\test.dll;

    string manifestPath = @"\\server\test.application";

    string applicationVersion = "1.0.0.0";

    VSTO.ServerDocument.AddCustomization(fileName, assemblyName, manifestPath, applicationVersion, false);

     

    After adding the customization successfully, I tried opening the workbook and received an error indicating that the customization assembly could not be found or loaded. Previously, there was nothing in the details, but now there is: "Element not found. (Exception from HRESULT: 0x80070490)" and then it lists the long exception text (which I can post if needed).

     

    So, it appears as if the Workbook.RemoveCustomization does not actually remove the runtime storage control. That, or I'm overlooking something. Any help would be greatly appreciated.

     

    Thanks,

    Kit

    Tuesday, May 6, 2008 7:10 AM
  • I decided I should go ahead and post the Exception text. Here it is:

     

    System.Runtime.InteropServices.COMException (0x80070490): Element not found. (Exception from HRESULT: 0x80070490)
       at Microsoft.VisualStudio.Tools.Applications.Runtime.IHostItemProvider.GetHostExtender(String extenderType, String extenderCookie, Object& extenderObject)
       at Microsoft.Office.Tools.Excel.Controls.TextBox.Microsoft.VisualStudio.Tools.Applications.Runtime.ISupportInitializeExtendedControl.InitializeControl(IHostItemProvider hostItemProvider, IRuntimeServiceProvider runtimeServiceProvider, String primaryCookie, String extenderCookie, Object container, String identifier)
       at Microsoft.Office.Tools.Excel.Controls.TextBox..ctor(IHostItemProvider hostItemProvider, IRuntimeServiceProvider runtimeServiceProvider, String primaryCookie, String extenderCookie, Object container, String identifier)
       at ER_May08.Sheet1.InitializeControls()   <----ER_May08 is the name of the workbook
       at ER_May08.Sheet1.Initialize()
       at Microsoft.VisualStudio.Tools.Applications.Runtime.AppDomainManagerInternal.CreateStartupObject(EntryPoint entryPoint, Dependency dependency, Assembly objectAssembly)
       at Microsoft.VisualStudio.Tools.Applications.Runtime.AppDomainManagerInternal.ConfigureAppDomain()
       at Microsoft.VisualStudio.Tools.Applications.Runtime.AppDomainManagerInternal.LoadAssembliesAndConfigureAppDomain(IHostServiceProvider serviceProvider)
       at Microsoft.VisualStudio.Tools.Applications.Runtime.AppDomainManagerInternal.LoadEntryPointsHelper(IHostServiceProvider serviceProvider)

    Tuesday, May 6, 2008 7:19 AM
  • Hi Kit

     

    "So, it appears as if the Workbook.RemoveCustomization does not actually remove the runtime storage control. That, or I'm overlooking something."

     

    I was skimming through some literature yesterday, looking for information on something completely unrelated, and I remember seeing that the Runtime Storage Control can't be removed if protection has been applied to the document. Could that be what's going on in your case?

     

    Tuesday, May 6, 2008 7:26 AM
    Moderator
  • Hey Cindy,

     

    Unfortunately, it's not the case. I know this because when I first tried to call the ThisWorkbook.RemoveCustomization method, it threw an exception because I hadn't unprotected the workbook. So programatically I removed the protection from the workbook before calling ThisWorkbook.RemoveCustomization and it worked fine.

     

    Thanks,

    Kit

     

    Tuesday, May 6, 2008 7:42 AM
  •  

    Well, after much frustration and prayer, I figured out a solution. Here's what I did:

     

    Initially, I managed to delete every "shape" from the workbook. After doing that, I was able to save the workbook in Excel 2007 without any errors. But after adding the customization back to the workbook, I received the same error: "Element not found." I realized this was due to the fact that I had deleted every shape from the document - including the windows form controls. The error was a result of a failure to initialize the controls.

     

    Then, I remembered reading about being able to add windows form controls dynamically at runtime. So I tried it out: I added the required buttons during runtime (this required quite a bit of re-coding) and then called the Workbook.RemoveCustomization method to remove the rutime storage control. I also realized (through trial and error), that I needed to remove the dynamic controls during runtime as well, because Excel 2007 didn't like them either. Once I implemented all of that, I was able to save & edit the workbook in Excel 2007, add the customization back to the workbook, and retain all of the customization's functionality. Yeah!!!

     

    Thank you Mary and Cindy for your help!

     

    Kit

    Thursday, May 8, 2008 8:50 AM