System.NullReferenceException in a class library

Question

I'm trying to configure some connection strings in app.config, but keep getting this error message,

System.NullReferenceException: Object reference not set to an instance of an object.

I've looked at numerous solutions on the web, but nothing directly applies,

• I'm copying the app.config file to the target directory
• I've referenced System.Configuration and am using ConfigurationManager
• The XML / source is almost a verbatim copy of the Microsoft guidelines

The only difference is that the project is a class library, which I'm exposing to Excel via ExcelDNA.

Is there something special I need to do to get this to work with Excel?

Here's a snippet of the XML,

<configuration>
    <connectionStrings>
        <add name="ConnectionCSV" providerName="System.Data.OleDb" connectionString="Provider=Microsoft.Jet.OLEDB.4.0;" />
    </connectionStrings>  
</configuration>

This is the source code (have got a using System.Configuration along with necessary reference),

string constr = ConfigurationManager.ConnectionStrings["ConnectionCSV"].ConnectionString;

The above line throws the Object reference not set to an instance of an object error.

Answer 1

The name of the configuration file needs to match the name of the .XLL file that is created by ExcelDNA.

e.g.

MyApp-AddIn.xll -> MyApp-AddIn.xll.config

MyApp-AddIn64.xll -> MyApp-AddIn64.xll.config

Answer 2

• Libraries don't have their own app.config files, they belong to the parent executable.
• Excel.exe does not have its own app.config file.
• The connection string ConnectionCSV does not exist in whatever configuration file, if any, is being used at runtime. You can verify this by checking if ConfigurationManager.ConnectionStrings["ConnectionCSV"] != null first and warning the user if the key doesn't exist.

In your case it might be better to store the connection string in the user's registry instead. To whit:

Update:

I just found this page on MSDN which describes your exact problem and explains how you can use app.config files with Office add-ins: https://msdn.microsoft.com/en-us/library/16e74h9c.aspx

Specifically:

In the [app.config filename] box, type the same name as the assembly plus the extension .config. For example, a configuration file for an Excel project assembly called ExcelWorkbook1.dll would be named ExcelWorkbook1.dll.config.