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

 

[argatica]

heres the code

Public Sub FillDaysOfTheWeek()
'extrae share

    
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, _
    DayWeek         As Variant
 
parentdirec = ActiveWorkbook.Path
DayWeek = Array("DOMINGO", "LUNES", "MARTES", "MIÉRCOLES", "JUEVES", "VIERNES", "SÁBADO")
operador = Array("ENTEL", "MOVISTAR", "CLARO")
Application.ScreenUpdating = False
Set parentwb = ActiveWorkbook
For i = 1 To 90
If Len(Dir(parentdirec & "\" & semana & "\" & file)) > 0 Then
Set semana = Sheets(1).Range("D1")
    file = parentwb.Sheets("info").Range("A" & i).Value
    Workbooks.Open parentdirec & "\" & semana & "\" & file
    Set filews = Workbooks(file).Sheets(1)
    For j = 2 To 22
        filews.Range(Cells(52, j), Cells(58, j)).Copy
        With Workbooks("share.xlsm").Sheets("tempshare")
            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("C" & destrow & ":C" & lastrow).Value = file
            .Range("D" & destrow & ":D" & lastrow).Value = DayWeek(Int((j - 2) / 3))
            .Range("E" & destrow & ":E" & lastrow).Value = operador((j - 2) Mod 3)
            .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

Else
Next i

Range("F2:F" & lastrow).Value = semana
Application.ScreenUpdating = True
End Sub

 

[wigi]

So you use the variable "file" (which is not a good variable name since it can lead to confusion, also for VBA) BEFORE you assign contents to that variable?

Your If Len(Dir(...

should come lower in the code, right before the opening of the workbook.

 

[argatica]

aw i see. now it works!

thanks a lot!!