Microsoft Excel

Ron de Bruin
Excel Automation

Microsoft MVP Program

Read value from Excel Preference file

Sometimes as a developer you need to know what a user have set in Excel>Preferences.
I use the code below in a installed add-in for example to know if the checkbox named
"Warn before opening files that contains macros" is checked or not in Excel>Preferences..Security in Excel 2011 for the Mac.

Note 1: most values only exists when you changed them ones in Excel >Preferences. So when it not exists it means it have the default setting, in my example below the checkbox is checked as default.

Note 2: Excel only update the com.microsoft.Excel.plist file when you close Excel

Sub GetPlistValueTester()
'Example to know if the checkbox named "Warn before opening files that contains macros"
'is checked or not in Excel>Preferences..Security in Excel 2011
    Dim PlistValue As Long
    PlistValue = _
    GetOfficeProgramPlistValue("com.microsoft.Excel.plist", "14\\Microsoft Excel\\Options6")

    Select Case PlistValue
    Case 152:
        MsgBox "The checkbox ""Warn before opening files that contains macros"" " & _
               "is checked in Excel>Preferences...Security"

    Case 144:
        MsgBox "The checkbox ""Warn before opening files that contains macros"" " & _
               "is unchecked in Excel>Preferences...Security"

    Case Else:
        MsgBox "Can't find entry in plist file, that means you never changed this checkbox. " & _
               "The checkbox ""Warn before opening files that contains macros"" " & _
               "is checked in Excel>Preferences...Security (default setting)"
    End Select
End Sub


Function GetOfficeProgramPlistValue(PlistName As String, PathToValue As String)
'Ron de Bruin : 11-Jan-2013
    Dim ScriptToRun As String

    ScriptToRun = "tell application " & Chr(34) & _
                  "System Events" & Chr(34) & Chr(13)
    ScriptToRun = ScriptToRun & "set PLRoot to property list file ((path to preferences as text) &" _
                & Chr(34) & PlistName & Chr(34) & ")" & Chr(13)
    ScriptToRun = ScriptToRun & "return value of property list item  " & Chr(34) & _
                  PathToValue & Chr(34) & " of PLRoot" & Chr(13)
    ScriptToRun = ScriptToRun & "end tell"

    On Error Resume Next
    GetOfficeProgramPlistValue = MacScript(ScriptToRun)
    On Error GoTo 0
End Function