Excel cant read the app.config file

Jun 29, 2011 at 8:05 AM

Hi, 

I created a C# library to write all the functions, I added the database as Entity data module( .edmx),

Database name is SchoolDB, When i create the object as SchoolDB school= new SchoolDB(), excel cant read app.config file to get the connection string, 

When i hard code the Connections string & create the object as SchoolDB school = new School(Conn), functions work properly in Excel,

My app.config file is this

<?xml version="1.0" encoding="utf-8"?>

<configuration>

  <connectionStrings>

    <add name="SchoolDataBase" connectionString="metadata=res://;provider=System.Data.SqlClient;provider connection string='Data Source=danu001\;Initial Catalog=school;Persist Security Info=True;User ID=DB;Password=log@12345;MultipleActiveResultSets=True'" />

  </connectionStrings>

</configuration>

 

Does any one can help me with this problem, why cant excel read my app.config file?

Thank you Danu123

Coordinator
Jun 29, 2011 at 8:20 AM

Hi Danu,

You should call your configuration file "MyAddIn.xll.config" and put it next to MyAddIn.xll. Then it should load OK.

It will also be included in the .xll file if you run ExcelDnaPack.

Regards,

Govert 

Jun 29, 2011 at 8:30 AM

Hey Govert,

My files are : SchoolName.dll ,   SchoolName.DNA ,   SchoolName.dll.config , SchoolName.xll

& yes i did include the ExcelDnaPack to C# library

this is the format i have...........

Thank you

Danu

Coordinator
Jun 29, 2011 at 10:31 AM
Edited Jun 29, 2011 at 8:32 PM

Hi Danu,

Your config file needs to be called SchoolName.xll.config.

-Govert

Jun 30, 2011 at 5:48 AM
Edited Jun 30, 2011 at 5:49 AM

Hi Govert,

I changed the file name to "SchoolName.xll.config", still the output is #VALUE!, do i have to change the content of the config file. 

<?xml version="1.0" encoding="utf-8"?>

<configuration>

  <connectionStrings>

    <add name="SchoolDataBase" connectionString="metadata=res://;provider=System.Data.SqlClient;provider connection string='Data Source=danu001\;Initial Catalog=school;Persist Security Info=True;User ID=DB;Password=log@12345;MultipleActiveResultSets=True'" />

  </connectionStrings>

</configuration>

Danu

Coordinator
Jun 30, 2011 at 8:36 AM

Hi Danu,

I don't really know how the "ConnectionsStrings" section is loaded. The references I see only have to do with web.config for ASP.NET pages. Does it work in the way you use it from a simple console app?

You can also try the regular appSettings section. I tried this:

The ConfigTest.xll.config file:

<?xml version="1.0" encoding="utf-8"?>
<configuration>
  <appSettings>
    <add key="SchoolDataBase" value="metadata=res://;provider=System.Data.SqlClient;provider connection string='Data Source=danu001\;Initial Catalog=school;Persist Security Info=True;User ID=DB;Password=log@12345;MultipleActiveResultSets=True'"/>
  </appSettings>
</configuration>

The ConfigTest.dna file:

<DnaLibrary>
<Reference Name="System.Configuration" />
<![CDATA[
Imports System.Configuration

Public Module Test
   Function configSayHello()
      Return "Hello from ConfigTest"
   End Function  

    Function configAppSetting(key As String)
     Return ConfigurationManager.AppSettings(key)
   End Function

End Module

]]>
</DnaLibrary>

And a matching copy of ExcelDna.xll called ConfigTest.xll.

Now in Excel I can open the .xll and enter the formula: =configAppSetting("SchoolDataBase") and get the long string back.

-Govert

Jul 7, 2011 at 10:02 AM

Hey Govert,

Thanx a lot, i changed the ConfigTest.xll.config content to <appsetting>, and it worked, i could retrieve the values using 

string outDir = ConfigurationManager.AppSettings["SchoolDataBase"];

We have to include the reference system.configuration.

Thank you Govert. 

Danu.