none
Error when setting ReferenceAssemblyFromVbaProject to True RRS feed

  • Question

  • Hello,


    I just started to create an Excel Workbook 2010 C# Project by following MSDN tutorial : http://msdn.microsoft.com/en-us/library/vstudio/bb608613.aspx

    When setting ReferenceAssemblyFromVbaProject to True, a message pop up that more or less say:
    Property value is not valid
    Error while loading dll
    HRESULT : 0x80029C4A TYPE_E_CANTLOADLIBRARY

    I don't have any additional information and internet is not of great help as well.

    I checked several times my workbook and my project. My project location is registered as trusted in Excel Trusted center and there is plenty of VBA macro in my project.

    I am using VS 2010 targeting Excel 2010.

    Any idea where I should look to find a solution to this issue ?

    Thank you


    • Edited by Julien5 Tuesday, October 1, 2013 3:27 AM
    Monday, September 30, 2013 5:15 PM

Answers

  • Hi Cindy,

    I got it to work finally.

    I tried to repair the installation earlier but it didn't work, that why I was more in "reinstalling" everything.
    The fact is I had my Office and Visual Studio up to date, which means office SP2 and Visual studio SP1 and all other path, including optional ones.

    I uninstalled Visual Studio and Office completely, all features and reinstalled again. So I went back to Office and Visual Studio both with no SP installed.
    It half worked like this, I was able to set ReferenceAssemblyForVbaProject to True but I had an Excel error when starting the project.
    After installing SP2 for Office, everything was fine.

    I think it would be a good idea to write the prerequisite in term of versions for Office and Visual Studio in the Walkthrough.
    I will install updates one by one to see which one is causing problems.

    Thank you for help

    Friday, October 4, 2013 4:16 PM

All replies

  • If it can help, here are some settings:

    Visual Studio located in C:\Program Files (x86)\Microsoft Visual Studio 10.0
    Office located in C:\Program Files\Microsoft Office\Office14

    • On top of classical dependencies, my project contains following references:
      Microsoft.Office.Interop.Excel (v14.0.0.0, runtime v2.0.50727)
      Microsoft.Office.Tools (v10.0.0.0, runtime v4.0.30319)
      Microsoft.Office.Tools.Common (v10.0.0.0, runtime v4.0.30319)
      Microsoft.Office.Tools.Common.v4.Utilities (v10.0.0.0, runtime v4.0.30319)
      Microsoft.Office.Tools.Excel (v10.0.0.0, runtime v4.0.30319)
      Microsoft.Office.Tools.Excel.v4.Utilities (v10.0.0.0, runtime v4.0.30319)
      Microsoft.Office.Tools.v4.Framework (v10.0.0.0, runtime v4.0.30319)
      Microsoft.Vbe.Interop (v14.0.0.0, runtime v2.0.50727)
      Office (v14.0.0.0, runtime v2.0.50727)

    This is the code in my Sheet3.cs:

    using System;
    using System.Collections.Generic;
    using System.Data;
    using System.Linq;
    using System.Text;
    using System.Windows.Forms;
    using System.Xml.Linq;
    using Microsoft.Office.Tools.Excel;
    using Microsoft.VisualStudio.Tools.Applications.Runtime;
    using Excel = Microsoft.Office.Interop.Excel;
    using Office = Microsoft.Office.Core;

    namespace WizardCS
    {
        [System.Runtime.InteropServices.ComVisible(true)]
        [System.Runtime.InteropServices.ClassInterface(System.Runtime.InteropServices.ClassInterfaceType.None)]
        public partial class Sheet3 : WizardCS.ISheet3
        {
            private void Sheet3_Startup(object sender, System.EventArgs e)
            {
            }

            private void Sheet3_Shutdown(object sender, System.EventArgs e)
            {
            }

            protected override object GetAutomationObject()
            {
                return this;
            }

            public void Compute()
            {
                MessageBox.Show("Genial !");
            }

            #region Code généré par le Concepteur VSTO

            /// <summary>
            /// Méthode requise pour la prise en charge du Concepteur - ne modifiez pas
            /// le contenu de cette méthode avec l'éditeur de code.
            /// </summary>
            private void InternalStartup()
            {
                this.Startup += new System.EventHandler(Sheet3_Startup);
                this.Shutdown += new System.EventHandler(Sheet3_Shutdown);
            }

            #endregion

        }
    }

    and the interface:

    using System;
    namespace WizardCS
    {
        [System.Runtime.InteropServices.ComVisible(true)]
        public interface ISheet3
        {
            void Compute();
        }
    }
    

    • My solutionis located in E:\Projects\WizardCS
      My project is located in E:\Projects\WizardCS\WizardCS
    • After generating the code, I got following files in E:\Projects\WizardCS\WizardCS\bin\Debug:
      Microsoft.Office.Tools.Common.v4.0.Utilities.dll
      Microsoft.Office.Tools.Common.v4.0.Utilities.xml
      Microsoft.Office.Tools.Excel.v4.0.Utilities.dll
      Microsoft.Office.Tools.Excel.v4.0.Utilities.xml
      wizard.xlsm
      WizardCS.dll
      WizardCS.dll.manifest
      WizardCS.pdb
      WizardCS.vsto

    Tuesday, October 1, 2013 12:58 AM
  • And same issue when following the tutorial with exact same code and Excel workbook.

    Do I guess it is VS installation or Office installation issue, or computer parameters. But again, the error message is not clear enough for me to know where to search.

    Microsoft Visual Studio 2010
    Version 10.0.40219.1 SP1Rel
    Microsoft .NET Framework
    Version 4.5.50709 SP1Rel

    Microsoft Visual C# 2010   01019-532-2002102-70991
    Microsoft Visual C# 2010

    Outils de développement Microsoft Office   01019-532-2002102-70991
    Outils de développement Microsoft Office

    Regards


    • Edited by Julien5 Tuesday, October 1, 2013 3:26 AM
    Tuesday, October 1, 2013 2:21 AM
  • Hi Julien,

    If the workbook or document does not already contain VBA code or if VBA code in the

    document is not trusted to run, you will receive an error message when you set the

    ReferenceAssemblyFromVbaProject property to True. This is because Visual Studio cannot

    modify the VBA project in the document in this situation.

    You can refer to the link to get more infomation :

    http://msdn.microsoft.com/en-us/library/vstudio/bb386306(v=vs.100).aspx

    You said you checked  project location is registered as trusted in Excel Trusted center and

    there is plenty of VBA macro .I think this issue should be for you workbook doesn't contain

    VBA code.

    Please visit link and notice step 1:

    http://msdn.microsoft.com/en-us/library/vstudio/bb608613.aspx

    Regard,

    • Edited by Mercop002 Wednesday, October 2, 2013 1:38 AM
    Wednesday, October 2, 2013 1:37 AM
  • Hi Mercop,

    Thank you for your answer.

    My workbook contains several macro defined in a new module (Module1).
    On top of that, as mentioned in the Walkthrough, I added a new Sub in ThisWorkbook file:

    Sub EmptySub() End Sub

    That is the state of my project and so far, I get the error.

    So yes, my project does contains VBA Code (sorry I didn't mention it previously).
    Is there any way to check, in Visual Studio, if this VBA Code is seen or not ? (other than setting ReferenceAssemblyFromVbaProject and getting an error).


    Also, not sure if there is a distinction to do between VBA Macro and VBA Code, the Walkthrough add to the confusion:
    "The first step is to create a macro-enabled workbook that contains a simple VBA macro. Before you can expose code in a customization to VBA, the workbook must already contain VBA code."

    Regards,

    Julien


    • Edited by Julien5 Wednesday, October 2, 2013 6:10 AM
    Wednesday, October 2, 2013 6:05 AM
  • Hi Julien

    I tested the walkthrough yesterday evening (my time) and it worked for me. Since I don't know what might make it fail in the manner you're experiencing, I didn't post a reply...

    I can, however, answer your last question: VBA code is the same as "macro". The important thing is that the workbook is enabled for VBA and contains a VBA project; or, to put it another way, the file needs to contain the necessary components so that the VSTO tools can work with it successfully.

    The only other suggestion I can make would be for you to start over with a new workbook and follow the steps in the walkthrough exactly. If that doesn't  trigger the same error you're seeing with your existing workbook, then we can be sure it's a problem with that workbook, rather than the VSTO configuration.


    Cindy Meister, VSTO/Word MVP, my blog

    Wednesday, October 2, 2013 2:00 PM
    Moderator
  • Hi Cindy,

    I tested several time with an empty workbook (with vba code in ThisWorkbook), following exactly what is in the Tutorial. I didn't skip any step and did exactly what is written, copied exactly the same code, I didn't take any liberty and didn't add anything more to what is written.

    My xlsm file definitly has VBA code and its location is trusted. And I still face the same issue.

    Is there really no log file or report where I can have more detail on what is going on ? I mean the error message is pretty obscure.

    If the issue comes from my environment, how can I check my environment has what is need to perform this tutorial ? (Visual studio settings / Office settings / Windows registry etc...)

    Regards,

    Julien


    • Edited by Julien5 Wednesday, October 2, 2013 2:52 PM
    Wednesday, October 2, 2013 2:40 PM
  • I suspect an issue with typelib registry settings. I don't know however what the proper values should looks like in the registry.

    The Visual Studio error message is saying error while loading type lib.

    Wednesday, October 2, 2013 3:35 PM
  • Hi Julien

    In your position, the next thing I'd try is to repair the installations of Office and of Visual Studio. With any luck, that will fix whatever is skewed on your machine...


    Cindy Meister, VSTO/Word MVP, my blog

    Thursday, October 3, 2013 3:18 PM
    Moderator
  • Hi Julien

    In your position, the next thing I'd try is to repair the installations of Office and of Visual Studio. With any luck, that will fix whatever is skewed on your machine...


    Cindy Meister, VSTO/Word MVP

    That could be an idea. However, there is no way to properly uninstall Visual Studio 2010. The uninstaller is far from removing all installed stuff (SQL Sever, ASP MVC 2, Language packs, Silverlight ...), and not talking about registry keys. Well that's another issue I guess...

    Still, I will try to remove everything and run a proper installation of Visual Studio and Office. I never had any issue like this and I am using a lot of different features in Visual Studio.

    Friday, October 4, 2013 12:07 PM
  • I didn't say uninstall, I said repair...

    Cindy Meister, VSTO/Word MVP, my blog

    Friday, October 4, 2013 2:01 PM
    Moderator
  • Hi Cindy,

    I got it to work finally.

    I tried to repair the installation earlier but it didn't work, that why I was more in "reinstalling" everything.
    The fact is I had my Office and Visual Studio up to date, which means office SP2 and Visual studio SP1 and all other path, including optional ones.

    I uninstalled Visual Studio and Office completely, all features and reinstalled again. So I went back to Office and Visual Studio both with no SP installed.
    It half worked like this, I was able to set ReferenceAssemblyForVbaProject to True but I had an Excel error when starting the project.
    After installing SP2 for Office, everything was fine.

    I think it would be a good idea to write the prerequisite in term of versions for Office and Visual Studio in the Walkthrough.
    I will install updates one by one to see which one is causing problems.

    Thank you for help

    Friday, October 4, 2013 4:16 PM