Bonjour Brutus01,
En ce cas, je te propose le code suivant à activer dans EXCEL par un bouton :
Option Explicit
Const cMSACCESSDataBaseName = "\\SSIGWP0101\Peinture\EPP\gestion stock peinture\BD Gestion du stock peinture 1.1.mdb"
Const cFormName = "70 bis Choix du produit à préparer"
Sub Bouton1_Cliquer()
Dim oDB As Access.Application
Dim oForm As Access.Form
Dim oSelectedRow As Object
Dim oField As Object
Dim oSheet As Worksheet
Dim lRow As Long
If AccessBaseIsActive Then
Set oDB = GetObject(, "Access.Application")
If oDB.Screen.ActiveForm.Name = cFormName Then
Set oForm = oDB.Screen.ActiveForm
Set oSelectedRow = oForm.Recordset
Set oSheet = ThisWorkbook.ActiveSheet
lRow = oSheet.Cells(oSheet.Rows.Count, 1).End(xlUp).Row
'On ajoute les données de la ligne sélectionnée dans l'EXCEL
For Each oField In oSelectedRow.Fields
oSheet.Cells(lRow + 1, oField.OrdinalPosition + 1) = oField.Value
Next
ThisWorkbook.Save
End If
Else
MsgBox "L'application ACCESS n'est pas active!", vbExclamation
End If
End Sub
Function AccessBaseIsActive() As Boolean
Dim oApp As Object
Dim oDB As Object
On Error Resume Next
Set oApp = GetObject(, "Access.Application")
If Not oApp Is Nothing Then
If InStr(1, oApp.ADOConnectString, cMSACCESSDataBaseName) > 0 Then
'Set oDB = GetObject(cMSACCESSDataBaseName)
AccessBaseIsActive = True
Else
AccessBaseIsActive = False
End If
Else
AccessBaseIsActive = False
End If
Set oApp = Nothing
On Error GoTo 0
End Function