VBA Help - Add Error Handler to Working Code - Open File

[Johnny Thunder]

Hi guys, working on a code that I got to work but need a small addition to this code.

Currently, the code will loop thru a named range of file names and then opens and deletes specific tabs and then closes and loops to the next file. I need to add a piece of code that will move on if it comes across a filename that is not in the folder and if the last file in the list of file names doesn't exist then to be completed with the loop.

Any help is appreciated. Almost there with this project!

Option Explicit
'--------------------------------------------------------
'--- Deletes the two new sheets
'---------------------------------------------------------
Public Sub DeleteSheet()


    Dim SourceSheet As Worksheet, SourceSheet2 As Worksheet
    Dim folder As String, filename As String, vFilename As String, vFileWKBK, vfilepath As String
    Dim vCell As Range
    Dim DestBook As Workbook, destinationWorkbook As Workbook
        
    With Application
        .DisplayAlerts = False
        .ScreenUpdating = False
    End With
        
    For Each vCell In Range("LISTTERRITORYNAME").Cells
            vfilepath = ThisWorkbook.Path & "\"     'Uses the files folder location for directory
            vFileWKBK = Range("TBTPREFIX").Value & " - " & vCell.Value & ".xls*"
            vFilename = vfilepath & vFileWKBK
        
    Application.AskToUpdateLinks = False 'Supresses External links warning
   
        Set destinationWorkbook = Workbooks.Open(vFilename)
            
        destinationWorkbook.Activate
        
        On Error Resume Next
            destinationWorkbook.Sheets("R&O").Delete
            destinationWorkbook.Sheets("Executive Summary").Delete
            destinationWorkbook.Sheets("Sheet1").Delete
            destinationWorkbook.Sheets("Sheet2").Delete
        On Error GoTo 0
                           
        destinationWorkbook.Close True
        
        Next
  
    With Application
        .DisplayAlerts = True
        .ScreenUpdating = True
    End With


MsgBox "Sheets have been removed"


End Sub

 

[Fluff]

Try

 If Dir(vFILENAME) <> "" Then
   Set destinationWorkbook = Workbooks.Open(vFILENAME)
            
        destinationWorkbook.Activate
        
        On Error Resume Next
            destinationWorkbook.Sheets("R&O").Delete
            destinationWorkbook.Sheets("Executive Summary").Delete
            destinationWorkbook.Sheets("Sheet1").Delete
            destinationWorkbook.Sheets("Sheet2").Delete
        On Error GoTo 0
                           
        destinationWorkbook.Close True
   End If

 

[Johnny Thunder]

@Fluff , thank you for your help on this. I entered the code where I thought it needed to go but it is still throwing an error that it can't find a file.

Here is the code:

Public Sub DeleteSheet()


    Dim SourceSheet As Worksheet, SourceSheet2 As Worksheet
    Dim folder As String, filename As String, vFilename As String, vFileWKBK, vFilepath As String
    Dim vCell As Range
    Dim DestBook As Workbook, destinationWorkbook As Workbook
        
    With Application
        .DisplayAlerts = False
        .ScreenUpdating = False
    End With
        
    For Each vCell In Range("LISTTERRITORYNAME").Cells
            vFilepath = ThisWorkbook.Path & "\"     'Uses the files folder location for directory
            vFileWKBK = Range("TBTPREFIX").Value & " - " & vCell.Value & ".xls*"
            vFilename = vFilepath & vFileWKBK
        
    Application.AskToUpdateLinks = False 'Supresses External links warning
   
        Set destinationWorkbook = Workbooks.Open(vFilename)
            
        destinationWorkbook.Activate
        
        On Error Resume Next
            destinationWorkbook.Sheets("R&O").Delete
            destinationWorkbook.Sheets("Executive Summary").Delete
            destinationWorkbook.Sheets("Sheet1").Delete
            destinationWorkbook.Sheets("Sheet2").Delete
        On Error GoTo 0
                           
        destinationWorkbook.Close True
        
        Next
  
    With Application
        .DisplayAlerts = True
        .ScreenUpdating = True
    End With


MsgBox "Sheets have been removed"


End Sub

 

[Norie]

Try this, it should take handle any files not being found and will also output a list of any files not found to the Immediate Window (CTRL+G), which might be useful.

Public Sub DeleteSheet()
Dim SourceSheet As Worksheet, SourceSheet2 As Worksheet
Dim folder As String, filename As String, vFilename As String, vFileWKBK, vFilepath As String
Dim vCell As Range
Dim DestBook As Workbook, destinationWorkbook As Workbook

    With Application
        .DisplayAlerts = False
        .ScreenUpdating = False
    End With

    For Each vCell In Range("LISTTERRITORYNAME").Cells
        vFilepath = ThisWorkbook.Path & "\"     'Uses the files folder location for directory
        vFileWKBK = Range("TBTPREFIX").Value & " - " & vCell.Value & ".xls*"
        vFilename = vFilepath & vFileWKBK

        Application.AskToUpdateLinks = False    'Supresses External links warning

        If Len(Dir(vFilename)) <> 0 Then
            Set destinationWorkbook = Workbooks.Open(vFilename)

            On Error Resume Next
            destinationWorkbook.Sheets("R&O").Delete
            destinationWorkbook.Sheets("Executive Summary").Delete
            destinationWorkbook.Sheets("Sheet1").Delete
            destinationWorkbook.Sheets("Sheet2").Delete
            On Error GoTo 0

            destinationWorkbook.Close True
        Else
            Debug.Print vFilename & " not found!"
        End If

    Next vCell

    With Application
        .DisplayAlerts = True
        .ScreenUpdating = True
    End With

    MsgBox "Sheets have been removed"

End Sub

 

[Johnny Thunder]

Thanks for the help @Norie

The code is working now but it does not show a list of the files it could not find. It does however skip the the MsgBox once it is done to let the user know the code has completed. Seems to loop very quickly and delete the sheets as intended but as you mentioned it would be nice to see what the code couldn't find in regards to missing files.

Is there a way to possibly show only the missing files in a messagebox?

Try this, it should take handle any files not being found and will also output a list of any files not found to the Immediate Window (CTRL+G), which might be useful.

Public Sub DeleteSheet()
Dim SourceSheet As Worksheet, SourceSheet2 As Worksheet
Dim folder As String, filename As String, vFilename As String, vFileWKBK, vFilepath As String
Dim vCell As Range
Dim DestBook As Workbook, destinationWorkbook As Workbook

    With Application
        .DisplayAlerts = False
        .ScreenUpdating = False
    End With

    For Each vCell In Range("LISTTERRITORYNAME").Cells
        vFilepath = ThisWorkbook.Path & "\"     'Uses the files folder location for directory
        vFileWKBK = Range("TBTPREFIX").Value & " - " & vCell.Value & ".xls*"
        vFilename = vFilepath & vFileWKBK

        Application.AskToUpdateLinks = False    'Supresses External links warning

        If Len(Dir(vFilename)) <> 0 Then
            Set destinationWorkbook = Workbooks.Open(vFilename)

            On Error Resume Next
            destinationWorkbook.Sheets("R&O").Delete
            destinationWorkbook.Sheets("Executive Summary").Delete
            destinationWorkbook.Sheets("Sheet1").Delete
            destinationWorkbook.Sheets("Sheet2").Delete
            On Error GoTo 0

            destinationWorkbook.Close True
        Else
            Debug.Print vFilename & " not found!"
        End If

    Next vCell

    With Application
        .DisplayAlerts = True
        .ScreenUpdating = True
    End With

    MsgBox "Sheets have been removed"

End Sub

 

[Norie]

Any files that are skipped because there can't be found will be written to the Immediate Window (CTRL+G).

 

[Johnny Thunder]

I found the window. Sorry I missed that comment the last time I read it.

I was curious, is there a way to create a log that a user who will not be viewing the VBA window can see all the files that weren't found? Similar to the immediate window but as a msgbox?