Automator, Microsoft Excel

Excel Toolbars Disappear-How to Automate Fixing Them With Automator in Excel 2008 For Mac

by
Excel Toolbars Disappear-How to Automate Fixing Them With Automator in Excel 2008 For Mac
Excel Toolbars Disappear-How to Automate Fixing Them With Automator in Excel 2008 For Mac

I love Microsoft Excel for the most part but there are a handful of things that irritate me about it too.  One of those things is that the toolbars I’ve carefully created, arranged, and customized (and have become used to) up and disappear for no apparent reason.  I have installed all the updates and it still does it.  I haven’t been able to find a solution except to keep a backup of the preference file that holds the toolbar settings and then when they disappear, I replace the bare preference file with my customized one.  Lately this has happened multiple times in a week so I decided I wanted to automate the repair.  Here’s how you can do it:

Excel Toolbars Disappear-How to Automate Fixing Them With Automator in Excel 2008 For Mac

Software:

Step 1: Set up your toolbars just the way you like them.

I don’t like that I can’t seem to get some of the toolbars to imbed in the top of the Excel window but I’ve gotten used to having floating toolbars and some imbedded as well.  Anyway, get them the way you like them and then close Excel.

Step 2: Locate the Excel Preference file that contains the toolbar settings.

This file is located in the following location (with “Macintosh HD” replaced by whatever you have named your HD and “your-username” replaced by, of course, your username):

Macintosh HD > Users > your-username > Library > Preferences > Microsoft > Office 2008 > Excel Toolbars (12)

One problem is that OS X currently “hides” the library file so you cannot browse to it in the Finder and it doesn’t even show up in the Go menu:
OS X currently automatically hides the Library folder
If you switch to the Finder, click on the Go menu at the top, and then press the option key (two to the left of the space bar) while the Go menu is dropped-down, then the Library menu item magically appears:
The Library menu item appears when you press and hold the option key
Click on Library in the menu and a Finder window will open up showing the Library folder for your current user.  Switch to the Excel Toolbars location:

Macintosh HD > Users > your-username > Library > Preferences > Microsoft > Office 2008 > Excel Toolbars (12)

Location of Toolbar Preference file for Microsoft Excel 2008 for Mac
You can see that I’ve already created a backup copy in the “Office 2008” Folder but we are going to put a copy of the file in another folder for our use in this automation.

Step 3: Copy this “Excel Toolbars (12)” file.

There are many different ways you can do this, here are a few to choose from:
  • hold down the control key and click on the file and select copy from the menu that pops up
  • can click on the file and then use the keyboard shortcut Command-C
  • click once on the file and then select it from the Finder’s menu: Edit > Copy.

Step 4: Create a folder in your Documents directory as a storage place for a backup copy of the good toolbar preference file.

This is your current preference file if you completed Step 1.  You most surely know how to create a folder if you are delving into this but in the spirit of being thorough, here are a few ways you can create a folder:
  • With the folder where you’d like your new folder highlighted, use the keyboard shortcut Command-N.
  • With the folder where you’d like your new folder highlighted, click on File and then New Folder in the menus at the top of the screen.
  • Hold down the control key and click in white space inside the folder where you want the new folder created and click on “New Folder” in the menu that pops up.

Step 5: Paste the Excel Toolbars (12) file you copied in step 3 into the new folder you created.

The folder you want the file to be located in will need to be highlighted when you paste because that indicates the place the Finder is focused on currently.  Don’t change the name of the file because you want the file to be named exactly the same as its original and I’ll explain why later.
Location of new folder for my “backup” Excel Toolbars (12) preference file

Step 6: Launch the Automator program.

You can simply type Automator in the spotlight search or you can find it located in the Applications folder.  It may show this when you open Automator:

 

If it shows the window pictured above then simply click the “New Document” button at the lower left corner and then move on below the image below.  If you don’t get the iCloud window like above, Automator may pop up a menu like this when you launch it:
If it shows the menu above then simply click on the “Application” icon and then click on the “Choose” button.  This will give you the following window:

Step 7: Add the “Get Specified Finder Items” to the workflow.

In the Actions search box type “Get Specified Finder Items.”  The “Get Specified Finder Items” Action will be the only one listed in the search results window.  Click, drag, and drop the action into the window on the right that says “Drag actions or files here to build your workflow.”  This is what you’ll have:

Step 8: Add the backup copy of your preferences to the “Get Specified Finder Items” Action you just added.

First Click the “Add…” button.  Then browse to the location where you placed the backup copy of your preferences in, in Step 5, and select the file “Excel Toolbars (12)” and click the “Add” button at the lower right of the window.  This is what you should have (where the path shown is the location where your backup preference file is located):

Step 9: Add the “Copy Finder Items” to the workflow.

In the Actions search box type “Copy Finder Items.”  There will be two results of this search (“Copy Finder Items” and “Duplicate Finder Items”).  Click, drag, and drop the “Copy Finder Items” action into the window on the right just below the “Get Specified Finder Items”.   It may be easier if you make your Automator window bigger so there’s room below the “Get Specified Finder Items.”  This is what you’ll have:

Step 10: Set the “Copy Finder Items” path.

Set the “Copy Finder Items” path to the folder where Microsoft Excel 2008 stores its preference file (replace “Macintosh HD” will be the name you have given your HD, “your-username” will be your user account username):

Macintosh HD > Users > your-username > Library > Preferences > Microsoft > Office 2008

To set the location click on the “To:” drop-down menu that by default points to “Desktop” and click on “Other” from the menu that pops up:
This will open up a File browser window but you will not be able to browse to it since it is hidden by default.  Switch back to the Finder and use the trick I showed you in Step 2 to reveal your users Library folder and open up the path to the following location:

Macintosh HD > Users > your-username > Library > Preferences > Microsoft > Office 2008

Drag and drop the “Office 2008” folder to the file browser window you have open in Automator as follows:

Once you have drag-and-dropped the “Office 2008” folder into the “Choose” window click on the “Choose” button.  This is what you’ll have at this point in Automator:
Your workflow is almost complete at this point

Step 11: Click on the checkbox next to “Replace existing files.”

You will need to do this because you want Automator to replace the bad preferences file that Excel defaulted to (and replaced your good one with) with your archived good copy.

Step 12:  Save the Automator application.

Make sure that the “File Format:” drop-down menu says “Application” because we want a stand-alone application that we can just run and be done.  I have a folder where I save all my Workflows and Applications I create in Automator.
Saving the Excel Toolbar Preference Fix Workflow as an Application

Step 13:  Run this file when Excel forgets your toolbars.

NOTE: Make sure to close Excel before running it or it may not work.  I put a copy of the application in my dock for super easy access (next to the other workflows I often use for connecting to specific network drives and for merging pdf’s):
My Workflow Application is Easily accessible in the OS X Dock

Another Idea:

If you implement this like I did and then modify your current toolbars (for the better) you will lose those modifications the next time Excel decides to forget your toolbar preferences again.  You can make another automation application that copies the current toolbar preference file over top of the backup copy so you can update your backup.  Close Excel before doing this.

My other Apple Automator blog posts:

Amazon Associate Disclosure: As an Amazon Associate I earn from qualifying purchases. This means if you click on an affiliate link and purchase the item, I will receive an affiliate commission. The price of the item is the same whether it is an affiliate link or not. Regardless, I only recommend products or services I believe will add value to Share Your Repair readers. By using the affiliate links, you are helping support Share Your Repair, and I genuinely appreciate your support.

Tags from the story
, , , , ,
John Mueller
Written By
More from John Mueller
How to Repair a Fraying MacBook Power Cord – Take 2
Previously (here) I wrote a blog post on how to repair a...
Read More

Leave a Reply

Your email address will not be published. Required fields are marked *