How does one set an Excel color theme to the default "Office" through VBA

rej 0
Viewed 23

I have a series of Excel workbooks that are currently set to color themes other than "Office" and I want to set them all back to the default "Office" color theme using VBA.

I know how to set the Excel color theme for a workbook in Excel to other themes using this command in VBA (ex: Office 2007 - 2010 theme):

wkbk.Theme.ThemeColorScheme.Load ("C:\Program Files\Microsoft Office\root\Document Themes 16\Theme Colors\Office 2007 - 2010.xml")

but I'm unable to determine how to set the color theme to the default "Office" theme. Comparing the color themes listed in the Excel ribbon and xml files under "C:\Program Files\Microsoft Office\root\Document Themes 16\Theme Colors", all are there in the folder except for "Office".

Using MS Office 365.

  • Leaving it blank like wkbk.Theme.ThemeColorScheme.Load () causes an error.
  • Leaving it blank like wkbk.Theme.ThemeColorScheme.Load ("") does nothing.
  • Setting it to "Office" like wkbk.Theme.ThemeColorScheme.Load ("Office") does nothing.

I'm hoping someone knows how.

default themes colors vba excel
1 Answers

not sure how to load "Office" color theme directly, but

you could activate it and then save in a .xlm file using ThemeColorScheme.Save method REF, which could be loaded later.

Code

You could utilize the solution from Vikas and akjoshi:

Private Sub CopyTheme(baseBook As Workbook, targetBook As Workbook)
Dim themeName As String
themeName = Environ("temp") & "\VBANoobTheme.xml"

'save theme
On Error Resume Next
Kill themeName
Err.Clear
On Error GoTo 0
'delete extra sheets
baseBook.Theme.ThemeColorScheme.Save themeName
targetBook.Theme.ThemeColorScheme.Load themeName
End Sub

You could then loop through files and call the above script, by something like this:
insert file paths in target_Wb_names

Sub main()
Dim target_Wb_names   As Variant
Dim baseBook            As Workbook
Dim targetBook          As Workbook

'insert paths to files where you want to
'change the color theme
target_Wb_names = Array( _
                    [path1], _
                    [path2])

'select "Office" color theme in this workbook
Set baseBook = ThisWorkbook

'looping through files tp change Color themes
For Each name In target_Wb_names:
    Set targetBook = Workbooks.Open(name)
    'calling script from (Vikas and akjoshi)
    Call CopyTheme(baseBook, targetBook)
    targetBook.Save
Next name

End Sub

Remember to select the "Office" color theme in the "base book" before running the script.

Vote up, if the solution works for you :-)

simpatak0
Related