none
Close Workbook Help RRS feed

  • Question

  • I am looking to close a workbook that will be open and am wondering if there is a way to close a workbook without having the exact full name of the workbook? The workbook that will be open will always begin with "New Equipment" but may not be the same full name all of the time.

    This is what I have come up with so far but it does not like the *New Equipment* part :(

    Private Sub Workbook_Open()

           

        On Error GoTo ErrorControl

        Dim filePath As String

       

        ThisWorkbook.Activate

        Workbooks("*New Equipment*").Close SaveChanges:=False

        ThisWorkbook.Activate

           

        filePath = ("\\NCAPP\users\" & fOSUserName & "\My Documents\*New Equipment*")

           

        If Dir(filePath) <> "" Then

            kill filePath

        End If

     

        Application.Quit

           

    ErrorControl:

        Application.Quit

           

    End Sub

    Anyone have any ideas on how/if this can be done?

    Any assistance is always greatly appreciated :)

    Thank you

    Wednesday, February 24, 2016 7:03 PM

Answers

  • Try this:

        Dim wbk As Workbook
        ' Loop through all open workbooks
        For Each wbk In Workbooks
            ' Check the name
            If wbk.Name Like "New Equipment*" Then
                ' Close it
                wbk.Close SaveChanges:=False
                ' And exit the loop
                Exit For
            End If
        Next wbk


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    • Proposed as answer by David_JunFeng Thursday, February 25, 2016 1:29 AM
    • Marked as answer by rstreets2 Friday, February 26, 2016 10:51 PM
    Wednesday, February 24, 2016 7:19 PM
  • You could use

        Application.Wait Now + Time(0, 0, 3)

    The last line

        wbk.Close SaveChanges:=False

    won't work since the workbook has already been closed.


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    • Marked as answer by rstreets2 Saturday, March 5, 2016 1:01 AM
    Saturday, February 27, 2016 10:09 AM

All replies

  • Try this:

        Dim wbk As Workbook
        ' Loop through all open workbooks
        For Each wbk In Workbooks
            ' Check the name
            If wbk.Name Like "New Equipment*" Then
                ' Close it
                wbk.Close SaveChanges:=False
                ' And exit the loop
                Exit For
            End If
        Next wbk


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    • Proposed as answer by David_JunFeng Thursday, February 25, 2016 1:29 AM
    • Marked as answer by rstreets2 Friday, February 26, 2016 10:51 PM
    Wednesday, February 24, 2016 7:19 PM
  • Hi Hans and thank you for your assistance. Your suggestion worked great! I was wondering if you knew how to insert maybe a 2-3 second pause in between these two lines of code? 

        Dim filePath As String

        Dim wbk As Workbook

        For Each wbk In Workbooks

            If wbk.Name Like "*New Equipment*" Then

                wbk.Close SaveChanges:=False

                Exit For

            End If

        Next wbk

    ‘’’’ 2-3 second pause’’’’

           

        filePath = ("\\NCAPP\users\" & fOSUserName & "\My Documents\*New Equipment*")

           

        If Dir(filePath) <> "" Then

            kill filePath

        End If

     

        wbk.Close SaveChanges:=False


    Friday, February 26, 2016 10:55 PM
  • You could use

        Application.Wait Now + Time(0, 0, 3)

    The last line

        wbk.Close SaveChanges:=False

    won't work since the workbook has already been closed.


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    • Marked as answer by rstreets2 Saturday, March 5, 2016 1:01 AM
    Saturday, February 27, 2016 10:09 AM
  • Thanks Hans! Again worked like a charm! You are wonderful!
    Saturday, March 5, 2016 1:02 AM