Vba copie de données selon différents critères sql

[creal69360]

Bonjour,

Dans le cadre de mon stage, je dois réaliser une analyse qui me permettrait grâce à une BDD contenant des données RH ( retards ....) de réaliser une requete. cette requete me créeraient une nouvelle BDD que je copierai dans une nouvelle feuille. je pourrais alors faire des statistiques selon différents critères ( date, unité ....). pour se faire j'ai écrit ce code qui doit normalement copier les données selon les critères dans un onglet de ma feuille.
Voici le code :
Public Sub analyse()

End Sub
Dim Cn As ADODB.Connection

Dim nomfeuille As String, SQL_analyseunite As String
Dim RST As ADODB.Recordset
Dim fichier1 As String


nomfeuille = "Feuil1"
fichier1 = "C:\Mathieu\2011-ANOMS TOUTES UNITES maj.xlsx"
Set Cn = New ADODB.Connection

Cn.Open "Provider=Microsoft.ACE.OLEDB.12.0;" & "Data Source=" & fichier1 & ";Extended Properties=Excel 8.0;"

SQL_analyseunite = "Select * from [" & nomfeuille & "$] "where " & date messa & "> #dtmin.value# and & date
' dtmin. value et dtmax.value sont tirés du formulaire qui permettrait de réaliser cette analyse
messa & " < #dtmax.value# " and "& Libellé US & " =unite.value;"



Set RST = New ADODB.Recordset
Set RST = Cn.Execute(SQL_analyseunite)
For i = 0 To RST.Fields.Count - 1
Cells(1, i + 1) = RST.Fields(i).Name
Next i
Sheets("analyse unite").Select
' c'est la feuille dans laquelle je veux que mes données issues de la requete se copient
Range("a2").CopyFromRecordset RST




Cn.Close
Set Cn = Nothing

End Sub

c'est a partir de la requete sql que l'erreur se presente. je tiens a preciser que les données que je gere sont confidentielles donc je pourrais pas vous envoyez lintegralité de la BDD.
Merci de m'aider ^^

 

[creal69360]

Re : Vba copie de données selon différents critères sql

elle correspond a la date ou on réalise l'importation, il faut la mettre apres la date maj ( je l'ai peut etre oublié sur la BDD que j'ai envoyé). En fait ce serait comme la date maj sauf que c'est pour la suppression, cela nous permettrai de savoir si la ligne est toujours valable et en plus de connaitre quand elle ne l'ai plus sa date de suppression. En esperant que tu comprennes de mieux en mieux ce que je veux faire.

 

[david84]

Re : Vba copie de données selon différents critères sql

Re
Donc il te suffit d'intégrer dans le code qui te comparera les 2 onglets (onglet 1 pour la BDD existante et onglet 2 pour la BDD actualisée) le fait qu'à chaque fois que la ligne présente dans l'onglet 1 ne l'est plus dans l'onglet 2, il place dans dans la colonne adéquate la date du jour.
Fais un fichier avec 2 onglets :
- un onglet BDD comportant tes données avec notamment une colonne où tu peux renseigner la date d'importation
- un onglet MAJ comportant la dernière mise à jour.
Il ne te reste qu'à faire un code permettant :
- de comparer les 2 onglets
- de placer dans la colonne date d'importation (appelle-la comme tu veux) la date du jour lorsque la ligne de l'onglet BDD n'apparaît pas dans l'onglet MAJ.

Regarde ici sur le site de JB les différents exemples permettant de comparer 2 BDD.
Regarde les exemples présentés et sélectionne celui qui te paraît le plus proche de ce que tu veux faire.
Essaie de l'adapter et si tu n'y arrives pas, reviens avec le fichier (1 seul avec les 2 onglets) pour que nous puissions t'aider (ne pas oublier de noter manuellement le résultat attendu (fais-le sur un 3ème onglet nommé Résultat par exemple).
A+

 

[creal69360]

Re : Vba copie de données selon différents critères sql

re,

ok merci je vais essayer de faire tout ça, par contre j'ai constaté que mon code permettant d'ajouter les nouvelles données de la maj dans la BDD ne fonctionnait pas si des lignes disparaissent, il va donc y avoir du travail^^

 

[david84]

Re : Vba copie de données selon différents critères sql

Re

ok merci je vais essayer de faire tout ça, par contre j'ai constaté que mon code permettant d'ajouter les nouvelles données de la maj dans la BDD ne fonctionnait pas si des lignes disparaissent, il va donc y avoir du travail^^

prenons les problèmes un par un et partons d'un cas concret que je pourrai tester et ainsi t'aider à avancer (j'ai bien dit t'aider, et non faire tout à ta place !).
Et puis ce que tu n'auras pas réussi à faire tu l'exposeras à ton maître de stage qui est là pour cela.
A+

 

[creal69360]

Re : Vba copie de données selon différents critères sql

oui bien sur l'objectif c'est que j'apprenne le maximum de choses et pas que l'on fasse le travail à ma place !!!! Bonne journée à toi, je te montrerai ce que j'ai fait plus tard ^^

 

[creal69360]

Re : Vba copie de données selon différents critères sql

re,

voila j'ai réussi à récupérer le nom des personnes qui sont présentes dans la table bdd mais absentes de la table maj ainsi que la ligne à laquelle elles sont présentes dans la table BDD. j'aimerais maintenant savoir comment grace à la ligne qu'on obtient ( ex:63 ), écrire dans la colone date supp à la ligne 63 la date du jour. Merci. J'envoie comme aide le fichier BDD que j'obtiens.

 

[david84]

Re : Vba copie de données selon différents critères sql

Re
1) As-tu pris le temps de regarder voire tester les exemples présents dans le lien fourni ?
Si oui, quel est celui qui semble correspondre le plus à ta problématique ?

2) Concernant ton fichier maintenant, donne plus d'explications :
- pour être considérée comme une même ligne, une ligne présente dans BDD doit-elle être en tout point identique à celle présente dans MAJ ?

- la recherche doit-elle prendre en compte chaque item de chaque ligne (donc comparer chaque valeur présente dans les colonnes Nom à Date messa en passant par compteur, compteur1,...) ou suffit-il de comparer les valeurs présentes uniquement dans certaines colonnes des 2 lignes (nom, prénom et code erreur par exemple) ?

- La feuille BDD_Maj comporte un nom et un n° de ligne : j'en conclus donc que la ligne 64 est la seule ligne qui n'est pas présente dans BDD : c'est cela ?
Si oui, dans cet onglet BDD_Maj, tu veux uniquement les nom et n° de ligne des lignes non retrouvées dans BDD : Est-on d'accord ?
A+

 

[creal69360]

Re : Vba copie de données selon différents critères sql

re,

Concernant les lignes identiques, en effet il faut qu'elle soit identique en tout point car le meme nom peut apparaitre plusieurs fois dans la BDD , il faut donc passer de compteur à compteur et tu as enfin raison en disant que la ligne 64 est la seule ligne qui n'était pas présente dans la BDD. j'ai utilisé pour cela le code suivant donné par ton lien qui est très bien d'ailleur:
-Sub BD2_BD1()
Set f1 = Sheets("Base1")
Set f2 = Sheets("Base2")
Set MonDico1 = CreateObject("Scripting.Dictionary")
For Each c In f1.Range("a2:a" & f1.[a65000].End(xlUp).Row)
MonDico1.Item(c.Value) = c.Value
Next c
Set MonDico2 = CreateObject("Scripting.Dictionary")
For Each c In f2.Range("a2:a" & f2.[b65000].End(xlUp).Row)
If Not MonDico1.exists(c.Value) Then
If Not MonDico2.exists(c.Value) Then MonDico2.Add c.Value, c.Row
End If
Next c
Sheets("Base2_Base1").[A2].Resize(MonDico2.Count, 1) = Application.Transpose(MonDico2.keys)
Sheets("Base2_Base1").[b2].Resize(MonDico2.Count, 1) = Application.Transpose(MonDico2.items)
End Sub

j'ai juste eu à modifier le nom des feuilles et sa a marché. Maintenant je voudrais que grace au numéro de ligne je puisse écrire a la meme ligne dans date suppr la date du jour. J'aimerai donc savoir si cela est possible, cette méthode de récuperation de la ligne me semble la plus efficace, apres je me trompe peut etre. Mais si tu pouvais me dire comment a partir des numero de ligne que j'obtiens je pourrais remplire ma colone date suppr ce serait cool. merci bonne soirée

 

[david84]

Re : Vba copie de données selon différents critères sql

Re
la macro de base est bonne mais si tu veux tester toutes les données d'une ligne, tu dois le prévoir dans ton code.
Ci-joint le code retravaillé à tester (placé pour l'instant dans un bouton de formulaire) :
- avant de le lancer, regarde les dates des 2 dernières lignes de BDD (j'ai rajouté une autre ligne différente pour le test) et la feuille BDD_Maj qui doit être vide.
- après avoir lancé la macro, vérifie à nouveau les dates des 2 dernières lignes et regarde dans la feuille BDD_Maj.

Sub ComparerBDD()
Dim Pl1 As Range, Pl2 As Range
Dim MonDico1 As Object, Mondico2 As Object, T(), col As Long, i As Long, j As Long, k As Long, temp As String
Set Pl1 = Sheets("BDD").Range("A2").CurrentRegion: Set Pl1 = Pl1.Offset(1, 0).Resize(Pl1.Rows.Count - 1, Pl1.Columns.Count - 1)
Set Pl2 = Sheets("MAJ").Range("A2").CurrentRegion: Set Pl2 = Pl2.Resize(Pl2.Rows.Count, Pl1.Columns.Count)
k = 1
Set MonDico1 = CreateObject("Scripting.Dictionary")
For i = 1 To Pl2.Rows.Count
    For j = 1 To Pl2.Columns.Count
        temp = temp & " " & Pl2(i, j)
        Next j
    MonDico1(temp) = temp: temp = ""
Next i
Set Mondico2 = CreateObject("Scripting.Dictionary")
For i = 1 To Pl1.Rows.Count
    For j = 1 To Pl1.Columns.Count
        temp = temp & " " & Pl1(i, j)
    Next j
If Not MonDico1.exists(temp) Then
    If Not Mondico2.exists(temp) Then
        Sheets("BDD").Cells(i + 1, Pl1.Columns.Count + 1) = Date 'place la date du jour
        Cells(i + 1, Pl1.Columns.Count + 1).Interior.ColorIndex = 7 'colorie la cellule en bleu
        Mondico2.Add temp, temp
        'stocke les n° de ligne à recopier dans un tableau VBA
        ReDim Preserve T(1 To Pl1.Columns.Count, 1 To Mondico2.Count)
        For col = 1 To Pl1.Columns.Count
            T(col, k) = Pl1(i, col)
        Next col
        k = k + 1
        temp = ""
    End If
End If
temp = ""
Next i
With Sheets("BDD_Maj")
    .Cells.ClearContents 'efface le contenu des celllules de la feuille BDD_Maj
    .Range("A2").Resize(Mondico2.Count, Pl1.Columns.Count) = Application.Transpose(T) 'copie la tableau VBA dans la feuille
End With
End Sub

A+

 

[creal69360]

Re : Vba copie de données selon différents critères sql

ok c'est super bien ça car sa ne m'oblige pas à créer la variable date supr car on peut voir si la donnée a été supprimer grace au surlignage violet. Merci beaucoup^^. Je vais voir maintenant pour intégrer ce code à mon programme, si j'ai du mal je te recontacte. a+

 

[creal69360]

Re : Vba copie de données selon différents critères sql

En fait ce que je vais essayer de faire, c'est de créer un 4e onglet excel. Dans lequel il y aurait les données présentes dans la maj mais absentes dans la BDD. Les données qui se mettraient alors dans cet onglet se mettrait alors à la suite de la BDD.

 

[creal69360]

Re : Vba copie de données selon différents critères sql

Je t'avouerai que j'ai un peu du mal à comprendre le code et donc à le modifier pour faire l'inverse ( récupérer dans une nouvelle feuille toutes les lignes présentes dans la maj et absentes dans la BDD). Voila ce que j'ai compris:
on crée une variable mondico1 qui va permettre grace au 2 boucles ( une pour les colonnes et l'autre pour les lignes ) de récupérer les données de la table maj et vis versa avec mondico2 pour la table BDD. Le probleme c'est que je comprends pas à a quoi sert la variable temp et ce qu'elle représente. Je comprends pas par exemple ces 2 lignes de code:
-MonDico1(temp) = temp: temp = ""
-temp = temp & " " & Pl1(i, j)

Ce serait sympa que tu m' expliques un peu plus comment fonctionne ce code pour que je puisse le faire fonctionner dans le sens inverse. Merci

 

[david84]

Re : Vba copie de données selon différents critères sql

Re
J'utilise l'objet Dictionary pour stocker les lignes présentes dans l'onglet MAJ (MonDico1).
Pour des infos sur cet objet, cf.

Ce lien n'existe plus

ou

Objet dictionary

Dictionary étant un objet, je l'instancie avec le mot clé Set (comme tous les objets créés) :

Set MonDico1 = CreateObject("Scripting.Dictionary")

Le problème dans ton cas est qu'il te faut comparer chaque ligne dans son intégralité. Donc j'utilise une boucle afin de ramener dans une variable temporaire nommée temp toutes les données de chaque ligne :

For i = 1 To Pl2.Rows.Count 'je boucle sur toutes les lignes de la plage Pl2
    For j = 1 To Pl2.Columns.Count 'je boucle sur toutes les colonnes de chaque ligne
        temp = temp & " " & Pl2(i, j) 'j'incrémente chaque données à la suite séparée d'une espace
        Next j
    MonDico1(temp) = temp: temp = ""
Next i

Ainsi, pour la ligne 1 de MAJ, temp donne BOUDOT puis BOUDOT CHRISTINE puis BOUDOT CHRISTINE B7, etc.
la boucle j permet donc de charger dans la variable temp la ligne complète.
Une fois l'ensemble des colonnes de la ligne traité (après Next j) :
- je charge le contenu de temp dans mon dico

MonDico1(temp) = temp

, puis je vide temp afin de traiter la ligne suivante

temp = ""

Une fois l'onglet MAJ traité et dont les lignes sont placées dans MonDico1, je traite l'onglet BDD de la même manière, en ne chargeant dans MonDico2 que les lignes qui n'existent pas dans MonDico1

If Not MonDico1.exists(temp) Then

et qui ne sont pas déjà présentes dans MonDico2 (au cas ou tu as 2 lignes identiques sur une même feuille)

If Not Mondico2.exists(temp) Then

Si c'est le cas :
- je mets la date du jour dans l'onglet BDD

 Sheets("BDD").Cells(i + 1, Pl1.Columns.Count + 1) = Date

- je colorie cette cellule

Cells(i + 1, Pl1.Columns.Count + 1).Interior.ColorIndex = 7

- j'ajoute cette ligne dans MonDico2

Mondico2.Add temp, temp

- je stocke cette ligne dans un tableau :

 ReDim Preserve T(1 To Pl1.Columns.Count, 1 To Mondico2.Count)
        For col = 1 To Pl1.Columns.Count
            T(col, k) = Pl1(i, col)
        Next col
        k = k + 1
        temp = ""

Enfin, je me place dans l'onglet BDD_Maj, efface toutes les données de cette feuille et recopie le tableau VBA dans cette feuille :

With Sheets("BDD_Maj")
    .Cells.ClearContents 'efface le contenu des celllules de la feuille BDD_Maj
    .Range("A2").Resize(Mondico2.Count, Pl1.Columns.Count) = Application.Transpose(T) 'copie la tableau VBA dans la feuille
End With

Sais-tu tester une macro en pas à pas ? Si c'est le cas, affiche la fenêtre des variables locales (onglet Affichage dans l'éditeur VBE), clique dans le code et appuie sur F8 (chaque appui sur cette touche fait progresser la macro d'une étape) : suis le déroulement de la macro dans cette fenêtre (temp, les dictionnaires en cliquant sur la croix,...).

Pose des questions si besoin sur les points que tu n'as pas compris.
A+

 

[creal69360]

Re : Vba copie de données selon différents critères sql

Merci tout est plus claire maintenant, je vais à présent faire le code pour récupérer les données présentes dans maj mais pas dans BDD.

 

[creal69360]

Re : Vba copie de données selon différents critères sql

Re,

voila, j'ai modifié un peu le code que tu m'avais donné pour pas que les données supprimés soient copiées dans le 3e onglet ( pas d'utilité car je veux juste savoir si elles ont été supprimé dans la BDD donc la couleur me suffit ). J'ai ensuite créer une nouvelle fonction à partir de la tienne qui me permet de récupérer les données presentes dans maj et absentes dans BDD dans la 3e feuille ( en supprimant la couleur de la date ). Cela fonctionne donc parfaitemment, mais j'aimerais maintenant que ces données présentes dans la 3e feuille se mettent à la suite de la feuille BDD, pour enfin recopié la feuille BDD dans mon fichier BDD. Merci de m'éclairer.