Import macros from Excel 2000 into 2002 (Excel 2002 SP-2)

Topic

New Reply

I recently copied over my Excel files (created in Excel 2000) to another PC with Office XP SP-2 installed (i.e. Excel 2002, SP-2). Files without macros open OK. However, files in which I had macros assigned to buttons display the following message on opening: “Macros in this workbook are disabled because the security level is high, and the macros and have not been digitally signed or verified as safe. To run the macros, you can either you can either have them signed or change your security level. Click Help for more information.” I read the Help file but its kind of Greek to me. Anyway, I reduced the Macro security setting (under Tools | Options) from High to Medium, closed and restarted Excel and opened a file. Now I don’t get the previous alert message, but another one asking me to Enable or Disable macros…I click on Enable macros. Then I click on a button or two to see if macros are working; then I get this message: “PERSONAL.XLS could not be found. Check the spelling of the file name….(etc.)” I click on Tools | Macro > Macros… but there is not a single macro listed there! Where have all my macros disappeared? How can I even digitally sign them if they are not even listed? [BTW, I don’t want to register with Verisign just for this; the macros are strictly for my own use.] Do I need to recreate them all over again in Excel 2002? Can I just import them somehow from the other PC (Windows 98 SE; Excel 2000 SP-3)?

Reply | Quote

Replies

hi,

if i understand correctly, you are enabling a macro that calls your personal.xls. but for this to work you need to copy that personal.xls file from your old to your new pc also!

greetings,

Reply | Quote

If I copy over personal.xls, where should I place it? In Excel’s startup folder (whichever that may be)? BTW, the Excel 2002 is on a PC running WinXP Pro, so I log in with my name (I have Administrator privileges).

Reply | Quote

If you start XL and launch VBA (Alt F11) and type the following in the immedaite window it should indicate the path you need (XLStart)

print Application.StartupPath

Or you can place in the system wide XLStart folder which you can find in the Office folder (typically C:Program FilesMicrosoft OfficeOffice10XLstart).

As regards signing your macro projects, look for a file call Selfcert.exe, which should be in your Office folder. If you run that it will guide you through creating a digital signature which is perfectly adequate for the use you intend.

Andrew C

Reply | Quote

I did the Alt-F11 and it opened VB but there was no immediate window for me to type (VB opens with some panes highlighted on the left side of screen, but no box). Anyway, I copied the file over to XLStart folder listed under my name in Documents and settings and now I can see the macros! Thanks. On a quick check, they all seem to be there.
This begs another Q: presumably, Excel 2002 doesn’t use personal.xls (like Excel 2000) so is there any way for me to get rid of the file after I have, uh, “extracted”/”copied”/”moved” the macros from it into excel 2002? How do I do this “extraction” (that way, hopefully, they will not require a reference to a file to run)?
I will try self-signing with selfcert.exe later today and let the forum know if I run into any trouble. The ideal would be to run the macros from within Excel 2002 without reference to any file and they should be “converted” into excel 2002 format and be accessible even with High Macro security setting.

Reply | Quote

XL2002 uses Personal.xls in the same manner as XL2000 (and indeed XL97), so I am not sure what problem you are experiencing. If you palce your old Personal.xls in the XLStartup folder you should no have problems as your macros should be available at all times. If you are having difficulties please post back.

As regards the immediate window in VBA, press Ctrl-G to toggle it on and off (Or goto View, Immediate Window).

Andrew C

Reply | Quote

Actually, I am not having any problems with the macros (so far). Now I am just wondering aloud that if I create new macros within Excel 2002, where do they get stored? Unlike Excel 2000, I don’t think they get stored in a deafult/hidden file named personal.xls (because there was no such file before I copied it over from the other PC). Or is it that there was no such file simply because I had not created any new macros in Excel 2002 and if I had created some then Excel would have automatically created a file named personal.xls? Bottom line is that for simplicity’s sake I would prefer all of my macros to be one single “repository”; it makes moving things around easier. Also, it will make it easier to migrate to the forthcoming Office 11 if I knew where all macros get stored in Excel 2002.

Reply | Quote

Excel 2002 still uses Personal.xls in the XLStart folder in exactly the same way it was in previous versions of Excel since Excel 95 (I believe). Personal.xls is not present in a clean new installation; it gets created automatically the first time you record a new macro and select Personal Macro Workbook as the place to store it.

Reply | Quote

Ah, that expalnis it. For some reason, I always thought that personal.xls was used by Excel not just to store macros but also your startup settings (template, toolbars, etc.) So I guess its just a repository for macros and any new ones I create in Excel 2002 will automatically be added there. Er, right?
I have run into another problem – I did a search of my whole computer and there is no file named Selfcert.exe in any folder. Help.

Reply | Quote

Most Excel application settings are in the Windows registry.

Custom toolbars and lists are stored in C:Documents and SettingsusernameApplication DataMicrosoftExcelExcel10.xlb.

Templates are stored in the user templates folder, usually C:Documents and SettingsusernameTemplates.

Selfcert.exe should be in C:Program FilesMicrosoft OfficeOffice10. Maybe you should check your Office installation to see whether this option is installed.

Reply | Quote

Not necessarily automatic. When you record a macro you have the option to store it in a number of locations, (Personal.xls, ThisWorkbook, any other open workbook, or New Workbook), so just make sure that Personal.xls is selected in the Store Macro in dropdown list.

Andrew

Reply | Quote

You might want to read This Post which gives an explaination of Personal.xls.

Reply | Quote

I had to modify my Office XP installation so that selfcert.exe got installed. After that, I created a digital signature for myself, opened personal.xls, then Tools | Options | Security and added my digital signature, saved the file, dragged it to the startup folder to replace the (now old) personal.xls file (the one without the signature). Now when I open my files with macros in them, I can use them and they are also listed under Tools | Macros. Things appear to be working OK so far.
Thanks one and all for your help. It was very satisfying to get this issue resolved (it had been bugging me for some time).

Reply | Quote

You should not have to sign the Personal.xls project at all. Since it resides in the XLSTART folder, the macro security setting is bypassed automatically. You should get no macro warning when accessing any macro in it. If you do, the location of personal.xls is obviously incorrect, since it is not loaded from what XL considers its xlstart folder..

Reply | Quote