none
File Not Found Help RRS feed

  • Question

  • Greetings - 

    Anyone know how to make the blow code end (exit sub) if there are no files in the path listed?

    Private Sub Workbook_Open()

            kill "\\NCAPP\users\" & fOSUserName & "\My Documents\*New Equipment*"

            Application.Quit

    End Sub

    Thanks in advance for any assistance you may be able to provide :)

    Wednesday, February 24, 2016 1:37 AM

Answers

  • Hi,

    Do you want to do:
      if a specific file does not exist, do nothing. If it exists, delete it.
    If this is correct, you can write code: 
    Dim filePath as String
    filePath = (describe the file name with full path, that you want to delete)
    If Dir(filePath) <> "" Then
        Kill filePath
    Else
        MsgBox "[" & filePath & "] does not exist."
    End If
    Regards.
    • Marked as answer by rstreets2 Wednesday, February 24, 2016 6:59 PM
    Wednesday, February 24, 2016 3:16 AM

All replies

  • Hi,

    Do you want to do:
      if a specific file does not exist, do nothing. If it exists, delete it.
    If this is correct, you can write code: 
    Dim filePath as String
    filePath = (describe the file name with full path, that you want to delete)
    If Dir(filePath) <> "" Then
        Kill filePath
    Else
        MsgBox "[" & filePath & "] does not exist."
    End If
    Regards.
    • Marked as answer by rstreets2 Wednesday, February 24, 2016 6:59 PM
    Wednesday, February 24, 2016 3:16 AM
  • Hi Ashidacchi - 

    Thank you for your response. This worked perfectly! That is exactly what I was looking for. I did have one other question you might be able to assist with...

    I added some additional code to yours...I am looking to close a workbook that will be open and am wondering if there is a way to close any workbooks 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()

           

        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

        Else

            Exit Sub

        End If

            Application.Quit

    End Sub

    Do you know if this will even be possible? Maybe I should post a new thread on this one?

    Let me know your thoughts.

    Thanks again for your assistance!

    Best,

    Randy

    Wednesday, February 24, 2016 5:25 PM
  • Hi, 
    Here's a sample code: close all workbooks except myself
    You don't have to know file name now opened, but should know workbook-name. 

    Call this procedure somewhere you like, e.g. button_Click.
    (call prc_Close_Workbooks)
    Public Sub prc_Close_Workbooks()
        ' --- get myself (workbook name)
        Dim myWbName As String
        Dim wb As Workbook
        myWbName = ActiveWorkbook.Name
        ' --- close all workbooks except myself
        For Each wb In Workbooks
            If (wb.Name <> myWbName) Then
                Workbooks(wb.Name).Close SaveChanges:=False
            End If
        Next wb
    End Sub
    Regards.
    Wednesday, February 24, 2016 10:29 PM