Lecture d'un 2e classeur

Sub Importe()
    GetDataFromClosedWorkbook "C:\Excel\Liste des codes défaut.xls", "MyDataRange", Range("A1"), True
End Sub

Sub GetDataFromClosedWorkbook(SourceFile As String, SourceRange As String, _
                              TargetRange As Range, IncludeFieldNames As Boolean)
    ' requires a reference to the Microsoft ActiveX Data Objects library
    ' if SourceRange is a range reference:
    '   this will return data from the first worksheet in SourceFile
    ' if SourceRange is a defined name reference:
    '   this will return data from any worksheet in SourceFile
    ' SourceRange must include the range headers
    '
    Dim dbConnection As ADODB.Connection, rs As ADODB.Recordset
    Dim dbConnectionString As String
    Dim TargetCell As Range, i As Integer
    dbConnectionString = "DRIVER={Microsoft Excel Driver (*.xls)};" & _
                         "ReadOnly=1;DBQ=" & SourceFile
    Set dbConnection = New ADODB.Connection
    On Error GoTo InvalidInput
    dbConnection.Open dbConnectionString    ' open the database connection
    Set rs = dbConnection.Execute("[" & SourceRange & "]")
    Set TargetCell = TargetRange.Cells(1, 1)
    If IncludeFieldNames Then
        For i = 0 To rs.Fields.Count - 1
            TargetCell.Offset(0, i).Formula = rs.Fields(i).Name
        Next i
        Set TargetCell = TargetCell.Offset(1, 0)
    End If
    TargetCell.CopyFromRecordset rs
    rs.Close
    dbConnection.Close    ' close the database connection
    Set TargetCell = Nothing
    Set rs = Nothing
    Set dbConnection = Nothing
    On Error GoTo 0
    Exit Sub
InvalidInput:
    MsgBox "The source file or source range is invalid!", _
           vbExclamation, "Get data from closed workbook"
End Sub