Jointure sur une colonne
Bonjour,
Je démarre en vba, j'ai essayé d'éplucher le forum pour trouver une solution mais ça ne correspond pas exactement à ce que je souhaite. Je souhaiterai travailler en macro sur des expressions de type WHERE et INNER JOIN en SQL.
1/ Je souhaiterai récupérer la liste des factures dont le Test=Y et les coller dans un un nouvel onglet.
2/ Je souhaiterai récupérer dans le 1er onglet toutes les lignes correspondant aux factures du 2e onglet et les insérer dans un 3e onglet.
La jointure entre l'onglet 1 et 2 se ferait sur le numéro de facture.
En SQL, ça me parait bateau ms je ne connais pas encore bien les macros excel.
J'ai mis un exemple en PJ histoire que ce soit un peu plus clair.
Je vous remercie vraiment d'avance pour votre aide.
bonjour
ma suggestion : pour gérer des données, on ne recopie pas les infos d'une feuille à une autre
on fait des tris et des filtres
je n'ai pas compris la notion de "jointure" (analogie avec les SGBD ? )
voir éventuellement RECHERCHEV qui permet d'afficher une valeur venant d'une feuille Z en face d'un repère en feuille X
Bonjour,
Merci beaucoup pour votre réponse.
ma suggestion : pour gérer des données, on ne recopie pas les infos d'une feuille à une autre
on fait des tris et des filtres
En fait, si je reprends mon fichier exemple, ce que je souhaite obtenir au final c'est un classeur avec l'onglet 1 et 3. Le seul moyen que j'ai trouvé pour y parvenir était de passer par un onglet intermédiaire.
En fait je souhaite automatiser au lancement de ma macro:
1/ je parcours le fichier et je récupère ttes les factures qui correspondent à un certain test
2/ pour ces factures uniquement, je récupère ttes les lignes correspondantes.
En ce qui concerne la RechercheV, je pensais ms peut être à tort, qu'elle ne pouvait récupérer qu'une ligne à partir d'un numéro de facture. Je souhaite pour ma part en récupérer plusieurs par numéro de facture.
Qu'est-ce que vs en pensez?
Lorsque je parle de jointure je fais bien l'analogie avec les SGBD, onglet1 étant une table et onglet2 une autre table; onglet3 étant le résultat d'une requête où on fait la jointure entre onglet1 et onglet2 sur la colonne Facture.
Mais bon, peut être que l'analogie induit en erreur...
Encore merci pour votre aide
re
on reprend selon ton besoin :
- pourquoi reporter des données d'une feuille dans une autre, alors qu'un filtre suffit ?
- si tu veux simuler une jointure N-1 avec Excel alors c'est bien RECHERCHEV qu'il faut
voir un exemple ici
https://forum.excel-pratique.com/post514965.html?hilit=*achat*#p514965
Ah c'est intéressant ça!
Pour être bien sûr que je peux utiliser ta méthode, je t'explique mes contraintes:
- les données du 1er onglet seront constamment modifiées
Est-ce que ça pose problème?
En regardant ton exemple, j'ai qd même du mal à comprendre comment ça peut fonctionner avec une RechercheV.
Est-ce que tu pourrais me le montrer sur mon fichier exemple stp?
Ca m'aiderait vraiment à bien comprendre...
Merci bcp
Bonjour à tous,
Il est relativement simple de requêter un fichier Xl avec du Sql.
Ci-joint ton fichier en retour avec un exemple qui devrait répondre à ce que tu cherches à faire (enfin il me semble)
Le résultat de la requête est collé en Feuil4 après clic sur le bouton
La fonction Query est une fonction générique (perso) qui peut être copiée/collée en l'état dans n'importe quel projet avec sa variable public Rcd, ensuite tu compose ta requête Sql comme dans l'exemple.
A noter sur des fichiers Xl ok pour Select, Insert et Update mais pas de Delete possible.
Sinon ok pour Union, Inner join, Left join, Group by, Order by, ... mais quelques limitations existent par rapport au Sql standard quand même.
Bon week-end
Pierre
re
salut Pierre
un exemple en orange, qui récupère les données du petit tableau et les affiche dans le grand
insensible aux ajouts de colonnes ou lignes (attention dans RECHERCHEV(D2;$K$11:$L$12;2) la plage $K$11:$L$12 est à définir judicieusement. Dans l'idéal, il faut mettre sous forme de tableau (automatisme lors de l'ajout de lignes)
Merci beaucoup pour vos réponses.
Ca ressemble effectivement plus à ce que je veux faire!Il est relativement simple de requêter un fichier Xl avec du Sql.
Par contre je suis arrêté au lancement de ta macro: Un composant ActiveX ne peut pas créer d'objet.
Faut-il installer qqch en plus d'excel pr le faire marcher?
@jmd, merci pour ton fichier, je ne pense pas que ça marche ds mon cas... en fait je veux automatiser la création du petit tableau en orange, et ensuite récupérer les lignes uniquement pour ces factures.
Merci beaucoup pour votre aide
Ah, je viens de voir que tu es avec un Mac ... Pas de bol, c'est pas compatible.
Essaye de lancer la macro 'Test' sans passer par le bouton (sur PC c'est Alt+F8 et choix de la macro'Test'
Sinon le code c'est :
Option Explicit
Public Rcd() As Variant
Sub Test()
Dim Req As String, lig As Long, i As Long
Sheets("Feuil4").UsedRange.ClearContents
With Sheets("Feuil2")
lig = .Range("A" & Rows.Count).End(xlUp).Row
Req = "SELECT Mois, Catégorie, Libelle, Facture, Debit, Credit FROM [Feuil1$] " & _
"WHERE Facture='" & .Cells(2, "D") & "' "
For i = 3 To lig
Req = Req & "UNION " & _
"SELECT Mois, Catégorie, Libelle, Facture, Debit, Credit FROM [Feuil1$] " & _
"WHERE Facture='" & .Cells(i, "D") & "' "
Next i
End With
If Query(Req) > 0 Then
Sheets("Feuil4").Range("A1").Resize(UBound(Rcd, 1), UBound(Rcd, 2)) = Rcd
End If
End Sub
Function Query(Req As String) As Long
Dim Cnx As Object, Rst As Object, T As Variant
Dim Ndf As String, Col As Integer, i As Long, j As Long
On Error GoTo errhdlr
Ndf = ActiveWorkbook.Path & "\" & ActiveWorkbook.Name
Set Cnx = CreateObject("ADODB.Connection")
Cnx.Provider = "MSDASQL"
Cnx.Open "Driver={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)};" & _
"DBQ=" & Ndf & "; ReadOnly=False;"
If Left(Req, 6) = "SELECT" Then
Set Rst = CreateObject("ADODB.Recordset")
Rst.Open Req, Cnx, 3
Query = Rst.RecordCount
Col = Rst.Fields.Count
ReDim Rcd(Query + 1, Col)
For j = 0 To Col - 1
Rcd(0, j) = Rst.Fields(j).Name
Next j
If Not Query = 0 Then
Rst.MoveFirst
T = Rst.GetRows
For i = 0 To Query - 1
For j = 0 To Col - 1
Rcd(i + 1, j) = IIf(IsNull(T(j, i)), 0, T(j, i))
Next j
Next i
End If
Else
Cnx.Execute Req
Query = 0
End If
Cnx.Close
Set Rst = Nothing
Set Cnx = Nothing
Exit Function
errhdlr:
If Not Rst Is Nothing Then If Rst.State = 1 Then Rst.Close
If Not Cnx Is Nothing Then If Cnx.State = 1 Then Cnx.Close
Set Rst = Nothing
Set Cnx = Nothing
Query = -1
MsgBox (Err.Description & vbCrLf & vbCrLf & "Vérifier la requête : " & vbCrLf & Req)
End FunctionOu emprunte un PC c'est fonctionnel
Pierre
Alors, je dois pas avoir de bol
J'ai essayé de la lancer sur un PC, j'ai un message d'erreur [Gestionnaire de pilotes ODBC] Source de données introuvable et nom de pilote non spécifié.
Tu as une idée?
Bonjour,
Testé sur 2 PS différents sous Windows 10 avec Office 2007 pour l'un et Office 2016 pour l'autre sans aucun addin particulier : fonctionnel dans ces 2 cas.
A noter qu'il est nécessaire d'enregistrer le fichier sur l'ordi pour que ça fonctionne.
Pour les versions d'Office antérieures je ne sais pas si c'est compatible.
Pierre
Encore merci pour votre aide...
En fait ce n'est pas compatible avec les versions antérieures d'excel. Je suis sous 2003 sur PC.
Tu vois une autre façon de faire cette requête d'UNION en macro sans passer par du SQL?
Je veux faire exactement ce que tu as fait dans ton exemple...
Merci beaucoup!!
Ah ok, alors si c'est une version 2003, il suffit probablement de modifier le code en enlevant les références non compatibles (et enregistrer le fichier au format 97-2003.
Essaye cette version jointe
Function Query(Req As String) As Long
Dim Cnx As Object, Rst As Object, T As Variant
Dim Ndf As String, Col As Integer, i As Long, j As Long
On Error GoTo errhdlr
Ndf = ActiveWorkbook.Path & "\" & ActiveWorkbook.Name
Set Cnx = CreateObject("ADODB.Connection")
Cnx.Provider = "MSDASQL"
Cnx.Open "Driver={Microsoft Excel Driver (*.xls)};" & _
"DBQ=" & Ndf & "; ReadOnly=False;"re à vous
mrmatt,
suite à message privé : aucune crainte à avoir concernant la "lourdeur" des formules, même si > 10000 lignes. Tu peux faire un essai (il est facile de créer des milliers de lignes factices pour des tests)
as-tu vu comment fonctionne RECHERCHEV (mon message ci-dessus Dim Mars 12, 2017 8:27 am ) ?
Bonjour Mrmatt31, Jmd et à tous,
Si le fichier avec Sql ne va pas sur ton ordi, tu peux tester celui-ci sans Sql.
Le code est plus court avec une vitesse d'exécution similaire (et peut être compatible avec ton Mac?)
Pierre
Bonjour à vous,
@pierrep56, c'est génial, ça fonctionne!!! Le 2e sans SQL fonctionne sur mon MAC aussi, du coup je pense que je vais plutôt utiliser celui là...
Est-ce que ce serait trop de demander de me commenter un peu plus le code pour que je comprenne mieux ce que tu as fait, et que je le refasse par moi même la prochaine fois?!
@jmd, j'ai bien compris comment fonctionne la RechercheV, par contre comme je voulais ramener plusieurs lignes pour chaque facture, ça ne marchait pas vraiment...
En ts cas, je vous remercie vraiment pour votre efficacité et votre aide
Ok c'est cool!
Pour commenter le code :
* le tout est basé sur l'utilisation de tableaux de variables
* le pivot du code c'est la fonction de filtre 'Select_T' qu'on appelle en lui passant 3 arguments :
- - le tableau à filtrer
- - le n° de colonne contenant les valeurs à filtrer
- - la valeur de filtre
Cette fonction renvoie un tableau.
* pour le corps du code (Sub Test):
- - le contenu de la Feuil4 est vidée
- - le contenu de la Feuil1 (de A à G) est placée dans le tableau de variable Tdata
- - une boucle, ligne par ligne sur le contenu de la Feuil2 :
### place le résultat dans un tableau de variable T
### colle le résultat T dans la feuil4 à la suite des autres tableaux de résultats
### incrémente le n° de ligne 'lg' => à ce sujet il est plus correct d'écrire (sinon bug si + de 2 valeurs à filtrer) :
lg = lg - 1 + UBound(T, 1)Espérant avoir été suffisamment clair?
Pierre
C'est hyper clair, un grand merci!!
Il y a juste la fonction que j'ai un peu de mal à comprendre... Est-ce que c'est le même genre de fonction que je peux utiliser pr filtrer une plage de données selon un critère?
Merci bcp
Ouiii! C'est tout à fait ça! T'es trop fort!
En fait la ligne : T = Select_T(Tdata, 4, .Cells(i, "D")) (après initialisatin de Tdata)
correspond en Sql à : SELECT * FROM Feuil1 WHERE Facture = .Cells(i, "D") (Facture étant en colonne 4)
D'ou le nom que j'ai choisi pour cette function : 'Select' = SELECT du sql et '_T' = sur tableau
Pierre
Super!
Si je reprends mon exemple et que je souhaite passer de Feuil1 à Feuil2 en faisant l'équivalent de:
SELECT * from Feuil1 where Test="Y"
Est-ce que je peux utiliser ta fonction Select_T(Tdata, 5, "Y"), après initialisation de Tdata, comme ça ou il faut que je la modifie?
Merci à toi, tu m'aides énormément