problem with a macro

[argatica]

hi everyone, im not an ace of macros with error handlers, and i have this one that open files and copies the info on a book.

the problem is that sometimes as i don't have some files, an error dialog appears and it turns very annoying.

every i represents a file, so i'm trying to make the errorhandler to jump to the next i everytime it doesn't find the file.

Public Sub VTA()
'extrae VTA
    
Dim file            As String, _
    i               As Long, _
    j               As Long, _
    parentwb        As Workbook, _
    parentdirec     As String, _
    filews          As Worksheet, _
    destrow         As Long, _
    lastrow         As Long, _
    operador        As Variant
    
    
parentdirec = ActiveWorkbook.Path
operador = Array("ENTEL", "MOVISTAR", "CLARO")

Application.ScreenUpdating = False

Set parentwb = ActiveWorkbook
For i = 1 To 90
Set semana = Sheets(1).Range("D1")
    file = parentwb.Sheets("info").Range("A" & i).Value
On Error GoTo errhandler

    Workbooks.Open parentdirec & "\" & semana & "\" & file
    Set filews = Workbooks(file).Sheets(1)
    For j = 24 To 29 Step 2
        filews.Range(Cells(5, j), Cells(48, j + 1)).Copy
        With Workbooks("venta.xlsm").Sheets("tempvta")
            destrow = .Range("d" & .Rows.Count).End(xlUp).Row + 1
            lastrow = Application.Max(.Range("A" & .Rows.Count).End(xlUp).Row, destrow)
            .Range("B" & destrow).PasteSpecial xlPasteValues
            .Range("D" & destrow & ":D" & lastrow).Value = file
            .Range("E" & destrow & ":E" & lastrow).Value = operador(j / 2 - 12)
            .Range("A" & destrow & ":A" & lastrow).Copy Destination:=.Range("A" & lastrow).Offset(1, 0)
        End With
    Next j
    Application.DisplayAlerts = False
    Workbooks(file).Close
Next i
Range("F2:F" & lastrow).Value = semana
Application.ScreenUpdating = True

Exit Sub
errhandler:

    Next i

End Sub

 

[wigi]

If Len(Dir(parentdirec & "\" & semana & "\" & file)) > 0 Then

This is a test to see if the file exists. If it exists, Dir() gives you the filename, else it will be an empty string "". Hence, testing if the length is bigger than 0 or not will give you what you want.

 

[argatica]

thanks for the answer! sadly it won't work cause it checks whether the file is on the list, not if it really exists, but maybe you know a way to correct that. hope you can shed some more light on this.

 

[wigi]

The test is for the file you are trying to open with the Workbooks.Open statement. So the test is correct, no?

 

[argatica]

not really, cause that line of code checks if the cell has a value on it, not if the file exists.

 

[wigi]

My test:

If Len(Dir(parentdirec & "\" & semana & "\" & file)) > 0 Then

will determine if the file a concatenation of 3 things) exists on your hard drive.

It does not have to do anything with a cell being empty or not.

Please see whether the concatenated text:

parentdirec & "\" & semana & "\" & file

is EXACTLY the file you want and need to open using Workbooks.Open(). Use MsgBox or Debug.Print.

 

[argatica]

oh i thought that checked if the concatenation of of the 3 vars equaled empty or not. uhm well anyway, it's not working, i still got error message 1004: can't find the file.

 

[wigi]

Again, as said above, check the contents of these variables using MsgBox or Debug.Print.

 

[argatica]

i know what the values of the variable are, i can see them on the error msg cause they are the file that doesn't exists. what i want to is just skip those files.

 

[wigi]

Insert the IF statement... !

Please post your full code that you are now using.