Results 1 to 5 of 5
  1. #1
    3 Star Lounger
    Join Date
    Jan 2002
    Location
    CT
    Posts
    361
    Thanks
    0
    Thanked 1 Time in 1 Post

    Saving an open worksheet (Office XP)

    He all.......

    I've got a situation where I want a maco to check to see if a workbook is open, and then if it is open, save it and close it.

    Say for example I've got three workbooks.........Book1.xls, Book2.xls and Book3.xls.

    I've got a maco in Book1 that needs to to save and close Book2.xls and Book3.xls IF they are open. They aren't always open though........just sometimes.

    I've got the maco set to save and close the books if they are open, but have tried a few IF statements to have it check to see if it is open or not with no success. Obviously if it isn't open and I try to ACTIVATE it I get an error. So, if one or both are not open I need to skip over the code lines that call for the ACTIVATE, SAVE and CLOSE. At least that's how I think I need to attack this.

    Thanks,
    BH

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

    Re: Saving an open worksheet (Office XP)

    You can loop through all open workbooks to see if the one you're looking for is among them:

    Sub CloseIt(WorkbookName As String)
    Dim wbk As Workbook
    For Each wbk In Workbooks
    If wbk.Name = WorkbookName Then
    wbk.Close SaveChanges:=True
    Exit For
    End If
    Next wbk
    End Sub

    Here is an example of how to use this procedure:

    Sub CloseSomeWorkbooks()
    CloseIt "Book2.xls"
    CloseIt "Book3.xls"
    End Sub

    Here is another version of the CloseIt procedure. Instead of looping, it uses On Error Resume Next to suppress the error that would occur if the workbook isn't open. You use this one the same way.

    Sub CloseIt(WorkbookName As String)
    Dim wbk As Workbook
    On Error Resume Next
    Set wbk = Workbooks(WorkbookName)
    On Error GoTo 0
    If Not wbk Is Nothing Then
    wbk.Close SaveChanges:=True
    End If
    End Sub

  3. #3
    3 Star Lounger
    Join Date
    Jan 2002
    Location
    CT
    Posts
    361
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Saving an open worksheet (Office XP)

    Han,

    As is so often the case this seems to be just a bit over my head.

    I've tried to work with all the examples but am not getting it to work. Here is what I used from your third example where you used the "On error resume next" statement.

    This would be run from file called "Last_Form.xls". The idea is to open "Layout Sheet.xls" and close and save "Active Layout Sheet.xls" if it is open.

    Thanks for your help........
    BH


    Sub New_Form()
    '
    ' New_Form Macro
    ' Macro recorded 1/13/02 by B.H. Davis
    '



    Application.DisplayAlerts = False

    Workbooks.Open Filename:="D:MS Office DocumentsLayout SheetsLayout Sheet.xls"

    Sub CloseIt "D:MS Office DocumentsLayout SheetsActive Layout Sheet.xls"
    Dim wbk As Workbook
    On Error Resume Next
    Set wbk = Workbooks "Active Layout Sheet.xls"
    On Error GoTo 0
    If Not wbk Is Nothing Then
    wbk.Close SaveChanges:=True
    End If
    End Sub



    Application.DisplayAlerts = True


    End Sub

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

    Re: Saving an open worksheet (Office XP)

    You cannot nest one procedure (Sub ... End Sub) within another one. Try this:
    <code>
    Sub New_Form()
    '
    ' New_Form Macro
    ' Macro recorded 1/13/02 by B.H. Davis
    '
    Application.DisplayAlerts = False
    Workbooks.Open Filename:="D:MS Office DocumentsLayout SheetsLayout Sheet.xls"
    On Error Resume Next
    Workbooks("Active Layout Sheet.xls").Close SaveChanges:=True
    Application.DisplayAlerts = True
    End Sub</code>

  5. #5
    3 Star Lounger
    Join Date
    Jan 2002
    Location
    CT
    Posts
    361
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Saving an open worksheet (Office XP)

    Han,

    That did the trick. I suspected you couldn't nest routines but I tried everything I could think of and still hadn't gotten it right.

    As always.....your help is valuable and appreciated.

    Sincerely,
    BH Davis

Posting Permissions

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