I'm trying to extract from a number of files in a folder.
The files are named MS060XXX.0.xls where XXX is a number ranging from
001 to 850.
My error handling works if there is only one file missing in the range of files I
want to extract from.
If two ore more files are missing the macro stopps with message "file not found"
Could anybody please give me a hint how to solve this problem.
Sub Macro1()
'
' Macro1 Macro
'
Dim i As Integer
For i = Range("I2").Value To Range("I3").Value Step 1
ChDir "E:\beredskap\bensin"
Application.ScreenUpdating = False
On Error GoTo Err
If i < 100 Then
Workbooks.Open Filename:="MS0600" & i & ".0.xls"
Else
Workbooks.Open Filename:="MS060" & i & ".0.xls"
End If
If FileLen(ActiveWorkbook.FullName) > 300000 Then
Sheets("Beställning").Activate
Range("A2:C2").Copy
Windows("select_files.xls").Activate
Range("C" & Rows.Count).End(xlUp).Offset(1, 0).PasteSpecial Paste:=xlValues
End If
If i < 100 Then
Windows("MS0600" & i & ".0.xls").Activate
ActiveWorkbook.Close
Else
Windows("MS060" & i & ".0.xls").Activate
ActiveWorkbook.Close
End If
Err:
Next i
Application.ScreenUpdating = True
End Sub
Bookmarks