Results 1 to 12 of 12

Thread: Macro's

  1. #1
    2 Star Lounger
    Join Date
    Apr 2001
    Location
    Melbourne, Australia
    Posts
    118
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Macro's

    Hello,

    This maybe a very simple question. Where does excel save macro's? I know you can locate them in the Tools, Macro's...menu. I am looking the for actual file.

    Thanks Kindly,

    Kerrrie

  2. #2
    Star Lounger
    Join Date
    Jan 2001
    Location
    Newcastle, New South Wales, Australia
    Posts
    81
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Macro's

    When you record a new macro it asks if you want to store it in the active workbook. The other choice is personal.xls. If you choose Window and the Unhide command is not greyed out, then click it and it will list the files that are hidden. Personal.xls is usually one of these. If it is greyed out then you have no personal.xls and the macros are probably in the current workbook.
    In earlier versions of XL, the macros were in a macro module that looked like a worksheet, but from xl97 (?) on they are stored inside personal.xls
    Ruth

  3. #3
    zenood
    Guest

    Re: Macro's

    Hello kerrie --

    An addition to Ruth's post.
    If you can see the macros in the Tools, Macros dialog box, then you can try this --
    o By default the dialog box will show macros in all open workbooks.
    o In the dialog box, click on the down-arrow against 'Macros in'.
    o If you select a file from the list (assuming there are more than one files open), and there are macros in the selected file, then you will know where to look for the macro.

    Hope this helps :-)
    Khushnood

  4. #4
    zenood
    Guest

    Re: Macro's

    One more thought
    If you want to 'edit' your macros then you have to use the VBE -- Visual Basic Editor.

    You can start that by pressing Alt+F11 from Excel

    This will take you to the VBA project explorer
    Then you can 'expand' the relevant project (if it is not password-protected) and look at the code for any macro

    Khushnood

  5. #5
    2 Star Lounger
    Join Date
    Apr 2001
    Location
    Melbourne, Australia
    Posts
    118
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Macro's

    Hi Ruth,

    Thanks for the info, however I create a macro and then the next day, I open excel 2000 and the button is on the toolbar however the macro is missing. If I try and assign it again nothing is listed in the "assign macros" and I check all listings.
    For some reason they keep disappearing.

    Thanks Kindly,

    Kerrie

  6. #6
    3 Star Lounger
    Join Date
    Apr 2002
    Location
    Redditch, Worcestershire, England
    Posts
    233
    Thanks
    0
    Thanked 22 Times in 21 Posts

    Re: Macro's

    When you say "the next day" - is it always the next day, or do they disappear even if you just close and reopen the file in question?

    Jeremy

  7. #7
    New Lounger
    Join Date
    Jun 2001
    Posts
    1
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Macro's

    Does anyone know where PowerPoint stores macros. I think Excel uses personal.xls but does PP have a similar facility?

  8. #8
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Ankeny, Iowa, USA
    Posts
    298
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Macro's

    If you create a macro and don't save the file you've created it in, then the macro won't be saved, either. Is this possibly what's happening?

    Something else you can do would be after you create the macro, go to the VB Editor, find your macro there, and then export the file. That way, at least if it disappears again you can just reimport it until you figure out what's happening...

  9. #9
    Star Lounger
    Join Date
    Jan 2001
    Location
    Newcastle, New South Wales, Australia
    Posts
    81
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Macro's

    After you have created the macro, does XL ask you if you want it in the current workbook or in personal.xls?
    Try explicitly saving personal.xls before exiting XL.
    If you exit XL and go back in immediately rather than waiting until next day, does it get lost?
    Ruth

  10. #10
    zenood
    Guest

    Re: Macro's

    Here's my 2 cents !

    Do you record your macro using the Record new macro command ?
    Usually the recording happens in the active workbook.
    If so, are you saving the file in which you created the macro ?

    If you record new macros in a new workbook (maybe Book1), it runs during the current 'session' of Excel.
    But if that file is not saved, then if you exit and re-start Excel, the macro is lost.

    However, the toolbar button stays, and when you click it in the next session of Excel, it tries to locate that macro in the file which was not saved ! :-)

    Also, if you have a Personal.xls workbook (in which other macros recorded earlier, are stored), any new macros you record, can be saved in that file

    Hope this helps

  11. #11
    2 Star Lounger
    Join Date
    Apr 2001
    Location
    Melbourne, Australia
    Posts
    118
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Macro's

    Hi Ruth,

    It does not come up asking to be saved. Is there a way to save it to the Persnal.xls?

    Kerrie

  12. #12
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Adelaide, South Australia, Australia
    Posts
    387
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Macro's

    When you go to Tools/Macro/Record New Macro and get the Record Macro box there is (or should be) a dropdown list of places to save the macro. Choose Personal.xls at this stage. This is before you begin to record the macro.

    I remember I used to get in trouble because I would forget to change the save location to Personal.xls and XL would save the macro in the current workbook and it was unavailable in other workbooks. However now when I go to record a macro the Save location always comes up as Personal.xls, even with a new workbook. So I guess it is possible to set the default Save location to Personal.xls, but I do not know how I did it.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •