personal.xls revisited (Excel 2003)

Topic

New Reply

I have my personal.xls file located at:
C:Documents and SettingsAbramsMApplication DataMicrosoftExcelXLSTART (where it has been forever)

I have a custom menu bar for the 20 macros in the personal.xls that works fine.

Today, all of a sudden, the personal.xls file does not load at Excel startup (of a new worksheet) but does load on a saved worksheet.

In addition, when I go to Tools/Macro/Macros, it is blank – BUT – I can run them from the custom menu bar !
After I run a macro from the menu bar, I go back to Tools/Macro/Macros and the macros are then listed !

This just started happening today – I checked with the network guys and no new updates or patches have been installed for a while.

This is just weird. Has anyone eles experienced this? Is it fixable?

Thanks for any insight.

Michael

Reply | Quote

Replies

It looks like Excel has lost or changed the path of its autostart folder.
You could try recording a new macro in your Personal Macro Workbook, quit Excel and see what happens – does the Personal.xls file at C:Documents and SettingsAbramsMApplication DataMicrosoftExcelXLSTART get updated, or is a new Personal.xls file created in another folder?

Reply | Quote

when I try that I get an error message:
Personal Macro Workbook in the startup folder must stay open for recording.
Click OK
Unable to record.

Any ideas?

Michael

Reply | Quote

Something has gone really wrong. Try Jan Karel Pieterse’s Systematic Approach to Behavioral Problems in XL.

Reply | Quote

Can you locate your personal.xls? If so, move it out of the xlstart folder and try if you can open it normally.
If that fails, try the options listed here:
http://www.jkp-ads.com/articles/corruptfiles.asp%5B/url%5D

Reply | Quote

Can you locate your personal.xls

It is two places:
C:Documents and SettingsAbramsMApplication DataMicrosoftExcelXLSTART
and
C:Program FilesMicrosoft OfficeOFFICE11XLSTART

Is this file supposed to be in both places? If yes, should I move them both out (temporarily?)

What is most weird, is that this only occurs on a “New” worksheet. Once I run the macro through the menu bar,
the macros then appear in Macro list!

All previously saved spreadsheets are fine.

Michael

Reply | Quote

What are the last modified dates of the two Personal.xls files?

When you run a macro, Excel will open the workbook in which it is stored. You could do the following:
– Select Tools | Customize…
– Right-click a toolbar button that is bound to a macro in Personal.xls.
– Select Assign macro from the popup menu.
– Look at the path of the file.

Reply | Quote

Edited by HansV to replace Word document with the picture it contained.

What are the last modified dates of the two Personal.xls files?

C:Documents and SettingsAbramsMApplication DataMicrosoftExcelXLSTART = 1/29/09 1:32PM

C:Program FilesMicrosoft OfficeOFFICE11XLSTART = 1/29/09 1:45PM

When you run a macro, Excel will open the workbook in which it is stored. You could do the following:
– Select Tools | Customize…
– Right-click a toolbar button that is bound to a macro in Personal.xls.
– Select Assign macro from the popup menu.
– Look at the path of the file.

See attached – I do not see a path listed.

Would it be easiest if I had my IT people just re-install Excel?

Michael

Reply | Quote

Try this:
– Quit all instances of Excel.
– Move the Personal.xls workbook in C:Documents and SettingsAbramsMApplication DataMicrosoftExcelXLSTART to another folder, e.g. your My Documents folder.
– Copy the Personal.xls workbook from C:Program FilesMicrosoft OfficeOFFICE11XLSTART to C:Documents and SettingsAbramsMApplication DataMicrosoftExcelXLSTART.
– Start Excel and see if the macros are available from the start.

Reply | Quote

These are the types of things that cause premature graying.

It still does not work – but I can still run macros using the custom menu bar. Then the macros are there.

Wow….

Thanks Hans for trying – do you think a re-install of Excel would solve this?

Michael

Reply | Quote

Before you take that step, let’s try something else:
– Start Excel.
– Run a macro from your custom menu bar to make the macros available.
– Activate the Visual Basic Editor (Alt+F11).
– Look at the Project Explorer on the left hand side.
– You should see your new workbook Book1 – actually, it says VBAProject (Book1) – and at least one other workbook.
– Is the other one named Personal.xls?
– If so, activate the Immediate window, and type

? Workbooks(“Personal.xls”).FullName

– Press Enter and note the result.
– If there is no Personal.xls in the Project Explorer, what’s the name of the other workbook?

Reply | Quote

Edited by HansV to replace Word doc with the picture it contained – less clicking for those who read the thread

Immediate window results:
?Workbooks(“Personal.xls”).FullName
C:Program FilesMicrosoft OfficeOFFICE11xlstartPersonal.xls

See attached for Project Explorer

Michael

Reply | Quote

Could you try the following?
– Select Start | Run…
– Type regedit and press Enter or click OK.
– Navigate to HKEY_CURRENT_USERSoftwareMicrosoftOffice11.0CommonGeneral
– The right hand pane should contain a value named Xstart. What is its value?

(On my PC, with Office XP aka Office 10.0, its value is XLSTART, meaning that Excel uses the default location.)

Reply | Quote

The entry reads XLSTART

Michael

Reply | Quote

I’m stumped. Have you actually tried Jan Karel Pieterse’s troubleshooting guide?

Reply | Quote

I will start from the top and see if anything helps.

Thank you very much for all the time you spent on my issue.

Michael

Reply | Quote

Re Hans’ third step, did you copy the workbook from one folder to the other or did you move it? You need to move it so that you only have one Personal.xls file in one XLSTART directory.

Reply | Quote

Yes Rory – I did actually move it. This is crazy !

Michael

Reply | Quote

If you moved it from the Program Files directory, why does your debug statement above show that it is still there?

Reply | Quote

Because I moved it back when it originally didn’t work. When I go to work tomorrow, I will redo the debu process AFTER I remove the file from Program files……

Good catch !! Hopefully this will show something that may help. This is so crazy !

Michael

Reply | Quote

OK_ when I remove the personal.xls file from C:Program FilesMicrosoft OfficeOFFICE11XLSTART
and try to run a macro from the custom menu bar – I get this message:
“personal.xls could not be found…etc…”

But, when I replace it there, and remove it from C:Documents and SettingsAbramsMApplication DataMicrosoftExcelXLSTART, I can run a macro from the custom menu bar and then the list of macros appears.

This is getting weirder & weirder….. :{{

Michael

Reply | Quote

Try this:
– Select Tools | Options…
– Activate the General tab.
– In the box “At startup, open all files in”, enter C:Program FilesMicrosoft OfficeOFFICE11XLSTART
– Quit Excel.
– Move Personal.xls back to C:Program FilesMicrosoft OfficeOFFICE11XLSTART.
– Restart Excel.

Reply | Quote

Bingo – looks like we have a winner !!!

Thanks guys for sticking it out with me. I appreciate your time and efforts so much !!

Have a great week !

Michael Abrams

Reply | Quote