« Another Mind-bending, Useless Statement about Time | Main | Idiot’s Guide to Digital Cameras »
Excel 2007 Problem: Solved!
By Brian | March 31, 2009 | Share on Facebook
I’ve been using MSOffice 2007 for a few weeks now, and while I’m generally pleased with the enhancements it brings, there was one bug in MSExcel that was driving me crazy.
When I opened an Excel file (.xls, .xlsx, .xlsm), Excel would launch, but the file wouldn’t appear. Instead, I’d see just an empty MDI frame (the Excel title bar and menus, but without any spreadsheet on the screen). I quickly discovered, through trial and error, that I could get the spreadsheet to open by choosing “Open” from the Office icon menu, and then clicking “Cancel” when the Open dialog box came up. This was, however, equal parts weird and annoying, and could not be the best answer to the problem.
Today, I got around to Googling it. As I suspected, others have had the same problem, and have suggested several possible solutions. One was to disable one or more suspicious add-ins, but I had none of the supposed culprits on my machine. The idea did get me thinking, though – the add-ins I did have (both of which were self-made), were in Excel 2003 format (.xla). Excel 2007 creates add-in files with a .xlam extension. Also, I’ve had several minor problems with Excel 2007/Excel 2003 compatibility issues in the past. Maybe the .xla format is the problem?
That solution turned out to be the right one. It also explains why others may find that disabling a certain add-in made the problem go away. Therefore, I’m pretty sure I’ve identified the problem.
So, to make the problem go away, you need to convert your .xla add-ins to .xlam. Here are the detailed instructions:
- Open Excel 2007 by running the app (not by opening an Excel file)
- On the Developer ribbon, chose “Visual Basic.” If the Developer ribbon isn’t displayed, you can add it by clicking the Office icon in the upper left corner of the screen, choosing “Excel Options” and checking the “Show Developer tab in the Ribbon” check box. Or, if you want to avoid the whole mess, you can use the old Excel 2003 keyboard shortcut (Alt-F11) to open the Visual Basic editor immediately
- Expand the add-in in question, and double click on the associated Module to bring up the code
- Click inside the code, and then press Ctrl-A to select all of it. Then press Ctrl-C to copy it
- Right click on the new Excel that opened with Excel (usually called Book1) and choose Insert…Module
- When the empty Module comes up, click inside of it and press Ctrl-V to paste in the code
- Click the office icon and choose “Save”
- Save the file as an Excel Add-In (.xlam) with the same name (other than the extension) and in the same folder as your existing add-in
- Repeat allof the steps above (starting with a brand new Excel file each time) for each .xla add-in you have
- Close the Visual Basic Editor
- From Excel, click the Office icon and choose Excel Options
- Click on the Add-Ins menu item on the left side of the dialog box. At the bottom, select Manage Excel Add-ins and click “Go.”
- When the Add-Ins box appears, click “Browse.”
- Choose the new (.xlam) add-in and click OK. You will be prompted that another add-in with the same name exists. Click OK at this prompt
- Repeat the above step for each newly created add-in
There you go – problem solved (hopefully). If you have any questions, comments, updates, or corrections, feel free to post them in the comments below.
Topics: Tech Talk | No Comments »