Results 1 to 3 of 3

Thread: Excel Macros

  1. #1
    Join Date
    Dec 2003
    Location
    singapore
    Posts
    1

    Smile Unanswered: Excel Macros

    Hi - can anyone please assist:

    When you create a macro in MS Word, it is placed into normal.dot

    Is it possible to create a Macro in Excel that sits at the program level - rather than in a specific XLS File?

    Now I want to be able to create a Macro that automatically saves and closes the current File and leave the others open.

    The first line is easy:
    ActiveWindow.Save

    what is next? if I use ActiveWindow.Close, the Macro will always leave open the XLS File that this macro was created in ...

  2. #2
    Join Date
    Oct 2003
    Posts
    1,091
    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:
    ActiveWindow.Save

    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!

    Rich
    (retired Excel 2003 user, 3/28/2008)

    How to ask a question on forums

  3. #3
    Join Date
    Feb 2004
    Posts
    1
    hello! can anyone please help me on this:

    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?

    here are the lines that i use:

    Workbooks.Open FileName:="C:\folder\file.DBF"
    ActiveWorkbook.SaveAs FileName:="E:\russell\vb\NEAX1.xls", _ FileFormat:=xlExcel9795, Password:="", WriteResPassword:="", _ ReadOnlyRecommended:= False, CreateBackup:=False
    ActiveWorkbook.Save True
    ActiveWindow.Close True

Posting Permissions

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