Results 1 to 6 of 6

workbooks.close statement?

This is a discussion on workbooks.close statement? within the Excel Questions forums, part of the Question Forums category; I'm using the command workbooks.open filename:= "G:\....." to open a workbook on my computer. Is there an equivalent statement to ...

  1. #1
    Board Regular vbacoder's Avatar
    Join Date
    Jul 2007
    Posts
    354

    Default workbooks.close statement?

    I'm using the command

    workbooks.open filename:= "G:\....."

    to open a workbook on my computer. Is there an equivalent statement to close the workbook after I have finished working on it?

    Many thanks,

    vcoder

  2. #2
    MrExcel MVP
    Moderator
    Joe4's Avatar
    Join Date
    Aug 2002
    Posts
    27,112

    Default

    I usually use:

    ActiveWorkbook.Close
    TIPS FOR FINDING EXCEL SOLUTIONS
    1. Use the built-in Help that comes with Excel/Access
    2. Use the Search functionality on this board
    3. A lot of VBA code can be acquired by using the Macro Recorder.

    "Give a man a fish, feed him for a day. Teach a man to fish, feed him for life!"

  3. #3
    Board Regular vbacoder's Avatar
    Join Date
    Jul 2007
    Posts
    354

    Default

    Thanks jm14!

    I tried various things like workbooks.close, but none worked. I'll try this.

    vcoder
    As far as the laws of mathematics refer to reality, they are not certain; and as far as they are certain, they do not refer to reality -
    Albert Einstein, (1879 - 1955)

  4. #4
    Board Regular vbacoder's Avatar
    Join Date
    Jul 2007
    Posts
    354

    Default

    I have used the ActiveWorkbook.Close statement, however, in some instances, I am asked (via dialogue prompts) whether I want to save the worksheet and whether I want to keep copied information in the clipboard for later use. I want to answer No, Yes, respectively, but I'm not sure how to invoke this in VB code.

    I would be very grateful for any advice on this.

    Many thanks,

    vcoder

  5. #5
    MrExcel MVP
    Moderator
    Joe4's Avatar
    Join Date
    Aug 2002
    Posts
    27,112

    Default

    Sorry for the delay in replying, I have been on vacation.

    The ActiveWorkbook.Close command has optional arguments, the first of which is whether or not you want to save changes. So if you want to answer "No" to that, it would look like this:
    Code:
    ActiveWorkbook.Close False
    Alternatively, you can suppress all warnings by putting this line of code before the other statement:
    Code:
    Application.DisplayAlerts = False
    This will suppress all warnings and choose the default. Just be sure to turn it back on afterwards with the command:
    Code:
    Application.DisplayAlerts = True
    Regarding the clipboard, I am not sure what the default is. If it is to keep it, they the above will work for that also. You can confirm it either way by testing.
    TIPS FOR FINDING EXCEL SOLUTIONS
    1. Use the built-in Help that comes with Excel/Access
    2. Use the Search functionality on this board
    3. A lot of VBA code can be acquired by using the Macro Recorder.

    "Give a man a fish, feed him for a day. Teach a man to fish, feed him for life!"

  6. #6
    MrExcel MVP
    Int'l Moderator
    Greg Truby's Avatar
    Join Date
    Jun 2002
    Location
    39 17' 15" N, -94 40' 26" W
    Posts
    9,770

    Default

    Unless you are opening this workbook and pulling stuff out or popping stuff in1 and the immediately closing it (i.e. if the user gains access to or has the ability to examine the workbook being opened = user can interact with the Excel) then I would shy away from using ActiveWorkbook.

    Better to use a global (not tested, just written "off the cuff"):
    Code:
    public g_wbToOpen as workbook
    sub foo()
    set g_wbtoopen = workbooks.open(filename:="g:\...")
    end sub
    
    sub oof()
    if not g_wbtoopen is nothing then
       application.displayalerts = false
       g_wbtoopen.close false
       application.displayalerts = true
    end if
    end sub

    1. "Popping stuff in", of course, doesn't make a lick of sense since you've told us you wish to close this WB w/o saving any changes. But I've seen odder stuff 'round here.
    Greg

    Work: XL 2003, 2007 and 2010 on Windows 7
    Please use CODE tags - especially for longer excerpts of code.

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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


DMCA.com