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

Johnny Thunder

Well-known Member
Joined
Apr 9, 2010
Messages
675
Office Version
  1. 2016
Platform
  1. MacOS
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!

Code:
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
 

Some videos you may like

Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
53,026
Office Version
  1. 365
Platform
  1. Windows
Try
Code:
 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

Well-known Member
Joined
Apr 9, 2010
Messages
675
Office Version
  1. 2016
Platform
  1. MacOS
@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:

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

Well-known Member
Joined
Apr 28, 2004
Messages
76,218
Office Version
  1. 365
Platform
  1. Windows
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.

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

        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

Well-known Member
Joined
Apr 9, 2010
Messages
675
Office Version
  1. 2016
Platform
  1. MacOS

ADVERTISEMENT

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.

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

        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
 
Last edited:

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,218
Office Version
  1. 365
Platform
  1. Windows
Any files that are skipped because there can't be found will be written to the Immediate Window (CTRL+G).
 

Johnny Thunder

Well-known Member
Joined
Apr 9, 2010
Messages
675
Office Version
  1. 2016
Platform
  1. MacOS
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?
 

Watch MrExcel Video

Forum statistics

Threads
1,122,747
Messages
5,597,905
Members
414,189
Latest member
Duque_

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top