Non... Il ne veut pas marcher avec les XLSX... Je me penche sur le sujet.
Daniel nous donne la réponse: Merci Daniel:
Function RecupPlage( _
Chemin As String, _
Fichier As String, _
Feuille As String, _
Cellule As String) As Variant
If Split(Fichier, ".")(1) = "xls" Then
RecupPlage = RecupPlage2003(Chemin, Fichier, Feuille, Cellule)
Else
RecupPlage = RecupPlage2007(Chemin, Fichier, Feuille, Cellule)
End If
End Function
Function RecupPlage2007( _
Chemin As String, _
Fichier As String, _
Feuille As String, _
Cellule As String) As Variant
'ADOExcel2007LirePlage
Dim Rst As New ADODB.Recordset
Dim Cnn As New ADODB.Connection
Dim Requete As String
Dim NbRecord As Long
Cellule = Range(Cellule).Resize(2).Address(0, 0)
With Cnn
.Provider = "Microsoft.Jet.OLEDB.4.0"
.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" _
& Chemin & "\" & Fichier & ";Extended Properties=""Excel 12.0;HDR=NO;"""
.Open
End With
texte_SQL = "SELECT * FROM [" & Feuille & "$" & Cellule & "]"
Rst.Open texte_SQL, Cnn, adOpenStatic
RecupPlage2007 = Rst(0)
Rst.Close
Cnn.Close
Set Rst = Nothing: Set Cnn = Nothing
End Function
Function RecupPlage2003( _
Chemin As String, _
Fichier As String, _
Feuille As String, _
Cellule As String) As Variant
Application.Volatile
Dim Source As Object, Rst As Object, ADOCommand As Object
Feuille = Feuille & "$"
Cellule = Range(Cellule).Resize(2).Address(0, 0)
Set Source = CreateObject("ADODB.Connection")
Source.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & Chemin & "\" & Fichier & _
";Extended Properties=""Excel 8.0;HDR=No;"";"
Set ADOCommand = CreateObject("ADODB.Command")
With ADOCommand
.ActiveConnection = Source
.CommandText = "SELECT * FROM [" & Feuille & Cellule & "]"
End With
Set Rst = CreateObject("ADODB.Recordset")
'1 = adOpenKeyset, 3 = adLockOptimistic
Rst.Open ADOCommand, , 1, 3
Set Rst = Source.Execute("[" & Feuille & Cellule & "]")
RecupPlage2003 = Rst(0).Value
Rst.Close
Source.Close
Set Source = Nothing
Set Rst = Nothing
Set ADOCommand = Nothing
End Function