determine current theme in xl2007

[JNW]

Is there a way to determine the current file being used as the theme in xl2007.

I know you can load different themes by using (watch line break)
ActiveWorkbook.Theme.ThemeColorScheme.Load ( _
"C:\Program Files\Microsoft Office\Document Themes 12\Theme
Colors\Equity.xml")

But I can't figure out how to get it to tell me which file it is currently
using.

Thanks.

 

[JNW]

Sorry for this, but I just discovered something else that I need answered.

If you record a macro to change the theme away from the default then change
it back to the "office" theme you get an unusable bit of code"
ActiveWorkbook.Theme.ThemeColorScheme.Load ()

this gives you a runtime error. Also, if you look in the directory listed
below, there is no xml file for the default theme. If this is the case, how
can I switch back to the default theme programmatically?

 

[Rob]

I have found a way around the 2nd of your problems, that of changing back to
the Office theme, although it is a bit roundabout! You need to first save the
Office theme to a 'thmx' file by using any workbook that has this Office
theme applied, where you use the Themes dropdown and then the 'Save Current
Theme' option to create this file.

You can then just use the ApplyTheme method of a Workbook using this 'thmx'
file, which will change back to the Office theme. Of course this does mean
that you need to have this file on any PC that you wish to use the VBA code!

For any component of the Theme, for example Colours, you just need to create
the Office Theme version of the XML file, where the details can easily be
obtained from the Office thmx file that you have just created, or using the
Save method of the individual Theme Component in VBA, where you can then just
use the Load method to change just this component of the theme
programmatically.

I can find no easy alternative, as I also get the error message when trying
to use the Recorded Macro code.

I can't help you on the first problem of determining what the current theme
is! Other than to suggest that you could look inside the xlsx file and locate
the Theme xml file inside this container, and then checking the Name attached
to the Colour, Font and Effects elements.

Hope this helps, and is not too late to be of any use!

Rob