Results 1 to 14 of 14
  1. #1
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Richmond, Virginia, USA
    Posts
    416
    Thanks
    0
    Thanked 0 Times in 0 Posts

    ActiveWorksheet.Close Question (easy)

    At the end of a macro, I have a ActiveWorksheet.Close command. I want this to close the workbook (or Excel, whichever is easiest) and not save any changes that were made. Right now, the macro is almost perfect-it runs start to finish with almost no user intervention, but it prompts you to save the changes before it closes the workbook and Excel. I just want to eliminate that one step. <img src=/S/help.gif border=0 alt=help width=23 height=15>

    Thanks in Advance,
    Becky

  2. #2
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Kortrijk, Belgium
    Posts
    571
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: ActiveWorksheet.Close Question (easy)

    just add

    Application.DisplayAlerts = False

    before the closing command line

    and add

    Application.DisplayAlerts = True

    after that command.

  3. #3
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Richmond, Virginia, USA
    Posts
    416
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: ActiveWorksheet.Close Question (easy)

    Thanks for your reply, but I'm not sure I'm doing this right <img src=/S/smile.gif border=0 alt=smile width=15 height=15>. Here's exactly what I put at the end of my macro:

    Application.DisplayAlerts = False
    ActiveWorkbook.Close
    Application.Quit
    Application.DisplayAlerts = True
    End Sub

    Is this the right order? I'm still getting prompted to save my workbook.

  4. #4
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Kortrijk, Belgium
    Posts
    571
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: ActiveWorksheet.Close Question (easy)

    This should work. Are you sure this is the only workbook that is open when you run that code? By adding application.quit you are actually closing not only this workbook but the Excel-application. Maybe you should try it out without that line and see if it works then.

  5. #5
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Richmond, Virginia, USA
    Posts
    416
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: ActiveWorksheet.Close Question (easy)

    Actually, the macro is in one spreadsheet (Fixit) that opens another worksheet (Fedex), makes changes to the second worksheet, and that's the file I want to have closed without saving. I thought that the ActiveWorksheet.Close command would just close the Fedex workbook then the Aplication.Quit command would exit Excel and the Fixit workbook.

    <img src=/S/sad.gif border=0 alt=sad width=15 height=15> Sorry I didn't explain the whole process from the start-if you want the whole story (from another post), click <A target="_blank" HREF=http://www.wopr.com/cgi-bin/w3t/showflat.pl?Cat=&Board=xl&Number=41708&page=0&view =collapsed&sb=5>here</A>.

    Becky

  6. #6
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Kortrijk, Belgium
    Posts
    571
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: ActiveWorksheet.Close Question (easy)

    I tried it out in Excel 2000, and it should work. I suggest you do something similar to what I have done, just to test if this works. I made a new workbook which I called test.xls and in the VBE I added a new module, and entered the following macro:
    <pre>Sub test()
    Application.DisplayAlerts = False
    ActiveWorkbook.Close
    Application.Quit
    Application.DisplayAlerts = True
    End Sub
    </pre>


    As you can see, exactly the code you posted. Then I made a new workbook, added something in a cell (to make sure that this workbook was changed, meaning that normally Excel should prompt to save the file before closing) and then run the macro test.xls!test when the last open workbook was the active one. The active workbook is closed, without saving, and Excel quits as it should.
    As you explain it, it normally should do what you wrote.

  7. #7
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Nottingham, UK, Nottinghamshire, England
    Posts
    190
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: ActiveWorksheet.Close Question (easy)

    Why not just code 'ActiveWorkbook.Close SaveChanges:=False' (see Help for full syntax of Close method). This will close the workbook without saving, as it says on the tin.

    Saves all the other coding!

  8. #8
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: ActiveWorksheet.Close Question (easy)

    IMHO

    ActiveWorkbook.Close False

    is better.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  9. #9
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: ActiveWorksheet.Close Question (easy)

    I see this has already been suggested. Sorry for the duplication.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  10. #10
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Richmond, Virginia, USA
    Posts
    416
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: ActiveWorksheet.Close Question (easy)

    <img src=/S/shrug.gif border=0 alt=shrug width=39 height=15> Unfortunately, it isn't working for me. If anyone wants to play with it (if you're dying of boredom), the file that opens the fedex.xls file is in the string referenced above (Post#: 42310 by Rory). It isn't the end of the world or anything. Thanks to everyone for your help!

    <img src=/S/smile.gif border=0 alt=smile width=15 height=15> Becky

  11. #11
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: ActiveWorksheet.Close Question (easy)

    Are you positive that it is the FEDEX workbook that is causing the message and not the Fixit workbook? Try changing your code to:

    <pre> Application.DisplayAlerts = False
    ActiveWorkbook.Close SaveChanges := False
    ActiveWorkbook.Close SaveChanges := False
    Application.DisplayAlerts = True
    Application.Quit
    End Sub
    </pre>


    That should close both workbooks before the Quit.
    Legare Coleman

  12. #12
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Nottingham, UK, Nottinghamshire, England
    Posts
    190
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: ActiveWorksheet.Close Question (easy)

    Hi Becky,

    Strange that it isn't working for you. I took Rory's attachment, added the Close method with SaveChanges set to False, and it worked just fine! The amended file is attached for you to try.
    Attached Files Attached Files

  13. #13
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Richmond, Virginia, USA
    Posts
    416
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Thanks

    <img src=/S/blush.gif border=0 alt=blush width=15 height=15> I'm so embarrassed! Your ideas work just fine, but I forgot about my DMS (document management system) which wants to have a hand in this process. Unfortunately, the macro won't work with the DMS in the way.

    I do sincerely appreciate everyone's help, though. I'm sorry for sending everyone on a wild goose chase!

    Becky

    Lesson learned: If something should work, make sure there's nothing specific that's causing it not to work.

  14. #14
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Nottingham, UK, Nottinghamshire, England
    Posts
    190
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Thanks

    No worries - it's nice to know that the obvious answer really was obvious! Hope you manage to persuade your DMS to be kind to you!

Posting Permissions

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