ConfigurationManager, appSettings; Where/how to store environment settings?

[Yugmorf]

Hi,

I'm trying to use <TheAddInName>.xll.config to store application settings that depend on the environment in which exceldna addin is run (eg. directory paths, connection strings).  I followed the instructions outlined here; 

https://exceldna.codeplex.com/wikipage?title=User%20settings%20and%20the%20.xll.config%20file

However, I have the problem that I can't read the appSettings key, val pairs if I hard code them in the xml config document (<TheAddInName>.xll.config ). 

Moreover if I try to add appSettings key,value pairs using the methods here;

http://msdn.microsoft.com/en-us/library/system.configuration.configurationmanager(v=vs.110).aspx

then it works within the intermediate window of visual studio, but if I try to call the function from excel (using exceldna) then I get the following access violation;

System.Configuration.ConfigurationErrorsException: An error occurred loading a configuration file: Access to the path 'C:\Program Files\Microsoft Office 15\root\office15\f2w2jgu3.tmp' is denied. (C:\Program Files\Microsoft Office 15\root\office15\EXCEL.EXE.config) ---> System.UnauthorizedAccessException: Access to the path 'C:\Program Files\Microsoft Office 15\root\office15\f2w2jgu3.tmp' is denied.

Any pointers about how to proceed, or how I might set this up differently, would be appreciated.

Thanks,

Yug

[Govert van Drimmelen]

Hi Yug,

You say:

> However, I have the problem that I can't read the appSettings key, val pairs if I hard code them in the xml config document (<TheAddInName>.xll.config ). 

Do you mean that the instructions on the CodePlex page did not work for you?

Do you get an exception or null when trying to read the key from ConfigurationManager.AppSettings?

Writing configuration files using the ConfigurationManager might be harder...

-Govert

[Yugmorf]

Hi Govert,
Thank you for your message.  The code for the App.config file and calling routine are shown below.  Typing from the intermediate window I notice the following (note that i and iii are the same, but it only works if called after the sub myconfig.dnaAppConfigGetSetting("MyTest") is called first, else returns an error.

i) ?System.Configuration.ConfigurationManager.AppSettings("MyTest")

'ConfigurationManager' is not a member of 'Configuration'.

ii) ?myconfig.dnaAppConfigGetSetting("MyTest")

"Forty-two"

iii) ?System.Configuration.ConfigurationManager.AppSettings("MyTest")

"Forty-two"

If I change the name of the config file from 'App.config' to something else such as <myAppname>.xll.config , then the sub returns 'nothing'.
No matter the config file name, if i call the sub from an excel sheet doesn't work in either case; either returns 'nothing' or !value. 

So, my primary problem is that while the sub to read the config setting is working from within visual studio, it doesn't work from excel (if i simply return the key, instead of the value, then i get the key pasted into excel, so this suggests something about the calling environment not being able to find the config file).  Secondly i wonder why the config file name change makes a difference.

Thanks,
Yug

<?xml version="1.0" encoding="utf-8" ?>
<configuration>
    <configSections>  
        <section name="nlog" type="NLog.Config, NLog"/>
    </configSections>
    <appSettings>    
        <add key="MyTest" value="Forty-two" />
    </appSettings>
</configuration>


<ExcelFunction(Description:="Reads key,value pair from application configuration file")>
 Public Shared Function dnaAppConfigGetSetting(ByVal key As Object) As String
     Return dnaAppConfigGetSetting(key(0).ToString)
 End Function
 Shared Function dnaAppConfigGetSetting(key As String) As String
     Try
         Dim appSettings As Object = System.Configuration.ConfigurationManager.AppSettings
         Dim result As Object = appSettings(key)
         If IsNothing(result) Then
             result = "Not found"
         End If
         Return result.ToString
     Catch e As System.Configuration.ConfigurationErrorsException
         Return e.ToString
     End Try
 End Function

[Govert van Drimmelen]

Hi Yug,

It all seems to work fine on my machine.

I followed these steps:

1. Create a new Visual Basic Class Library (.NET 4.5) project called CheckConfig.

2. Install-Package Excel-DNA

3. Add a Reference to System.Configuration.

4. Put the following code into the Class1.vb code file:

Imports System.Configuration

Imports ExcelDna.Integration

Public Class TestConfig

    <ExcelFunction(Description:="Reads key,value pair from application configuration file")>

    Public Shared Function dnaAppConfigGetSetting(ByVal key As Object) As String

        Return dnaAppConfigGetSetting(key(0).ToString)

    End Function

    Shared Function dnaAppConfigGetSetting(key As String) As String

        Try

            Dim appSettings As Object = ConfigurationManager.AppSettings

            Dim result As Object = appSettings(key)

            If IsNothing(result) Then

                result = "Not found"

            End If

            Return result.ToString

        Catch e As ConfigurationErrorsException

            Return e.ToString

        End Try

    End Function

End Class

5. Add a new file into the project, which I call CheckConfig-AddIn.xll.config.

6. Set the properties for the CheckConfig-AddIn.xll.config to "Copy to Output Directory: Copy if newer".

7. Put the following content to the CheckConfig-AddIn.xll.config file:

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

<configuration>

  <appSettings>

    <add key="MyTest" value="Forty-two" />

  </appSettings>

</configuration>

8. Press F5 to run Excel and load the add-in.

9. Into a cell enter the formula =dnaAppConfigGetSetting("MyTest")

10. Confirm that the answer in Excel is "Forty-two" as expected.

Perhaps you can try the same steps on your side.

If that works, and you still want to track down why it did not work originally, I suggest you check that the .config file exists next to your .xll file, and has the right name. Also, check that, if there Excel.exe.config file (in Program Files...) that it has nothing funny in.

Regards,

Govert

[Yugmorf]

Govert,

Thank you for taking the time to write a detailed reply.  Following your steps, I also got it to work.  I think I might have had some combination of extra files and absent 'copy if newer' reference on the config file in my project.  Anyway, fixed now.

For the record, please find below the code that can be used for writing to the config file from excel/vba.

Best regards

Yug

<ExcelFunction(Description:="Writes key,value pair to application configuration file")>
    Shared Function dnaAppConfigSetSetting(ByVal KeyVal() As Object) As Object
        Try
            Dim configFile As Object = ConfigurationManager.OpenExeConfiguration(ConfigurationUserLevel.None)
            Dim settings As Object = configFile.AppSettings.Settings
            Dim newKeyVal() As String = KeyVal.Select(Function(item) item.ToString).ToArray() 'changes to a 1D array
            Dim key As String = newKeyVal(0).ToString, val As String = newKeyVal(1).ToString
            If IsNothing(settings(key)) Then
                settings.Add(key, val)
            Else
                settings(key).Value = val
            End If
            configFile.Save(ConfigurationSaveMode.Modified)
            ConfigurationManager.RefreshSection(configFile.AppSettings.SectionInformation.Name)
            Return "True"