Yes, Excel has a file called Personal.xls. Go to Window > Unhide. If nothing shows up that means you have not stored any macros there. To get it to show, follow this to record a macro and Excel will automatically create Personal.xls.
Tools > Macro > Record Macro - this should bring up a dialog box that asks if you want to name the macro (Macro1, by default, you can change later if you want). You can also determine whether the macro will be part of the "Personal.xls" or "This Workbook"; in this case, you want Personal.xls, so that it will be available whenever you have Excel open. Click OK, then a small toolbar will appear with the stop button for the macro recorder. For this part, just set the format of any cell. Then click "Stop" button on the recorder toolbar.
Now, go to Tools > Macro > Macros and you will see Macro1 listed. Also, if you go to Window > Unhide, now you can unhide Personal.xls.
Now if you want to edit this macro, hit ALT + F11, which will bring up the Visual Basic Editor (VBE). On the left side you will see Project Explorer (listing all open documents and any macros attached), on the right should be a window that shows you the macro you just created.
Now for your macro, just copy it from the one sheet that you worked on originally, and then in the Project Explorer window, select Personal.xls, and go to Insert > Module, and a new window will appear. Paste the code from the other sheet into this window. Close out of VB, and then back in Excel be sure to hide Personal.xls first, then exit the program. When you do a dialog box will ask if you want to save changes to Personal.xls. Click OK.
Restart Excel, and you can start using the macro.
The first line is easy:
what is next? if I use ActiveWindow.Close, the Macro will always leave open the XLS File that this macro was created in ...
Now, ActiveWindow.Close will close the file because it is working with the underlying Personal.xls which is hidden.
Last edited by shades; 01-01-04 at 00:46.
old, slow, and confused
but at least I'm inconsistent!
i'm opening a dbf file in excel using macros and want to save this file as xls file and close it immediately after saving it. but everytime i run my macro, i am being prompted something like "this file is not a microsoft excel 97 format. If you wish to save...."
so, how can i open a dbf file and save it to excel and close it immediately without asking if i want to save it in a microsoft excel format?