How do i skip a file that does not exist in my Excel macro?

[tsoccer63]

Hey!

Here is my code, sorry it is messy but I am not a programmer by trade so my skills are limited.

Basically, I do not know how to handle errors in VBA. In this code I grab a file path from a cell, do some tweeking of the path, then open the file and paste the info into a new sheet. My problem is....once i get to a file that does not exist the macro ends. How can i skip over that file/error.

Application.ScreenUpdating = False
Sheets("files").Select
Range("A1000").Select
Selection.End(xlUp).Select
index1 = ActiveCell.Row - 100
For i = 2 To index1
fpath = Sheets("files").Range("I1").Value
fname = Sheets("files").Range("A" & (i + 1)).Value
Dim fpath3 As String
If Mid(fname, 1, 1) = "H" Then
fpath2 = "HD5"
fpath3 = Mid(fname, 5, 1)
ElseIf Mid(fname, 1, 1) = "N" Then
fpath2 = "NG"
fpath3 = Mid(fname, 4, 1)
Else: fpath2 = "SALPG"
fpath3 = Mid(fname, 7, 1)
End If
fullf = fpath + fpath2 + "\" + fpath3 + "kw\" + fname + "\" + fname + ".xls"
Dim wsheet As Worksheet
Set wsheet = ThisWorkbook.Worksheets.Add
On Error GoTo ErrorTrap1
wsheet.Name = fname
' copy paste time!
Workbooks.Open Filename:=fullf
On Error GoTo ErrorTrap1
Cells.Select
Selection.Copy
Windows("overview").Activate
Sheets(fname).Select
Cells.Select
ActiveSheet.Paste
Range("A1").Select
Selection.Copy
ActiveWindow.SelectedSheets.Visible = False
Windows(fname).Activate
Range("A1").Select
ActiveWindow.Close
Sheets("Overview").Select
Range("B25").Select
'2nd part
Columns("F:F").Select
Selection.Copy
Range("EZ1").Select
Selection.End(xlToLeft).Select
Cells(1, (ActiveCell.Column + 1)).Select
' MsgBox (ActiveCell.Column)
ActiveSheet.Paste
Cells(1, (ActiveCell.Column)).Select
ActiveCell.Value = fname
' Columns("H:H").Select
Application.CutCopyMode = False
enderror:
Next
Exit Sub

ErrorTrap1:
'MsgBox ("File already added or file does not exist")
Application.DisplayAlerts = False
wsheet.Delete
Application.DisplayAlerts = True
GoTo enderror
Exit Sub
End Sub

 

[PHV]

Have you tried to replace this:
GoTo enderror
with this ?
Resume enderror

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[firefytr]

Try using functions like these ...

Function IsWbOpen(wbName As String) As Boolean
    On Error Resume Next
    IsWbOpen = Len(Workbooks(wbName).Name)
End Function

Function SheetExists(wsName As String, wkb As Workbook) As Boolean
    On Error Resume Next
    SheetExists = Len(wkb.Sheets(wsName).Name)
End Function

Function DirExists(strPath As String) As Boolean
    DirExists = Len(Dir(strPath, vbDirectory))
End Function

Function FileExists(strFile As String) As Boolean
    FileExists = Len(Dir(strFile))
End Function

You can incorporate them right into your code. And I would recommend setting the workbook to a variable. Not sure if I understood that right, but maybe..

    If FileExists("C:\YourPathHere\Filename.xls") Then
        If IsWbOpen(fullf) Then
            Set wb = Workbooks(fullf)
        Else
            Set wb = Workbooks.Open(yourPath & fullf)
        End If
    Else
        Set wb = Workbooks.Add(xlWBATWorksheet) 'only one sheet
    End If

HTH

-----------
Regards,
Zack Barresse