Des requetes sql en code vba de l'excel

Version imprimable

24/07/2016, 12h48
nisrine hammout

Des requetes sql en code vba de l'excel

Bonjour,

J'ai une question à poser à propos de vba de l'excel

Quelle est la formule correspondante en vba qu'on peut utiliser si on veut effectuer cette requette

"select column A from mon tableau"?

Et mon tableau est dans une feuille en excel,

Sans faire appelle à une base de données parce que je vais tout travailler sur mon tableau qui est dans la feuille
et Merci :roll:
24/07/2016, 20h05
Zekraoui_Jakani

Tu peux essayer

Code:

Sheets("NomDetaFeuille").Range("A:A").Select
24/07/2016, 20h20
nisrine hammout

apres select j'ecris ma requete?
24/07/2016, 20h41
78chris

Bonjour

Une base de données et un tableau de tableur sont 2 choses différentes.

Même si dans quelques cas on peut utiliser des requêtes sur un tableau Excel, ce n'est pas le cas le plus "normal".

Il vaudrait mieux expliquer ce que tu veux faire concrètement de cette colonne A...
24/07/2016, 21h04
nisrine hammout

voila ce qui est demandé plus precisement

Comparer la désignation de l’item avec la Base critère

- Comparer les Intervalles de tolérance

- Comparer la valeur relevée avec les bornes de la base critère

- Renseigner la sanction

- Renseigner le commentaire suivant le sens de la Hors Tolérances

o Commentaire de gauche si la valeur est inférieure à la valeur nominale

o Commentaire de droite si la valeur est supérieure à la valeur nominale

o Le commentaire s’il n’y en a qu’un seul.

au faite j'ai deux fichier un pour le pointage = base critère et l’autre pour le remplissage
25/07/2016, 09h26
78chris

Bonjour

Sur le principe, même en requête sur une base de données, il faudrait un ID commun pour établir une relation et comparer les autres champs...

Ici tu dis que tu as deux fichiers, chacun avec un tableau dont on ne connait pas la structure. Commentaire de gauche et Commentaire de droite paraissent nébuleux en l'état...

Tu peux consulter cela http://silkyroad.developpez.com/VBA/ClasseursFermes/ mais a priori il faudrait plutôt ouvrir le second fichier et comparer par une boucle...

Bonjour,

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
Sub testHDR_Yes()
Dim Cn As Object, Rs As Object, Sql As String
Set Cn = CreateObject("Adodb.connection")
With Cn
        .Provider = "Microsoft.ACE.OLEDB.12.0"
        .ConnectionString = "Data Source=" & ThisWorkbook.FullName & ";Extended Properties=""Excel 12.0 Xml;HDR=Yes;IMEX=1;"""
        Sql = "select [EnteteColonne1]  from [Nomonglet$]"
        Set Rs = CreateObject("Adodb.RecordSet")
       .Open
        Rs.Open Sql, Cn
        Rs.Close
        Set Rs = Nothing
        .Close
    End With
    Set Cn = Nothing
End Sub

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
Sub testHDR_No()
Dim Cn As Object, Rs As Object, Sql As String
Set Cn = CreateObject("Adodb.connection")
With Cn
        .Provider = "Microsoft.ACE.OLEDB.12.0"
        .ConnectionString = "Data Source=" & ThisWorkbook.FullName & ";Extended Properties=""Excel 12.0 Xml;HDR=No;IMEX=1;"""
        Sql = "select [F1]  from [Nomonglet$]" '[F1] collonne A
        Set Rs = CreateObject("Adodb.RecordSet")
       .Open
        Rs.Open Sql, Cn
        Rs.Close
        Set Rs = Nothing
        .Close
    End With
    Set Cn = Nothing
End Sub

25/07/2016, 20h11
nisrine hammout

J'ai réussi à faire la connexion entre mon tableau et sql
J'ai excute la requete select * from mafeuille
Mais comment faire pour faire select colonneA from mafeuille where macolonneB=macolonneC
Et merci pour votre aide
25/07/2016, 20h21
Invité

Bonsoir,
je t'es donnée la réponse au poste #7!
25/07/2016, 20h35
nisrine hammout

J ai essayé la methode de F1 ca marchée mais par exemple poue la colonne B qu'est ce que je dois mettre
25/07/2016, 20h40
Invité

F1=A;F2=B; etc...

Oui c'est réussi merci pour votre aide

j'essaie à manipuler deux fichier depuis la même sub
Mais j'ai un message d'erreur https://scontent-mad1-1.xx.fbcdn.net...19&oe=57980F33

voila mon code

Code:

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
40
41
42
43
Sub TestConnection_V1()
    Dim Cn As ADODB.Connection
    Dim Cn2 As ADODB.Connection
    Dim Fi1, Fi2 As String
    Dim client, safran As String, SQL As String
    Dim Rst As ADODB.Recordset
 
    Fi2 = "C:\Users\admin\Desktop\nouveau travail\safran.xlsm"
    Fi1 = "C:\Users\admin\Desktop\nouveau travail\Copie de FormIDD_DERX189100_RETOUR_SES.xlsm"
    client = "Description_et_Decision_Techn"
    safran = "Feuil1"
    Set Cn = New ADODB.Connection
 
    '--- Connexion ---
 
    With Cn
         Cn.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & Fi1 & _
                  ";Extended Properties=""Excel 12.0;HDR=No;"";"
    End With
    With Cn2
            Cn2.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & Fi2 & _
                  ";Extended Properties=""Excel 12.0;HDR=No;"";"
    End With
 
    'Définit la requête.
 SQL = "Select [" & client & "$].[F5] as a , [" & safran & "$].[F2] as b from [" & client & "$],[" & safran & "$] where a=b"
 
 
    Set Rst = New ADODB.Recordset
    Set Rst = Cn.Execute(SQL)
 
    'Ecrit le résultat de la requête dans la cellule A2
    Range("A135").CopyFromRecordset Rst
 
    '--- Fermeture connexion ---
    Cn.Close
    Set Cn = Nothing
 
  Cn2.Close
    Set Cn2 = Nothing
 
 
End Sub

Desole je sais pas comment l'afficher comme un code

25/07/2016, 22h29
Invité
Code:

1 2 Dim Cn As New ADODB.Connection Dim Cn2 As New ADODB.Connection
Pour formater ton code, tu le grise avec la souris et tu click sur l'icône #

Code:

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
40
41
42
43
44
Sub TestConnection_V1()
    Dim Cn As ADODB.Connection
    Dim Cn2 As ADODB.Connection
    Dim Fi1, Fi2 As String
    Dim client, safran As String, SQL As String
    Dim Rst As ADODB.Recordset
 
    Fi2 = "C:\Users\admin\Desktop\nouveau travail\safran.xlsm"
    Fi1 = "C:\Users\admin\Desktop\nouveau travail\Copie de FormIDD_DERX189100_RETOUR_SES.xlsm"
    client = "Description_et_Decision_Techn"
    safran = "Feuil1"
    Set Cn = New ADODB.Connection
    Set Cn2 = New ADODB.Connection
    '--- Connexion ---
 
    With Cn
         Cn.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & Fi1 & Fi2 & _
                  ";Extended Properties=""Excel 12.0;HDR=No;"";"
 
    End With
     With Cn2
         Cn2.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & Fi2 & _
                  ";Extended Properties=""Excel 12.0;HDR=No;"";"
 
    End With
    'Définit la requête.
 SQL = "Select [" & client & "$].[F5] as a from [" & client & "$] where [" & safran & "$].[F2]=[" & client & "$].[F5]"
 
    ', [" & safran & "$].[F2] as b from [" & client & "$],[" & safran & "$] where a=b"
    Set Rst = New ADODB.Recordset
    Set Rst = Cn.Execute(SQL)
 
    'Ecrit le résultat de la requête dans la cellule A2
    Range("A135").CopyFromRecordset Rst
 
    '--- Fermeture connexion ---
    Cn.Close
    Set Cn = Nothing
  Cn2.Close
    Set Cn2 = Nothing
 
 
 
End Sub

j'ai essaye mais il me donne
https://scontent.xx.fbcdn.net/v/t34....86&oe=5799038A
l'erreur est ici https://scontent-mad1-1.xx.fbcdn.net...18&oe=57992399
j'ai tout essayé :( :(

26/07/2016, 07h14
Invité

Bonjour,
Tu n'as pas l'impression de concaténer les deux fichier?

Code:

Source=" & Fi1 & Fi2

Là je suis sur mon téléphone portable mais dès que je retrouve mon ordinateur on va revoir tous!

Je suis un des meilleurs didacticiel que tu pourras trouver sur le sujet et en plus interactif!

Maintenant que j'ai évaluer ton niveau on va pouvoir passer au choses séreuses.
26/07/2016, 09h36
nisrine hammout

Je suis debutante :(

Citation:

Envoyé par nisrine hammout

Je suis debutante :(

j'ai bien remarqué!

Quand tu utilise les variable de type DODB.Connection tu es obligé de bricoler les références ce qui te brides à utiliser la même version de Windows et/ou Excel !

Je préconise d’utiliser CreateObject qui t’affranchis de cette corvée et ton application reste compatible toutes versions !

Diviser pour régner.
Il nous est souvent utile d’exécuter les mêmes actions. Il est de ce fait important de factoriser nos actions sous forme procédure et de fonctions.

Avantage c’est également de centraliser nos actions, ainsi il devient possible d’enrichir un fichier de log retracent l’historique de nos action.

Nous allons implémenter les différentes actions utiles pour notre traitement.

On verra par la suite que tu n’a pas besoin de faire plusieurs connections qu’il est possible d’utiliser une connexion à un fichier et faire une requête sur un autre fichier Excel ! Il faut noter qu’une requête permet un traitement de masse sur une base de données (Excel en l’occurrence) mais le temps de connexion reste relativement lent et on s’affranchira dans la mesure du possible de multiplier les connexions ! On verra qu’il est possible de faire des liaisons externe vers d’autres fichiers Excel dans une requête. On pourra même faire des jointures entre onglets de fichiers différents.

Voila à quoi pourrait ressembler ton code !
Code:

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 40 41 42 43 44 45 46 Sub Test() Dim Cn As Object, Rst As Object, Sql As String, Fi1 As String, Fi2 As String,client as String,safran as String Fi2 = "C:\Users\admin\Desktop\nouveau travail\safran.xlsm" Fi1 = "C:\Users\admin\Desktop\nouveau travail\Copie de FormIDD_DERX189100_RETOUR_SES.xlsm" client = "Description_et_Decision_Techn" safran = "Feuil1" Set Cn = OpenConnetion(Fi1, False) If TypeName(Cn) = "Nothing" Then Exit Sub Sql = "Select Frm1.[F5] from [" & client & "$] as Frm1 inner join (Select * from [" & safran & "$] in '" & Fi2 & "' 'excel 8.0;HDR=no;IMEX=1;' )as frm2 on frm2.[F2]=frm1.[F5]" Set Rst = OpenRecordSet(Sql, Cn) If TypeName(Rst) = "Nothing" Then Exit Sub Range("A135").CopyFromRecordset Rst Rst.Close: Cn.Close Set Rst = Nothing: Set Cn = Nothing End Sub Public Function OpenConnetion(FichierXls As String, AvecTitre As Boolean) As Object On Error Resume Next Dim HDR If Dir(FichierXls) = "" Then MsgBox FichierXls & vbCrLf & "Pas trouvé": Exit Function HDR = Array("No", "Yes") Set OpenConnetion = CreateObject("ADODB.Connection") With OpenConnetion .Provider = "Microsoft.ACE.OLEDB.12.0" .ConnectionString = "Data Source=" & FichierXls & ";Extended Properties=""Excel 12.0 Xml;HDR=" & HDR(Abs(AvecTitre)) & ";IMEX=1;""" .Open If Err Then MsgBox Err.Description Set OpenConnetion = Nothing End If Err.Clear On Error GoTo 0 End With End Function Public Function OpenRecordSet(Sql, Cn As Object) As Object 'Retourne un RecordeSet On Error Resume Next Set OpenRecordSet = CreateObject("ADODB.Recordset") OpenRecordSet.Open Sql, Cn, 1, 3 If Err Then MsgBox Err.Description Set OpenRecordSet = Nothing End If Err.Clear On Error GoTo 0 End Function

26/07/2016, 22h02
nisrine hammout

Code:

"Select Frm1.[10] from [" & client & "$] as Frm1 inner join (Select * from [" & safran & "$] in '" & Fi2 & "' 'excel 8.0;HDR=no;IMEX=1;' )as frm2 on frm2.[F2]=frm1.[F5]"

vous m'avez fourni ce code
Mais, quand on a étudié le langage de base de données on travaille seulement comme le suivant :

Code:

" select [" & client & "$].[F10] from [" & client & "$] , [" & safran & "$] where [" & safran & "$].[F2]=[" & client & "$].[F5] and [" & safran & "$].[F8]=[" & client & "$].[F9] and [" & safran & "$].[F7]=[" & client & "$].[F8]"

Ma question est la suivante est ce que vba comprend seulement votre méthode car je l'ai vu aussi dans un tuto ?
si oui comment faire pour mettre plusieurs conditions dans le where qui est equivaut à on dans votre code?

et Merci
26/07/2016, 22h28
Invité

Oui bien que vba comprend mon code . Si tu copy Cole le code dans un module vba et que tu testes la sub test tu me diras.

On ne t'as jamais appris qu'il était possible de renommer un from?
select frm.* from mytable as frm
26/07/2016, 22h35
nisrine hammout

le trucs que je suis pas habitué c'est inner join on
26/07/2016, 22h49
Invité

Code:

select champ from table1 inner join table2 on table2.champx=table1.champx

C'est la même chose que

Code:

select champ from table1 ,table2 where table2.champx=table1.champx
26/07/2016, 22h54
nisrine hammout

bah c'est ca ma question et Merci
Sinon si vous avez un peu de temps à mettre des commentaires sur ton code ça me ferait plaisir

et c'est la meme chose pour code vba?

Non et comme la je suis sur mon téléphone portable c'est pas pratique, demain! Pas de problème.

Code:

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
40
41
42
43
44
45
46
47
48
49
50
51
52
53
Sub Test()
Dim Cn As Object, Rst As Object, Sql As String, Fi1 As String, Fi2 As String,client  as String,safran   as String
Fi2 = "C:\Users\admin\Desktop\nouveau travail\safran.xlsm"
Fi1 = "C:\Users\admin\Desktop\nouveau travail\Copie de FormIDD_DERX189100_RETOUR_SES.xlsm"
client = "Description_et_Decision_Techn"
safran = "Feuil1"
Set Cn = OpenConnetion(Fi1, False) 'ici j'ouvre une connexion adodb via une fonction
If TypeName(Cn) = "Nothing" Then Exit Sub 'si la connexion ne se fait pas je quitte la sub
 
'la je formule ma requête frm1 et ma sous requête frm2
'il est possible d'imbriquer dz requête frm2 fait un jointure externe au deuxième fichier Excel 
Sql = "Select Frm1.[F5]  from [" & client & "$] as  Frm1 inner join   (Select * from [" & safran & "$] in '" & Fi2 & "'  'excel 8.0;HDR=no;IMEX=1;' )as frm2 on frm2.[F2]=frm1.[F5]"
Set Rst = OpenRecordSet(Sql, Cn) 'la j'ouvre mon recordset via une fonction 
If TypeName(Rst) = "Nothing" Then Exit Sub 'si erreur je quitte la sub
Range("A135").CopyFromRecordset Rst
Rst.Close: Cn.Close
Set Rst = Nothing: Set Cn = Nothing
End Sub
 
Public Function OpenConnetion(FichierXls As String, AvecTitre As Boolean) As Object
'ouvre la connexion au fichier Excel 
'FichierXls non et chemin complet du fichier
'AvecTitre précise si la première ligne de l'onglet est les entête de colonnes ou pas
'rzutourne la connexion 
On Error Resume Next
Dim HDR
If Dir(FichierXls) = "" Then MsgBox FichierXls & vbCrLf & "Pas trouvé": Exit Function ' versifie si le fichier existe
HDR = Array("No", "Yes")
Set OpenConnetion = CreateObject("ADODB.Connection") Instancie un objet adosb c'est mieux que d'utiliser le références 
With OpenConnetion
        .Provider = "Microsoft.ACE.OLEDB.12.0"
        .ConnectionString = "Data Source=" & FichierXls & ";Extended Properties=""Excel 12.0 Xml;HDR=" & HDR(Abs(AvecTitre)) & ";IMEX=1;"""
        .Open
        If Err Then
            MsgBox Err.Description
            Set OpenConnetion = Nothing
        End If
        Err.Clear
        On Error GoTo 0
End With
End Function
Public Function OpenRecordSet(Sql, Cn As Object) As Object 'Retourne un recordset 
'Retourne un RecordeSet
On Error Resume Next
Set OpenRecordSet = CreateObject("ADODB.Recordset")
OpenRecordSet.Open Sql, Cn, 1, 3 'ouvre un recordset sur la requête SQL pour la connexion en lecteur écriture et ajou dynamique
If Err Then
    MsgBox Err.Description
Set OpenRecordSet = Nothing
End If
Err.Clear
On Error GoTo 0
End Function

26/07/2016, 22h59
nisrine hammout

Merci Pour votre aide

quand j'ai fait

Code:

Select Frm1.[F5] from [" & client & "$] as Frm1 , [" & safran & "$] in '" & Fi2 & "' 'excel 8.0;HDR=no;IMEX=1;' as frm2 where frm2.[F2]=frm1.[F5] and frm2.[F8]=frm1.[F9] and frm2.[F7]=frm1.[F8]

il m'a donné erreur dans la clause

Code:

From
26/07/2016, 23h18
Invité

Tu est obligé de faire des requête imbriquer, dis toi qu'Excel n'est pas vraiment un base de données il faut composer!

Si tu travaillais sur un autre onglets du même classeur tu ferais comme tu as l'habitude.
Sql = "Select Frm1.[F5] from [" & client & "$] as Frm1, (Select * from [" & safran & "$] in '" & Fi2 & "' 'excel 8.0;HDR=no;IMEX=1;' )as frm2 Where frm2.[F2]=frm1.[F5]"

Code:

in '" & Fi2 & "' 'excel 8.0;HDR=no;IMEX=1;' )

Permet de faire une jointure externe een précisant le driver utiliser, ca pourrais être un fichier csv ,Accès ou autre

Encore je sais pas comment faire et j'ai tous ecris dans le commentaire du code

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
sqlmin = "Select  Frm2.[F9]  from [" & client & "$] as  Frm1 ,  (Select * from [" & safran & "$] in  '" & Fi2 & "'  'excel 8.0;HDR=no;IMEX=1;' )as frm2 where frm2.[F2]=frm1.[F5] and frm2.[F7]=frm1.[F8]   "
sqlmin = "Select  Frm2.[F10]  from [" & client & "$] as  Frm1 , (Select * from [" & safran & "$] in  '" & Fi2 & "'  'excel 8.0;HDR=no;IMEX=1;' )as frm2 where frm2.[F2]=frm1.[F5] and frm2.[F7]=frm1.[F8]   "
 
Set Rst = OpenRecordSet(Sql, Cn)
Set rstmin = OpenRecordSet(sqlmin, Cn)
Set rstmax = OpenRecordSet(sqlmax, Cn)
If TypeName(Rst) = "Nothing" Then Exit Sub
 
Rst.Close: Cn.Close
Set Rst = Nothing: Set Cn = Nothing
If Sql > sqlmin And Sql < sqlmax Then
   'pointer sur la cellule correspondante à sql dans la colonne [F14] et lui donner la valeur "AEE" je sais pas comment faire
Else
   'pointer sur la cellule correspondante à sql dans la colonne [F14] et lui donner la valeur "INC" je sais pas comment faire
End If

Bonjour,
Citation:
Code:

1 2 3 4 5 If Sql > sqlmin And Sql < sqlmax Then 'pointer sur la cellule correspondante à sql dans la colonne [F14] et lui donner la valeur "AEE" je sais pas comment faire Else 'pointer sur la cellule correspondante à sql dans la colonne [F14] et lui donner la valeur "INC" je sais pas comment faire End If
là je ne comprends pas!
Si tu veux modifier tes cellules via ado, il faut faire une requête de mise à jour, ado ne te donnera pas l'adresse de la cellule! Ca na plus rien à voir avec Excel mais avec une base de données.

Envoi moi deux fichier Excel en exemple en remplaçant les données confidentiel par de données bidonnées.

Dans l'éditeur click sur gérer les pièces jointes.

Et détails moi ce que tu veux faire.

27/07/2016, 08h46
nisrine hammout

Ce que je veux faire c'est avoir des valeurs relevée corrseponds à l'item par exemple 234 de fichier excel client par la requete sql

Et avoir la valeur mini accepte de la base critere de la societe par la requete sql mini

Ainsi la valeur maxi par la requete sqlmax

Apres comparer la valeur relevée avec la valeur min et max acceptée

Si elle est dans l'intervalle on écrit dans la colonne de sanction coorsependant à la valeur que cest acceptee

Sinon on ecrit rejetée