Dim cnn
Dim cat
Dim xlSheet As Variant
Dim répertoire
Dim fichier As String
Dim rs As ADODB.Recordset
Dim L As Long
Dim NomF As String
Private Sub UserForm_Initialize()
'XXXXXXX IMPORTANT POUR QUE LE PROGRAMME FONCTIONNE XXXXXXXXXXX
'Outils - Référence - Activé
'Microsoft ActiveX Data Object 2.8 Library
'Microsoft ADO Ext 6.0 for DDL and Security
'XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
'Dim cnn
'Dim cat
'Dim xlSheet As Variant
'Dim répertoire
'Dim fichier As String
'Dim rs As ADODB.Recordset
Set cnn = New ADODB.Connection
Set cat = CreateObject("ADOX.Catalog")
répertoire = "C:\0. PROGRAMME JRE\BDD DIVERS 2012\"
fichier = "BDD MSIT 2012.xlsm"
cnn.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & répertoire & fichier & ";Extended Properties='Excel 12.0;HDR=Yes'"
Set cat.ActiveConnection = cnn
For Each xlSheet In cat.Tables
If Right(xlSheet.Name, 1) = "$" Then ComboBox1.AddItem Replace(Replace(xlSheet.Name, "$", ""), "#", ".")
Next
For i = 1 To 5
If i = 1 Then largeur = 120 Else largeur = 200
Me("ListView" & i).ColumnHeaders.Add , , "niveau" & i, largeur
Me("ListView" & i).Gridlines = True
Me("ListView" & i).View = lvwReport
Next
Me.Label1.Visible = False
Set cnn = Nothing
Set cat = Nothing
SendKeys "(F4)"
End Sub
Private Sub ComboBox1_Change()
Dim rs As ADODB.Connection
Set cnn = New ADODB.Connection
Set cat = CreateObject("ADOX.Catalog")
cnn.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & répertoire & fichier & ";Extended Properties='Excel 12.0;HDR=Yes'"
'Set cat.ActiveConnection = cnn
If Me.ComboBox1 <> "" Then
NomF = Me.ComboBox1.Value
For i = 1 To 5
Me("ListView" & i).ListItems.Clear
Next
Set rs = cnn.Execute("SELECT BD FROM ComboBox1")
'Worksheets(NomF).Range("D6:Q77").Name = "bd"
For Each C In Application.Index([bd], , 1)
If C <> "" Then
Me.ListView1.ListItems.Add , , C & " - " & C.Offset(, 6)
End If
Next
Me.ListView1.ListItems(1).Selected = False
Set Me.ListView1.SelectedItem = Nothing
For i = 1 To Me.ListView1.ListItems.Count
If i Mod 2 = 0 Then
Me.ListView1.ListItems(i).ForeColor = &H8000& ' &HFF0000 'bleu &H8000& 'vert
Me.ListView1.ListItems(i).Bold = True
End If
Next
End If
End Sub
