Private Sub UserForm_Initialize()
Dim rep As Variant
Dim rep1 As String
Dim c As Range
Dim Rs As ADODB.Recordset
Dim Cn As String
Dim Cible As String
Dim Fichier As String
Label29.Caption = Format(Date, 'DD/MM/YYYY')
Me.ComboNumCip.Enabled = Me.OptionButton1
Me.ComboNatCIP.Enabled = Me.OptionButton1
Me.TxtMontCIP.Enabled = Me.OptionButton1
rep = Split(ThisWorkbook.Path, '\\')
rep1 = rep(UBound(rep))
With Me.ComboCDF
.ColumnCount = 4
.ColumnWidths = '150;0;0;0'
For Each c In Sheets('CDF').Range('F1:F' & Sheets('CDF').Range('F65536').End(xlUp).Row)
If c = rep1 Then
.AddItem c.Offset(0, -3)
.List(.ListCount - 1, 1) = c.Offset(0, -1)
.List(.ListCount - 1, 2) = c.Offset(0, -2)
.List(.ListCount - 1, 3) = c.Offset(0, -3)
End If
Next c
End With
With Sheets('NatDep')
Me.ComboNatDep.List = .Range('A2:A' & .Range('a65536').End(xlUp).Row).Value
End With
Fichier = ThisWorkbook.Path & '\\FichierB.xls' 'adapter le chemin
Cn = 'DRIVER={Microsoft Excel Driver (*.xls)};' & _
'ReadOnly=1;DBQ=' & Fichier
Cible = 'SELECT * FROM [Feuil1$];'
Set Rs = New ADODB.Recordset
Rs.Open Cible, Cn, adOpenForwardOnly, adLockReadOnly, adCmdText
'les données à récuperer sont dans la colonne A
If Not Rs.EOF Then
' ComboBox1.AddItem Rs.Fields(2).Name 'entete
Do While Not Rs.EOF
ComboNumCip.AddItem Rs.Fields(1).Value
Rs.MoveNext
Loop
End If
Rs.Close
Set Rs = Nothing
End Sub