Discussion :

[modus57]

Bonjour à tous.
J’ai un petit souci avec une macro de Theze que j’ai trouvé dans une autre discussion.
A l’initialisation de l’useform voici le message qui s’affiche : "La table externe n’est pas dans le format attendue."

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
Private Sub ConnectCLasseur(ConnectCL As Object, Fichier As String, Optional Rs)
    Set ConnectCL = CreateObject("ADODB.Connection")
    If Not IsMissing(Rs) Then
        Set Rs = CreateObject("ADODB.Recordset")
    End If
    ConnectCL.Open "Provider=Microsoft.Jet.OLEDB.4.0;" &  "Data Source=" & Fichier & ";" & _
              "Extended Properties=""Excel 8.0;HDR=NO;IMEX= 1;"""
End Sub

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
Private Sub UserForm_Initialize()
    Dim ConnectCL As Object
    Dim Rs As Object
    Dim Tbl()
    Dim Classeur As String
    Dim NomFeuille As String
    Dim Plage As String
    Dim I As Integer
    'chemin du classeur cible
    Classeur = "D:\Classeur2.xls"
    'cellules à lire
    Plage = "A1:A10"
    'nom de la feuille où se trouve la plage
    NomFeuille = "Feuil1"
    'connecxion
    ConnectCLasseur ConnectCL, Classeur, Rs
    With Rs
        .CursorType = 1
        .LockType = 3
        'récup des valeurs
        .Open "SELECT * FROM `" & NomFeuille & "$" &  Plage & "` ", ConnectCL
        'dimensionne le tableau au nombre de valeurs (ici 10)
        .MoveFirst
        ReDim Tbl(1 To .RecordCount)
        .MoveFirst
        'rempli le tableau
        Do While Not .EOF
            I = I + 1
            Tbl(I) = .Fields(0).Value
            .MoveNext
        Loop
    End With
    'ferme la connexion
    ConnectCL.Close
    'rempli le combo
    ComboBox1.List() = Tbl
    Set Rs = Nothing
    Set ConnectCL = Nothing
End Sub

J'utilise Excel 2010 c'est peut être la source du problème ?

[clementmarcotte]

Bonjour,

À partir d'Office 2007, on oublie le moteur Jet, et on utilise le moteur ACE. Le moteur ACE est disponible dans le Runtime d'Access que l'on peut trouver le site de Microsoft.

On peut trouver la chaîne de connexion là:

http://www.connnectionstrings.com

[modus57]

Bonjour à tous.

J’ai trouvé une partie de la réponse dans l’excellent tutoriel de SilkyRoad.

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
Sub extractionValeurCelluleClasseurFerme()
    Dim Source As ADODB.Connection
    Dim Rst As ADODB.Recordset
    Dim ADOCommand As ADODB.Command
    Dim Fichier As String, Cellule As String, Feuille As String
 
    'Pour une plage de cellules, utilisez:
    Cellule ="D:D" ‘n’accepte pas une plage nommée
 
    Feuille = "Feuil1$" 'n'oubliez pas d'ajouter $ au nom de la feuille.
    'Chemin complet du classeur fermé
    Fichier = "C:\Base.xlsx"
 
    Set Source = New ADODB.Connection
    Source.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
        "Data Source=" & Fichier & ";Extended Properties=""Excel 8.0;HDR=No;"";"
 
    Set ADOCommand = New ADODB.Command
    With ADOCommand
        .ActiveConnection = Source
        .CommandText = "SELECT * FROM [" & Feuille & Cellule & "]"
    End With
 
    Set Rst = New ADODB.Recordset
    Rst.Open ADOCommand, , adOpenKeyset, adLockOptimistic
 
    Set Rst = Source.Execute("[" & Feuille & Cellule & "]")
 
    Range("A2").CopyFromRecordset Rst
 
    Rst.Close
    Source.Close
    Set Source = Nothing
    Set Rst = Nothing
    Set ADOCommand = Nothing
End Sub

Mais je souhaiterai extraire le contenu de la colonne D trié et sans doublons dans un Combobox.

Est-ce possible merci d’avance pour votre aide.

[boisgontierjacques]

Bonjour,

Exemple:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
 
Sub Tri()
   'Outils/Références Microsoft ActiveX Data Object 2.8
  ActiveWorkbook.Names.Add Name:="MaBd", RefersTo:=Sheets(1).[A1].CurrentRegion
  Dim rs As ADODB.Recordset
  Set cnn = New ADODB.Connection
  cnn.Open "DRIVER={Microsoft Excel Driver (*.xls)};DBQ=" & ThisWorkbook.Path & "\" & ThisWorkbook.Name
     Sql = "select Distinct vendeur From MaBD Order By Vendeur"
     Set rs = cnn.Execute(Sql)
     a = rs.GetRows
     Sheets("feuil1").ComboBox1.List = Application.Transpose(a)
     rs.Close
     cnn.Close
     Set rs = Nothing
End Sub

Jacques Boisgontier

[modus57]

Bonjour boisgontierjacques.

Je n’ai pas testé, est-ce que ton code permet d‘extraire le contenu trié et sans doublons de la colonne D d’un fichier fermé et remplir un Combobox.

J’arrive à le faire avec un fichier ouvert.

Mais je souhaiterai le faire avec le fichier fermé, pour éviter de vérifier si celui-ci est ouvert et si non l’ouvrir.

[boisgontierjacques]

Bonjour,

Je n’ai pas testé, est-ce que ton code permet d‘extraire le contenu trié et sans doublons de la colonne D d’un fichier fermé et remplir un Combobox.

ADO fonctionne aussi bien avec des fichiers ouverts que des fichiers fermés.

Jacques Boisgontier

[Invité]

Bonjour,
d'une manière comme une autre ,nous nous efforçons à répondre au plus près de la demande!

mais si tu test tu pourras nous le dire!

Bonjour boisgontierjacques.

Je n’ai pas testé, est-ce que ton code permet d‘extraire le contenu trié et sans doublons de la colonne D d’un fichier fermé et remplir un Combobox.

J’arrive à le faire avec un fichier ouvert.

Mais je souhaiterai le faire avec le fichier fermé, pour éviter de vérifier si celui-ci est ouvert et si non l’ouvrir.

[modus57]

Bonjour rdurupt,

Ci-joint 2 fichiers pour m'aider à résoudre mon problème.

Je ne comprends pas bien cette ligne :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

Sql = "select Distinct vendeur From bd_boucle_controles_1 Order By Vendeur"

Les données pour renseigner la Combox1 dans la Feuil du fichier "liste_combobox_fichier_fermé" sont dans le fichier "bd_boucle_controles_1.xls" en D2.

Je ne suis pas un spécialiste et je n'arrive pas à modifier le code Sql.

@ + et merci.

[patricktoulon]

Bonjour a tous

je ne vois pas d'intérêt a utiliser ADO avec un fichier ouvert puisque il est accessible par pure vba

une sélection de la colonne et un dico point barre

je constate aussi que tu travaille sur 2 fichier avec extension différentes(xlm,xls) souhaitons que tu travaille toujours sur le même ordi avec au moins 2007
parce que si tu travaille sur un pc avec excel 2003 c'est choux blanc la rétrocompatibilité n'est pas dans les deux sens

[modus57]

Bonjour patricktoulon,

Je cite Jacques Boisgontier:

ADO fonctionne aussi bien avec des fichiers ouverts que des fichiers fermés.

Pour résumé, le but recherché : extraire les données triées et sans doublons d'un fichier fermé pour renseigner une ComboBox.

[patricktoulon]

Bonjour
re
oui j'ai bien vu la réponse de Jacques c'est vrai mais je n'en vois pas l'intérêt de coder un code pompeux avec ado si le fichier est ouvert entre nous

[joe.levrai]

Et si à la limite t'es pas sûr qu'il soit ouvert, tu testes et tu agis en conséquence (ouverture du fichier)

certes c'est un peu plus long (ça dépendra de la taille du fichier s'il faut l'ouvrir) .... mais ça t'éviteras de bloquer sur une banale requête SQL

et pense au fait qu'un jour tu pourrais transmettre le code à d'autres personnes ... tu préfères expliquer ADO et SQL qui te posent déjà problème, ou bien un teste d'existence de classeur (et son éventuelle ouverture) ?

[patricktoulon]

voici 2 fichiers
"classeur1" et "classeur fermé"
voici le code pour mettre la colonne "D" du classeur fermé dans la combobox du classeur1 sheets(1)
utilisation d'une variable tableau et d'un dico ainsi que 2 variables classeur
je pense avoir suffisamment commenter le code pour une compréhension rapide

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
Sub list()
    Sheets(1).ComboBox1.Clear
    Dim chemin As String, ouvert As Boolean, wbkF As Object, wbk1 As Object, derlig As Long, tablo As Variant, wbk As Object, listsansdoublons   ' toute les variables utilisées
    Set listsansdoublons = CreateObject("Scripting.Dictionary")    'on créé le dictionnaire
    Set wbk1 = ThisWorkbook
    chemin = ThisWorkbook.Path & "\classeur fermé.xls"
    'test si il est ouvert ou non
    For Each wbk In Application.Workbooks
        If wbk.Name = "classeur fermé" Then ouvert = True
    Next
    If ouvert = False Then    ' si le fichier n'est pas ouvert
        Application.ScreenUpdating = False: Application.DisplayAlerts = False    'on supprime tout les avertissements
        Set wbkF = Workbooks.Open(chemin)    'on ouvre le classeur en silentieux(updating et alerts a false)
    End If
    With wbkF.Sheets(1)
        derlig = .Range("D" & Rows.Count).End(xlUp).Row    'determine la derniere ligne de la colonne "D"
        tablo = .Range("D2:D" & derlig)    ' on integre les valeur de la plage dans tablo
    End With
    'on supprime les doublons avec un dictionnaire
    For i = LBound(tablo) To UBound(tablo) - 1
        If Not listsansdoublons.exists(tablo(i, 1)) Then listsansdoublons.Add tablo(i, 1), ""
    Next
    wbkF.Close 'on referme le classeur fermé on en a plus besoins
    ' on transpose la liste sans doublons dans la combo du sheets (1) du classeur de reception
    wbk1.Sheets(1).ComboBox1.list = listsansdoublons.keys
End Sub

[modus57]

Bonsoir

Finalement c'est plus simple de tester si le fichier est ouvert ou non.

Par contre un petit de détail, la liste n'est pas triée et je ne s'est pas comment le faire dans un tableau.

[patricktoulon]

Comment ca la liste n'est pas triée ?
Dans l'exemple que je t'ai donné elle est triée avec le dico !!

[joe.levrai]

Bonjour,

la liste est bien sans doublon dans ton code, mais où fais-tu le tri alphabétique ? Je ne le vois pas

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
'Tri
For i = LBound(tablo) To UBound(tablo) - 1
    For j = i + 1 To UBound(tablo)
        If tablo(i) > tablo(j) Then
            Temp = tablo(j)
            tablo(j) = tablo(i)
            tablo(i) = Temp
        End If
    Next j
Next i

[modus57]

Bonjour et bon dimanche,

Je me suis contenté d'utiliser vos 2 fichiers et à prime abord la liste dans la ComboBox1 de la Feuil1 du Classeur1 ne se trie pas ?

Re

En ajoutant le code suivant trouver sur ce forum :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
Dim j As Byte
Dim temp As String
 
With Sheets(1).ComboBox1
        For i = 0 To .ListCount - 1
            For j = 0 To .ListCount - 1
                If .list(i) < .list(j) Then
                    temp = .list(i)
                    .list(i) = .list(j)
                    .list(j) = temp
                End If
            Next j
         Next i
End With

Ça fonctionne

[joe.levrai]

parfois, vaut mieux ne rien poster que de poster dans le vide
on a du se croiser
Message #16 de ce fil

[patricktoulon]

re
tu compare un item avec lui même sur la première boucle

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
for i= 0 to
for j = 0 to

j devrai être i+1

[Invité]

bonsoir,
quand on parle de fichier bidon, on parle des données pas de la structure!

hors ton fichier est totalement bidon et inexploitable pour restituer un rendu conforme à tes attente
Onglet Data:

A	B	C	Vendeur
			Gilbert
			Raymond
			Claude
			Yvon
			Tina
			Ugo
			Vladimir
			Kevin
			Gilbert

Code boisgontierjacques modifié :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
Sub Trié()
   'Outils/Références Microsoft ActiveX Data Object 2.8
  ActiveWorkbook.Names.Add Name:="MaBd", RefersTo:=Sheets(1).[A1].CurrentRegion
  Dim rs As ADODB.Recordset
  Set cnn = New ADODB.Connection
  cnn.Open "DRIVER={Microsoft Excel Driver (*.xls)};DBQ=" & ThisWorkbook.Path & "\bd_boucle_controles_1.xls"
     Sql = "select Distinct vendeur From [data$] Order By Vendeur"
     Set rs = New ADODB.Recordset
     rs.Open Sql, cnn
     a = rs.GetRows
     Sheets("feuil1").ComboBox1.List = Application.Transpose(a)
     rs.Close
     cnn.Close
     Set rs = Nothing
End Sub