PERSONAL.XLSB

Question

I have just installed Office 2007 onto my new HP computer (model h8-1070uk), running Windows 7 Home Premium.

I copied across some Excel VBA macros from my old XP machine onto the new machine (via a USB stick) and saved them in PERSONAL.XLSB, because I want them available to all Excel workbooks that I work with.

While this works perfectly in Excel 7 under Windows XP, I am getting some strange behaaviour on the new machine under Windows 7.  On the new machine, tthis is what happens:

If I just launch Excel, then it starts up PERSONAL.XLSB but shows a blank workbook - exactly as it should.  I can then open Excel workbooks and everything behaves as normal, with the PERSONAL.XLSB macros available when I need to use them.

However, if I open an Excel workbook by double-clicking on the file name (thereby automatically launching Excel), the PERSONAL.XLSB file is not included when Excel launches, and if I try to run one of those macros, I get a VBA error number 400 (presumably meaning that it cannot find the macro).

I have looked at various folders to see where Microsoft Office and PERSONAL.XLSB are stored, and this is the result:

"C:\Users\User\AppData\Roaming\Microsoft\Excel\XLSTART" contains PERSONAL.XLSB

"C:\Program Files\Microsoft Office\Office12" doesn't contain an XLSTART folder

"C:\Program Files (x86)\Microsoft Office\Office12" does contain an XLSTART folder but that folder is empty. (I've tried copying PERSONAL.XLSB into this folder, but it makes no difference).

The normal application startup file for Excel appears to be the first of the three paths above (according to the "?Application.StartupPath" command).

Does anyone have any idea what is going on here.  I am new to Windows 7, and don't understand why Microsoft Office appears in so many paths.

I must say that the new pc came with Microsoft Office 2010 starter version pre-loaded, and I uninstalled that before installing Office 2007, but not sure if that would have affected things.

Regards,

Geoff

Answer 1

Hi Max Meng.

I've managed to solve the problem!

I had the preview pane open in Windows Explorer, so when I selected the Excel spreadsheet ready to double-click it to open it, the preview window was showing the spreadsheet.  It would seem that Windows Explorer probably has to launch the appropriate application (Excel in this case) somehow to show the preview and, in doing so, the PERSONAL.XLSB file was presumably opened in the background..

So...when I double-clicked it to open it, it opened, but because PERSONAL.XLSB was already open (in the Windows Explorer "preview" version), then it didn't open it a second time.

It's one of those problems that, when you find the answer and think it through, then it makes some sort of sense.  But trying to figure out why it is happening in the first place is fiendishly difficult!

So, the answer is - don't have the preview pane open in Windows Explorer.

Regards,

Geoff

Answer 2

Glad to know that!

Thanks for the update!

Answer 3

Hi Geoff,

Could you try to replace the personal.xlsb file with a new simple one created with Excel 2007, and check if it will work?

Just want to make sure, you don't have Office 2003 side-by-side installed, right?