Sub Lire_Classeur_Ferme()
Dim source As ADODB.Connection
Dim Rst As ADODB.Recordset
Dim ADOCommand As ADODB.Command
Dim Chemin As String, Cellule As String, Feuille As String
Dim Tableau As Variant
Feuille = "Feuil1$" 'n'oubliez pas d'ajouter $ au nom de la feuille.
Chemin = "C:\Tools\Classeur_ferme.xlsm"
Set source = New ADODB.Connection
source.Open "Provider=Microsoft.Ace.OLEDB.12.0;" & "Data Source=" & Chemin & ";Extended Properties=""Excel 8.0;IMEX=1;HDR=NO;"";"
Set ADOCommand = New ADODB.Command
With ADOCommand
.ActiveConnection = source
.CommandText = "SELECT * FROM [" & Feuille & "]"
End With
Set Rst = New ADODB.Recordset
Rst.Open ADOCommand, , adOpenKeyset, adLockOptimistic
Dim nombre As Integer
nombre = 1000
Cellule = "A4:J" & nombre
Set Rst = source.Execute("[" & Feuille & Cellule & "]")
Tableau = Rst.GetRows
Rst.Close
source.Close
Set source = Nothing
Set Rst = Nothing
Set ADOCommand = Nothing
End Sub