1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38
| Option Base 1
Sub import()
Application.ScreenUpdating = False
Application.DisplayAlerts = False
Dim plage As Range, chemin As String, wbkP As Workbook, nomfich, indice, mestablos, i, a, firstadress As String
indice = Array("foot", "basket", "tennis")
ReDim mestablos(UBound(indice))
chemin = "g:\Statistiques\sports\"
nomfich = Dir(chemin & "*.xls")
Do While nomfich <> ""
If Left(nomfich, 5) = "fiche" Then
Set wbkP = Workbooks.Open(chemin & nomfich)
Set plage = Worksheets("sports").Range("B35:e55")
For i = 1 To UBound(indice)
Set c = plage.Find(indice(i), LookIn:=xlValues)
If Not c Is Nothing Then
firstadress = c.Address
Do
Set c = plage.FindNext(c)
Loop While Not c Is Nothing And firstadress <> c.Address
mestablos(i) = Range(Cells(c.Row, 2), Cells(c.Row, 11))
End If
Next
wbkP.Close
For i = 1 To UBound(mestablos)
Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Resize(1, 7) = mestablos(i)
Next
nomfich = Dir()
End If
Loop
End Sub |
Partager