Option Explicit
Const NbLig& = 500 'à adapter
Const NbCol& = 20 'à adapter
Sub Princ()
Recup 'C:\\Documents and Settings\\UserName\\My Documents\\', 'TestVB.xls', Array('Feuil1', 'Feuil2'Â'Â')
End Sub
Sub Recup(ByVal Chemin$, ByVal NomFich$, T)
Dim I&, J&, K&, Temp
Application.ScreenUpdating = False
If ThisWorkbook.Worksheets.Count <> UBound(T) + 1 Then Exit Sub
For I = LBound(T) To UBound(T)
ReDim Temp(1 To NbLig, 1 To NbCol)
For J = 1 To NbLig
For K = 1 To NbCol
Temp(J, K) = GetValue(Chemin, NomFich, T(I), Cells(J, K).Address)
Next K
Next J
ThisWorkbook.Worksheets(I + 1).[A1].Resize(UBound(Temp), UBound(Temp, 2)) = Temp
Next I
End Sub
Private Function GetValue(Path, File, Sheet, Ref) 'John Walkenbach
Dim Arg As String
If Right(Path, 1) <> '\\' Then Path = Path & '\\'
If Dir(Path & File) = '' Then
GetValue = 'File Not Found'
Exit Function
End If
Arg = ''' & Path & '[' & File & ']' & Sheet & ''!' & Range(Ref) _
.Range('A1'Â'Â').Address(, , xlR1C1)
GetValue = ExecuteExcel4Macro(Arg)
GetValue = IIf(GetValue = 0, '', GetValue) 'modif perso
End Function