September 20, 2008

Saving and Restoring Your Excel Environment

Filed under: General Excel — fatrunner @ 2:54 pm
Tags: , , , ,

Instead of Visual Basic, today I wanted to talk about something close to my heart, saving and restoring your toolbars, macros and add-ins.  Two days ago I was having problems with my computer and I decided I would get radical and instead of trying to track down what was probably a very simple issue I would just do a system restore from a recent image I had made of my hard drive.

Let me back track a little.  About a month ago I did a wipe of my system and did a clean install of everything and had recently heard about MaxBlast, a free drive imaging program, so I decided that after my clean install I would make an image of my system to an external USB hard drive for a quick restore instead of spending LITERALLY a whole day reinstalling all my programs.  Since my clean install I’ve made a couple of additional images and thought that eveyone should be envious of me and how awesome I am.  Little did I know.

Long story short, the restore process hosed my computer. Numerous attempts at all of the drive images failed.  I’ve read a lot of people saying that they liked MaxBlast, especially since it is a branch of the Acronis Ture Image software which has been ranking top of the list on PC Mag’s reviews.  Sad fact is that it didn’t work for me.  Now I’ve done the good ol’ fashioned wipe and reinstall and am back up and running and that gets me to what I really wanted to talk about…being able to easily restore your Excel environment after a ‘wipe job’.

Fortunately I’m a freak about backing up my stuff and have learned over the years how to implement a completely painless way of getting my documents and other important “things” backed up daily or more or less often if needed.

Here is what I back up in regards to Excel:

  1. personal.xls – This is the file that houses all of your macros, or at least the macros that you make available to all spreadsheets in Excel. This does not include macros that get recorded and saved directly to a workbook. This is a choice you make when you record your macro and select the ‘store macro in:’ drop down.  This file is normally saved in the following directory: C:\Documents and Settings\username\Application Data\Microsoft\Excel\XLSTART.  I’m using Windows XP and Excel 2003. It may be slightly different if you are using a different version of Excel and/or Vista.
  2. Excel11.xlb – Depending on the version of Excel you are using this may be named something slightly different.  What you are looking for is the .xlb and you will find it in the following location: C:\Documents and Settings\username\Application Data\Microsoft\Excel. This is just one folder up from where the personal.xls file is saved. This file is the one that houses all of your various buttons and toolbars that you’ve spent years getting just the way you like it. If you’ve made buttons for you macros and then gone in and edited the button image to something other than the few crappy canned images that are provided, this is a must save file.
  3. Add-ins – The location is: C:\Documents and Settings\username\Application Data\Microsoft\AddIns. This is not a specific file, and you may not even have anything here. If you’ve downloaded add-ins (like I have), this is the place to put them.  If they are in this folder they will show up in the list of available add-ins when you go to Tools –> Add-ins…

Now that you know the location of these critical files Here’s what you do to make sure they are backed up.  Download Karen Kenworthy’s Free Replicator.  You can easily set this program up to start with Windows and perform backups at scheduled times and either copy these files to your My Documents or to an external hard drive or where ever makes sense to you.  Now they will be available to you whenever you need to put things back the way they were before and do so quickly.

I hope that someone finds this useful and if you have any other ideas on saving and restoring your Excel environment OR saving and restoring your computer please let me know about it, I’d love to hear from you.


Create a free website or blog at WordPress.com.