Bonjour,
Voici un exemple,
Sub test()
Dim Fich As String, rep As String, FeuilSource As String, Feuil_cellule_destination As String
rep = "C:\Users\xxxxx\Documents\" 'à adapter
Fich = "List_test.xlsm" 'à adapter
FeuilSource = "Sheet1" 'à adapter
Feuil_cellule_destination = "Feuil2!A1" 'à adapter
LireCellule rep, Fich, FeuilSource, Feuil_cellule_destination 'à adapter
End Sub
'nécessite d'activer la référence: Microsoft ActiveX Data Objects xx Library
Function LireCellule(repertoire As String, fichier As String, feuille As String, dest As String)
Set cnn = New ADODB.Connection
'--- Connexion ---
With cnn
.Provider = "Microsoft.Jet.OLEDB.12.0"
.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" _
& repertoire & "\" & fichier & ";Extended Properties=""Excel 12.0;HDR=NO;"""
.Open
End With
'--- récupérer les données --
Set rs = cnn.Execute("SELECT * FROM [" & feuille & "$" & cellule & "]")
Range(dest).CopyFromRecordset rs
rs.Close
cnn.Close
Set rs = Nothing
Set cnn = Nothing
End Function