Results 1 to 7 of 7
  1. #1
    Star Lounger
    Join Date
    Mar 2005
    Location
    London, Hampshire, United Kingdom
    Posts
    59
    Thanks
    0
    Thanked 0 Times in 0 Posts

    MACRO TO CLOSE (2007)

    Probably obvious. But can anyone give me some lines of VBA code which will save and close a macro-enabled workbook?

    Thanks!

  2. #2
    Bronze Lounger
    Join Date
    Sep 2007
    Posts
    1,203
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: MACRO TO CLOSE (2007)

    This will save the workbook in the same location:

    Dim strPath As String

    strPath = ThisWorkbook.Path
    If Not Right(strPath, 1) = "" Then
    strPath = strPath & ""
    End If

    ActiveWorkbook.SaveAs Filename:=strPath & "workbook name.XLS"
    ActiveWorkbook.Close

    HTH.

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: MACRO TO CLOSE (2007)

    If the workbook has been saved before, the line

    ActiveWorkbook.Close SaveChanges:=True

    will save and close the active workbook. If you want to close a specific workbook (not necessarily the active one), use

    Workbooks("MyWorkbook.xlsm").Close SaveChanges:=True

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: MACRO TO CLOSE (2007)

    Nathan,

    If the workbook already exists, there is no need to specify the filename again. And in Excel 2007, a macro-enabled workbook has extension .xlsm.

  5. #5
    Star Lounger
    Join Date
    Mar 2005
    Location
    London, Hampshire, United Kingdom
    Posts
    59
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: MACRO TO CLOSE (2007)

    Thank you both. If I may trouble you further: is there a line which will close Excel?

    Thanks again.

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: MACRO TO CLOSE (2007)

    To quit Excel:

    Application.Quit

  7. #7
    Bronze Lounger
    Join Date
    Sep 2007
    Posts
    1,203
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: MACRO TO CLOSE (2007)

    My apologies, I should have noticed that the question related to 2007, as i am not a user, I would not have responded. I also failed to mention that the code may or may not fully meet the criteria, though should be adaplable to suit.

Posting Permissions

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