none
How to revert the Excel Theme to the default Office Theme?

    Question

  • Hi,

    First of all, this is my first time posting here, and I'm hoping that this is the right forum to ask this question. If you think I'd get better response elsewhere, please let me know.

    I've been trying to figure out how to set Office Themes programmatically. In Excel, I saw that there is ApplyTheme method that I can use with a valid .thmx file. But, I've noticed that the default "Office" theme is missing in the Theme directory in the Office 2010. So, I cannot use ApplyTheme to revert back to the default theme.

    I would think that there would be a global constant of a sort that defines the default Office theme, but I have not came across one yet on MSDN. Can somebody enlighten me on how to pull this off?

    Thanks!

    Kesh

    http://msdn.microsoft.com/en-us/library/microsoft.office.interop.excel._workbook.applytheme.aspx

    Monday, March 19, 2012 4:08 AM

Answers

  • Kesh,

    The Office Theme is defined in the xml of a new workbook and AFAIK nowhere else, I know it's odd but that's the way it is.

    For your purposes if it's only the colours you are interested in it might be a bit easier just to save as a ColorScheme, which is a much smaller xml file to deal with.

    Peter Thornton

    • Marked as answer by hokiedsp Monday, March 19, 2012 6:34 PM
    Monday, March 19, 2012 3:49 PM
  • Try the following. For testing save a template named "book.xltx" in your XLSTART folder with an obvious non Office color-scheme (delete it when done)

    Only lightly tested but it should create (if not previously created) a color-scheme xml file in the appropriate folder.

    Option Explicit
    Sub test()
    Dim wb As Workbook
    Dim sClrSchm As String
            Set wb = ActiveWorkbook
         If GetOfficeClrSchme(sClrSchm) Then
                 wb.Theme.ThemeColorScheme.Load sClrSchm
         Else
                 ' ?
         End If
    
    End Sub
    
    Function GetOfficeClrSchme(sClrSchm) As Boolean
    Dim i As Long
    Dim va As Variant
            va = Array("\Microsoft", "\Templates", "\Document Themes", "\Theme
    Colors\")
            sClrSchm = Environ("appdata")
    ' might need to create the folder
         For i = 0 To UBound(va)
                 sClrSchm = sClrSchm & va(i)
                 If Not FileFldrExists(sClrSchm, vbDirectory) Then
                         MkDir sClrSchm
                 End If
         Next
            sClrSchm = sClrSchm & "Office.xml"
         If Not FileFldrExists(sClrSchm, vbNormal) Then
                 MakeOfficeSchm sClrSchm
                 GetOfficeClrSchme = FileFldrExists(sClrSchm, vbNormal)
         Else
                 GetOfficeClrSchme = True
         End If
    
    End Function
    
    Function MakeOfficeSchm(sClrSchm)
    Dim bFlag As Boolean
    Dim sFile As String, sTmp As String
    Dim wb As Workbook
    ' temporarily rename Book.xltx (if it exists)
    ' add a new wb and save the real Office color-scheme
            On Error GoTo errH
         sFile = Application.StartupPath & "\Book.xltx"
         If FileFldrExists(sFile) Then
                 Name sFile As sFile & ".tmp"
                 bFlag = True
         End If
    ' maybe disable screenupdating here
         Set wb = Workbooks.Add
         wb.Theme.ThemeColorScheme.Save sClrSchm
         MakeOfficeSchm = FileFldrExists(sClrSchm)
         wb.Close False
    done:
         On Error GoTo 0
         If bFlag Then
                 Name sFile & ".tmp" As sFile
         End If
         Exit Function
    errH:
         Resume done
    
    End Function
    
    Function FileFldrExists(ByVal sFile As String, _
                 Optional FileFolder As VbFileAttribute = vbNormal) As Boolean
    Dim nAttr As Long
         On Error Resume Next
        nAttr = GetAttr(sFile)
        FileFldrExists = (Err.Number = 0) And ((nAttr And 16&) = FileFolder)
        On Error GoTo 0
    
    End Function

    I don't quite follow what you are doing when you say Office color schemes plural, there's only one. Maybe explain in more detail the overall objective.

    Note this example only gets and applies the Office color-scheme, not the full Theme with fonts etc, though easy to adapt if that's what you really need.

    The code places the xml file in the appropriate folder such that it'll be visible in the UI, though of course it could be any folder.

    FWIW I use APIs to find the AppData folder though Environ is normally OK for simplicity.

    Peter Thornton

    • Marked as answer by hokiedsp Wednesday, March 21, 2012 6:36 PM
    Tuesday, March 20, 2012 2:14 PM

All replies

  • Hi Hokiedsp,

    I would think that there would be a global constant of a sort that
    defines the default Office theme, but I have not came across one yet
    on MSDN. Can somebody enlighten me on how to pull this off?

    The easiest is to open an empty file, apply the default theme you like and then save that file as filetype xltx (or m) to your XLSTART folder under %appData%\microsoft\Excel. Name the file Book.xltx (or m).


    Regards, Jan Karel Pieterse|Excel MVP|http://www.jkp-ads.com
    Monday, March 19, 2012 5:20 AM
  • Another thing you can do is,

    1) Find your theme folder (something like C:\Program Files\Microsoft Office\Document Themes 14)

    2) Open a workbook, change the theme to Office, then Save Current Theme as Office in the above folder

    Now you can use ApplyTheme to change to any theme, including the default Office theme

    Sub ChangeTheme(ByVal strTheme As String)
    Dim pathTheme As String
    pathTheme = "C:\Program Files\Microsoft Office\Document Themes 14\"
    ThisWorkbook.ApplyTheme pathTheme & strTheme & ".thmx"
    End Sub

    Use like so

    Call ChangeTheme("Office")

    This allows you to change an existing workbook to any theme programmatically.

    Ed Ferrero
    www.edferrero.com


    Monday, March 19, 2012 6:15 AM
  • Hi Ed,

    Now you can use ApplyTheme to change to any theme

    I assume you can only apply themes that you previously saved to that folder, right?


    Regards, Jan Karel Pieterse|Excel MVP|http://www.jkp-ads.com
    Monday, March 19, 2012 10:55 AM
  • Jan & Ed, Thanks for your replies.

    Hmmm, I prefer not to create an additional Theme or Excel files to do this (I'm trying to dynamically pull information out of MS Office for my application, which itself has nothing to do with Office, just to use its Theme color schemes).

    It kind of baffles me that MS decided to hard-code only the default theme while other preset themes in separate files... In any case, I'll poke around some more in MSDN to see if there is a solution.

    Kesh

    • Edited by hokiedsp Monday, March 19, 2012 1:34 PM
    Monday, March 19, 2012 1:33 PM
  • Hi Jan Karel

    Now you can use ApplyTheme to change to any theme

    I assume you can only apply themes that you previously saved to that folder, right?

    ApplyTheme can apply a theme from a *.thmx file in any folder or the full path to a workbook file, xlsx/m

    Probably best not to put the new Office theme in the default Theme's folder (as Ed's example implies if for no other reason than permission problems) but (assuming you want it visible to user) in one of the App-Data folders which is where custom Themes are stored by default.

    \Application Data\Microsoft\Templates

    Or, along the lines you originally suggested, create and save a temporary workbook to any folder and do ApplyTheme with the full path to the temporary workbook (which can be kept or deleted as required)

    Peter Thornton

    Monday, March 19, 2012 3:38 PM
  • Kesh,

    The Office Theme is defined in the xml of a new workbook and AFAIK nowhere else, I know it's odd but that's the way it is.

    For your purposes if it's only the colours you are interested in it might be a bit easier just to save as a ColorScheme, which is a much smaller xml file to deal with.

    Peter Thornton

    • Marked as answer by hokiedsp Monday, March 19, 2012 6:34 PM
    Monday, March 19, 2012 3:49 PM
  • Peter,

    The Office Theme is defined in the xml of a new workbook and AFAIK nowhere else.

    Does this mean if a (custom) default template changes its theme from the MS default, are the MS default lost for that file for good? Or is it safe to assume that user cannot change the default template in Excel?

    I thought in Word, you can change normal.dotm/x to set the default template. I'm not as familiar with Excel.

    Thanks,

    Kesh

    • Marked as answer by hokiedsp Monday, March 19, 2012 6:34 PM
    • Unmarked as answer by hokiedsp Monday, March 19, 2012 6:34 PM
    Monday, March 19, 2012 4:10 PM
  • Ah, if user creates a template named "Book.xltx" and stores it in the XLSTART folder all new workbooks will be based on that template. So in theory yes, user could change the Theme in that template. However if that template is removed or even renamed (say temporarily), any new workbook will be based on factory default.

    I forgot about that, and for my purposes I write the Office color scheme xml file in code rather than based on a workbook.

    Peter Thornton

    Monday, March 19, 2012 4:25 PM
  • Peter,

    Although it was not the answer I was hoping for, but you got me the information I was looking for. Thanks!

    Just FYI, I'm creating a program (in MATLAB) to import Office theme color schemes (both the default and those from from color scheme XML files).

    Monday, March 19, 2012 6:34 PM
  • Hi Jan Karel,

    Peter Thornton has already said most of this, but...

    The office theme folder contains theme files for all the standard Excel themes, except for the default Office theme. I suggested saving the default Office theme to that folder, since Microsoft seems to have forgotten to put the default in there.

    That way, you can use ApplyTheme to change between all the default themes.

    I appreciate that security settings may stop some users writing to that folder. Still, to me it seems better to write one file to a system folder where it is available to all users, than to ask every user to store a theme file in their own \username\Application Data\Microsoft\ folder. In a corporate environment, I would probably suggest incorporating this in the default image.


    Ed Ferrero
    www.edferrero.com

    Tuesday, March 20, 2012 1:28 AM
  • Hi Ed,

    The office theme folder contains theme files for all the standard
    Excel themes, except for the default Office theme. I suggested saving
    the default Office theme to that folder, since Microsoft seems to
    have forgotten to put the default in there.

    So I gathered, thanks. I learnt something new today.


    Regards, Jan Karel Pieterse|Excel MVP|http://www.jkp-ads.com
    Tuesday, March 20, 2012 6:29 AM
  • Try the following. For testing save a template named "book.xltx" in your XLSTART folder with an obvious non Office color-scheme (delete it when done)

    Only lightly tested but it should create (if not previously created) a color-scheme xml file in the appropriate folder.

    Option Explicit
    Sub test()
    Dim wb As Workbook
    Dim sClrSchm As String
            Set wb = ActiveWorkbook
         If GetOfficeClrSchme(sClrSchm) Then
                 wb.Theme.ThemeColorScheme.Load sClrSchm
         Else
                 ' ?
         End If
    
    End Sub
    
    Function GetOfficeClrSchme(sClrSchm) As Boolean
    Dim i As Long
    Dim va As Variant
            va = Array("\Microsoft", "\Templates", "\Document Themes", "\Theme
    Colors\")
            sClrSchm = Environ("appdata")
    ' might need to create the folder
         For i = 0 To UBound(va)
                 sClrSchm = sClrSchm & va(i)
                 If Not FileFldrExists(sClrSchm, vbDirectory) Then
                         MkDir sClrSchm
                 End If
         Next
            sClrSchm = sClrSchm & "Office.xml"
         If Not FileFldrExists(sClrSchm, vbNormal) Then
                 MakeOfficeSchm sClrSchm
                 GetOfficeClrSchme = FileFldrExists(sClrSchm, vbNormal)
         Else
                 GetOfficeClrSchme = True
         End If
    
    End Function
    
    Function MakeOfficeSchm(sClrSchm)
    Dim bFlag As Boolean
    Dim sFile As String, sTmp As String
    Dim wb As Workbook
    ' temporarily rename Book.xltx (if it exists)
    ' add a new wb and save the real Office color-scheme
            On Error GoTo errH
         sFile = Application.StartupPath & "\Book.xltx"
         If FileFldrExists(sFile) Then
                 Name sFile As sFile & ".tmp"
                 bFlag = True
         End If
    ' maybe disable screenupdating here
         Set wb = Workbooks.Add
         wb.Theme.ThemeColorScheme.Save sClrSchm
         MakeOfficeSchm = FileFldrExists(sClrSchm)
         wb.Close False
    done:
         On Error GoTo 0
         If bFlag Then
                 Name sFile & ".tmp" As sFile
         End If
         Exit Function
    errH:
         Resume done
    
    End Function
    
    Function FileFldrExists(ByVal sFile As String, _
                 Optional FileFolder As VbFileAttribute = vbNormal) As Boolean
    Dim nAttr As Long
         On Error Resume Next
        nAttr = GetAttr(sFile)
        FileFldrExists = (Err.Number = 0) And ((nAttr And 16&) = FileFolder)
        On Error GoTo 0
    
    End Function

    I don't quite follow what you are doing when you say Office color schemes plural, there's only one. Maybe explain in more detail the overall objective.

    Note this example only gets and applies the Office color-scheme, not the full Theme with fonts etc, though easy to adapt if that's what you really need.

    The code places the xml file in the appropriate folder such that it'll be visible in the UI, though of course it could be any folder.

    FWIW I use APIs to find the AppData folder though Environ is normally OK for simplicity.

    Peter Thornton

    • Marked as answer by hokiedsp Wednesday, March 21, 2012 6:36 PM
    Tuesday, March 20, 2012 2:14 PM
  • Peter,

    Clever. You are right; all it takes is simply renaming the user default template.

    "Office color schemes" I was just referring to all the theme color schemes in Office (not just the default one). I'm creating a function in MATLAB (math/science programming environment) to use Office color schemes. I'm accessing Excel via .NET interface.

    Wednesday, March 21, 2012 6:36 PM