Ajout d'entrées en automatique

Bonsoir,

J’ai de nouveau besoin de votre aide.

Je récupère un fichier (fichier joint « Nouvelle Liste.xlsx ») issue d’une base externe.

Je crée un fichier de synthèse (fichier joint « Synthese.xlsx ») dans lequel je dois recopier les lignes qui sont contenues dans le fichier « Nouvelle Liste.xlsx » mais absente du fichier « Synthese.xlsx ». La recopie se fait à la suite des lignes déjà importées.

La comparaison s’effectue sur la colonne « Licence ». Pour tout nouveau numéro, je dois copier la ligne complète dans le fichier de synthèse. Rien d’autre à comparer que le numéro, les autres champs peuvent avoir des doublons.

Les fichiers ne seront pas forcément dans le même répertoire.

Quelqu’un pourrait m’aider à créer la macro à mettre dans le fichier « Synthese.xlsx » pour que je puisse résoudre mon soucis ?

Merci par avance.

Thierry

Salut,

Dans ton fichier Synthèse, tu places le code suivant

Option Explicit

Sub Actualiser()
Dim i As Integer, j As Integer, DerLig As Integer, DerLig_Bis As Integer, Chemin As String

Application.ScreenUpdating = False

Chemin = "C:\Users\lacy\Documents"
Workbooks.Open Filename:=Chemin & "\Nouvelle Liste.xlsx"

DerLig = ActiveWorkbook.Sheets("Feuil1").Range("B" & Rows.Count).End(xlUp).Row

With ThisWorkbook.Sheets("Feuil1")
    For i = 3 To DerLig
        On Error Resume Next
        j = Application.WorksheetFunction.Match(Range("B" & i), .Range("B:B"), 0)
        If j = 0 Then
            DerLig_Bis = .Range("B" & Rows.Count).End(xlUp).Row + 1
            Range("B" & i & ":I" & i).Copy .Range("B" & DerLig_Bis)
        End If
        j = 0
    Next i
End With

ActiveWorkbook.Close

End Sub

Tu dois alors remplacer le Chemin de mon arborescence par le tien (ligne Chemin = "C:\Users\lacy\Documents").

Si cette première partie te convient, on pourrait encore effectuer une mise en forme de ton tableau tout à la fin.

Cordialement.

Bonsoir Yvouille,

Un grand merci pour le code il marche à merveille.

J'ai 2 doléances si je peux me permettre :

- la 1ère : est-il possible d'avoir quelques commentaires sur le code pour que je le comprenne ?

- la 2ème : est-il possible lors de l'import des lignes de mettre en plus des conditions (par exemple n'importer que les gens dont l'age est supérieur à 30 ans dans un fichier, supérieur à 50 ans dans un autre et les mineurs dans un 3ème ?

Concernant la mise en forme j'ai fait un code avec l'enregistreur de macro pour encadré les valeurs, les colonnes et les lignes. Le code est très long mais il marche.

Il ressemble à cela :

Selection.Borders(xlDiagonalDown).LineStyle = xlNone

Selection.Borders(xlDiagonalUp).LineStyle = xlNone

With Selection.Borders(xlEdgeLeft)

.LineStyle = xlContinuous

.Weight = xlThin

End With

With Selection.Borders(xlEdgeTop)

.LineStyle = xlContinuous

.Weight = xlThin

End With

With Selection.Borders(xlEdgeBottom)

.LineStyle = xlContinuous

.Weight = xlThin

End With

With Selection.Borders(xlEdgeRight)

.LineStyle = xlContinuous

.Weight = xlThin

End With

Etc...

Existe t-il une fonction d'encadrement plus light que toutes ces lignes de code ?

En tout cas de nouveau merci pour l'aide déjà apportée.

Thierry

Re,

Pour tes bordures, juste avant ou juste après l’instruction ActiveWorkbook.Close tu places le bout de code ci-dessous :

With Range("B3:K" & DerLig_Bis)
    .Borders.LineStyle = xlNone
    .Borders.Weight = xlThin
    .BorderAround Weight:=xlMedium
End With

Comme je ne connais pas ton niveau de VBA, il m’est assez difficile de te commenter mon code, ayant peur d’en faire trop ou pas assez. Je te propose alors que tu m’indiques toi les passages sur lesquels tu désires des explications. Et si c’est partout, j’essaierai de répondre quand même à ta demande.

Pour ta répartition selon l’âge, tu parles d’effectuer cette répartition dans des fichiers différents. Alors d’abord j’aimerai que tu me confirmes que tu désires vraiment cela dans des fichiers différents et non pas dans des feuilles différentes d’un même fichier et ensuite, si tu désires la répartition dans des fichiers différents, désires-tu effectuer la répartition en une seule fois depuis ton fichier de base (il n’y aurait donc qu’une seule macro qui ouvrirait non seulement le fichier ‘Nouvelle liste’, mais également tous les fichiers nécessaires à la répartition comme les fichiers ‘Synthèse pour plus de 30 ans’, ‘Synthèse pour les plus de 50 ans’, etc.) ou désires-tu une macro par fichier dans lesquelles viendraient placées les données (une macro dans le fichier ‘Synthèse pour plus de 30 ans’, une macro dans le fichier ‘Synthèse pour les plus de 50 ans’, etc.) ? Dans le cas de plusieurs fichiers, je présume que tous les fichiers dans lesquels s’effectuerait la répartition seraient de même structure que ton fichier ‘Synthèse’.

Par contre si tu désires répartir les données dans plusieurs feuilles d’un même fichier, je te prierais de me fournir un modèle du fichier complet.

Ou n’ai-je strictement rien compris à ta répartition et tu dois me réexpliquer cela un peu mieux ?

A te relire.

Bonjour,

Merci pour le code des encadrements, il est effectivement bien plus simple.

Je ne comprends pas le calcul de J, avec la fonction Match et les différents critères associés dans la ligne de code.

Concernant la répartition par l'age, je souhaites avoir un fichier de synthèse par critère, comme cela je copie le fichier, le renomme et je ne change que le critère age dans la macro.

Merci.

Thierry

Salut,

With ThisWorkbook.Sheets("Feuil1")
    For i = 3 To DerLig
        On Error Resume Next
        j = Application.WorksheetFunction.Match(Range("B" & i), .Range("B:B"), 0)
        If j = 0 Then
            DerLig_Bis = .Range("B" & Rows.Count).End(xlUp).Row + 1
            Range("B" & i & ":I" & i).Copy .Range("B" & DerLig_Bis)
        End If
        j = 0
    Next i
End With

Dans la boucle For i/Next i ci-dessus, on passe en revue tous tes numéros de licence du fichier ‘Nouvelle Liste et l’on recherche chaque numéro l’un après l’autre dans le fichier ‘Synthèse’ à l’aide de la fonction Match.

Cette fonction correspond à la fonction Excel EQUIV() et si tu veux comprendre comment fonctionne mon code, tu pourrais placer la formule

=EQUIV(B3;[Synthese_V1.xlsm]Feuil1!$B:$B;0)

dans la cellule J3 de ton fichier ‘Nouvelle Liste’ et l’incrémenter vers le bas. Tu verrais alors – à condition que ton fichier ‘Synthese’ soit ouvert en même temps - que les licences trouvées ont leur numéro de ligne qui est renvoyé par la formule alors que les licences absentes renvoient #NA (voir l’image ci-dessous).

En VBA, la fonction Match ne renvoie pas un message d’erreur, mais bloque le code si aucun résultat n'est trouvé. Il est donc nécessaire de placer l’instruction On Error Resume Next de manière à ce que si aucune correspondance entre les deux fichiers est trouvée, ça ne bloque pas, mais que ça passe simplement à l’instruction suivante.

Il suffit maintenant d’affecter à la variable j le numéro de la ligne trouvée par la fonction Match (le même numéro de ligne que trouve la fonction Excel EQUIV() pour voir si une ligne existe ou non.

Lors du premier passage de la boucle For/Next, pour la licence 1, un numéro de ligne 3 est trouvé et est affecté à la variable j. A l’instruction suivante on contrôle si j est plus grand que 0, si oui c’est qu’une correspondance a été trouvée et qu’il n’y a rien à faire ; on remet simplement la valeur de j à zéro et on passe à la licence suivante.

Et ainsi de suite jusqu’à ce que la fonction Match créé une erreur, l’instruction j = Application.WorksheetFunction.Match(Range("B" & i), .Range("B:B"), 0) est ignorée à cause de On Error Resume Next, la valeur de j reste à zéro et le code voit qu’il y a des instructions complémentaires à effectuer, soit reporter la ligne concernée sur le fichier ‘Synthese’.

Si quoi que ce soit n’est toujours pas clair, reviens à la charge.

Pour le reste de ton problème, je regarde ça ce soir ou demain.

Cordialement.

Bonjour Yvouille,

Merci pour les explications du code, je vais m'y attarder.

J'ai de nouvelles remarques quant à ce qui existe déjà, je me lance :

- Après la ligne J=0 j''ai ajouté le code ci-dessous afin d'avoir la mise à jour des lignes déjà extraite (par exemple au cas où l'adresse changerait) :

Range("B" & i & ":I" & i).Copy .Range("B" & i + 2)

Le "i + 2" c'est pour gérer le décalage de lignes entre les 2 fichiers.

Ça marche tant que la nouvelle liste rajoute une nouvelle entrée en fin de liste ou modifie un champs existant.

Par contre si par exemple le fichier Nouvelle Liste.xlsx intègre une ligne au milieu de celles déjà existantes (par exemple le numéro de licence 6 qui se situera entre 5 et 7) alors la macro intègre bien la ligne de la licence 6 mais rajoute aussi à la fin un doublon pour toutes celles qui sont situées après le numéro de licence 6 (de 7 à 32) !

- Je voudrais aussi pouvoir si possible supprimer une entrée dans la nouvelle liste et que la synthèse se mette à jour.

Je remets en pièces jointes mes fichiers à jour en version V2.

Merci d'avance pour les éclaircissements et l'aide apportée.

Cordialement.

Thierry

Re

Tu as quand même un peu tendance à jouer à l’apprenti sorcier avec tes codes ; j’ai l’impression que tu essaies n’importe quoi puis que tu demandes que l’on vienne jouer au docteur.

J’ai modifié passablement ta dernière macro avec beaucoup d’informations directement dans le code.

biquet08 a écrit :

Par contre si par exemple le fichier Nouvelle Liste.xlsx intègre une ligne au milieu de celles déjà existantes (par exemple le numéro de licence 6 qui se situera entre 5 et 7)

Je ne vois pas de quoi tu parles. Le code actuel plaçait les nouvelles entrées à la suite des autres, quels que soient leur numéro. Dans le fichier ci-joint, je te propose un tri en plus.

Pour ton histoire de répartition selon l’âge, j’ai laissé ça de côté pour l’instant, afin de ne pas se mélanger les pinceaux en voulant traiter trop de choses à la fois. Mais peux-tu déjà réfléchir à cette proposition : on laisserait la macro actuelle faire son travail et – à la suite – on répartirait par exemple les données selon les critères que tu désires sur plusieurs feuilles ou plusieurs fichiers. Est-ce une solution ?

A te relire.

Re,

Non je ne cherche pas à faire l'apprenti sorcier, mais j'essaie de modifier le code pour essayer d'avancer par moi même. Je te l'accorde avec plutôt moins de succès. Désolé si je te fais perdre du temps, ce n'est absolument pas mon objectif.

Sinon effectivement ton nouveau code est très bien.

Juste un détail lorsque je supprime 3 lignes dans le fichier "Nouvelle_liste V2.xlsx", il faut appuyer 3 fois sur le bouton Import pour les faire disparaitre. En fait l'appui sur ce bouton ne supprime qu'une ligne à la fois.

Concernant la répartition par l'age ta proposition est intéressante (celle de plusieurs onglets).

Penses tu qu'il serait envisageable de paramétrer le critère de tri sur le choix entre 2 colonnes (Sexe ou Age) ? Par exemple on documente dans une case la valeur du critère de tri et un nouvel onglet est créé sur ordre (bouton de commande) ?

Encore merci.

Thierry

Salut Thierry,

Pour la suppression des lignes, j’ai oublié que le code tel que je l’avais préparé ne pouvait pas fonctionner correctement et je n’avais malheureusement pas poussé mes tests assez loin.

J’ai maintenant échangé la ligne

For i = 5 To DerLig_Bis

par

For i = DerLig_Bis To 5 Step -1

et ça devrait fonctionner.

Pour ton tri, je te propose de passer par un formulaire (UserForm). J’ai placé dans le fichier ci-joint un modèle. A toi de dire ce qui devrait être modifié. Pour l’instant, tu ne peux que choisir des options ‘Sexe/Age’ et cliquer sur ‘Annuler’.

A te relire.

Bonsoir Yvouille,

C'est bon pour la modif de suppression, merci.

Concernant le tri l'idée me plait bien. Donc est-il possible sur ces 2 zones de critères de mixer les choix pour obtenir les 14 combinaisons possibles (M ou F, les ages seuls, ou le sexe mixé avec les ages) ?

Si oui cela créerait le nouvel état dans un nouvel onglet dès validation par ok ?

J'ai un nouvelle question pour le code de tri final : que penses tu du code suivant ?

Range("b4").Select

Selection.Sort Key1:=Range("b4"), Order1:=xlAscending, Header:=xlYes

Il est quelque peu différent du tien, mais peut-être sais tu me dire si je me fourvoie ou s'il y a une raison technique à préférer le tien ?

Merci par avance de ton retour.

Cordialement.

Thierry

Re-bonjour,

Dans le fichier ci-joint, j’ai combiné la macro qui exporte les données nouvelles avec le tri Sexe/Age sur la Feui2.

A la fin de la macro, tu as une Feuil1 actualisée par rapport au fichier ‘Nouvelle Liste’ et – visible à l’écran – une Feuil2 avec seulement les données choisies.

Si une Feuil2 existe au départ, elle est tout d’abord supprimée.

En ce qui concerne ta proposition de modification du tri, je dois te dire que pour la majeure partie des instructions VBA – tout comme lorsque l’on travaille avec des formules Excel – il y a 36 variantes possibles.

Mon code passe par une variable et contient deux lignes, est-ce mieux que ton code qui en contient 3 ??

Mais mon code peut-être réduit à une seule ligne sans variable, donc il est incontestablement mieux que le tien (Regarde dans le code du fichier ci-joint, ma variante de tri 3).

Ah zut, je viens de me rendre compte que ta proposition à 3 lignes peut être réduite à 1 seule ligne également (Variante 4), donc on est à égalité …… enfin pas tout à fait, puisque tu utilises moins de caractères que moi. Bravo, c’est toi qui as gagné …… mais mon code fonctionne quand même

Amicalement.

Attention

Variante 5 remplacée par la Variante 6 (modifications au niveau des bordures)

Bonjour Yvouille,

Merci de cette nouvelle version, ça touche au but.

Ne serait-il pas possible de mettre dans la Feuil1 un bouton qui crée un nouvel onglet dans lequel se trouverait le fameux bouton "Import et tri" ? L'avantage serait de conserver en mémoire, dans le nouvel onglet, les checkbox activées (est-ce possible ?) afin que la mise à jour corresponde toujours au choix initial.

Chaque appui sur le bouton de la Feuil1 génère un nouvel onglet dans lequel on choisirait les checkbox souhaitées.

Suis-je clair ?

Merci

Thierry

Non, je ne vois pas très bien ce que tu veux dire.

Ma macro actuelle te permet d’actualiser ta Feuil1 par rapport au fichier ‘Nouvelle Liste’ puis elle te demande quelles données tu désires sur la Feuil2.

Est-ce que la première partie ci-dessus - qui correspondait au tout premier bouton mis en place - ne te convient plus ? Moi il me semble quand même que cette actualisation par rapport à cet autre fichier doit rester, non ?

Ensuite je t’ai proposé d’effectuer immédiatement sur la Feuil2 une sélection par rapport à cette Feuil1 actualisée ; sélection à définir à chaque fois. Est-ce cette partie qui ne te convient pas ?

Si tu le désires, on peut laisser un bouton en place sur la Feuil1 pour l’actualisation par rapport au fichier ‘Nouvelle Liste’ et que plusieurs sélections se fassent automatiquement sur plusieurs feuilles selon des critères définis (par exemple sur Feuil2, toutes les femmes, sur Feuil3 tous les hommes, sur Feuil4 tous les participants de 20 à 30 ans, etc.).

Ou est-ce que tu désires prédéfinir des critères de sélection (par exemple Choix 1 = toutes les femmes, Choix 2 = tous les hommes, Choix 3 = tous les participants de 20 à 30 ans, etc.) et de ne faire apparaitre sur la Feuil2 que les noms correspondants au Choix choisi ?

Si ce n’est rien de tout cela, essaie de décrire étape par étape ce qui te convient déjà, ce que tu désirerais en plus, etc.

A te relire.

Bonsoir Yvouille,

En fait je reste sur le principe d'actualisation via le bouton initial.

Je te joins un fichier simulant mon idée.

Dans l'onglet "Base", l'extraction se fait sur l'ensemble du fichier (sans aucun critère) comme ton fichier original par un clic sur le bouton "Import".

Le bouton "New" de l'onglet "Base" génère un nouvel onglet (dans mon fichier via un copier-coller de 'onglet masqué "Modele").

Dans ce nouvel onglet il y a un bouton "Critères" qui ouvre ton Userform dans lequel on choisit les critères Checkbox.

Par contre il faudrait pouvoir renommer l'onglet créé (via le Userform) afin que cela soit explicite (dans mon cas il s'appelle Modele(2) ).

Il faudrait aussi que les Checkbox choisies soient gardées en mémoire tant que l'utilisateur ne les a pas modifiées via un nouveau clic sur le bouton "Critères".

Suis-je plus clair ?

Thierry

Je n’arrive toujours pas à comprendre exactement ton souhait, mais peut-être n’es-tu pas si clair.

D’abord tu ne réponds pas explicitement aux questions que je me donne la peine de te poser. Je pense donc que l’actualisation par rapport au fichier ‘Nouvelle Liste’ – à l’aide du bouton ‘Import’ - te convient et que la suite non.

Maintenant, plutôt que de m’expliquer quelles sont les solutions que tu envisages, tu ferais peut-être mieux de m’indiquer quels sont tes attentes finales, quels sont les buts de ce que tu désires.

J’ai quand même tenté quelque chose dans le fichier ci-joint. Si tu cliques sur le bouton ‘Nouvelles feuilles’, tu peux créer autant de feuille que tu désires – sur la base des données de la feuille ‘Base’ telle qu’elle est à ce moment-là – en nommant à chaque fois les feuilles comme tu le désires. Les noms des onglets des nouvelles feuilles créées sont visibles en bas, mais l’écran reste sur la feuille 'Base' et ton UserForm reste ouvert tant que tu n’as pas fini de créer les feuilles désirées. Seul le nom de la dernière feuille indiqué disparait du UserForm après que la nouvelle feuille ait été créée, les autres données (les critères) restent en place.

Si ce n’est pas ça, comme je te l’ai déjà dit, indique-moi ce qui fonctionne déjà bien, ce qui est inutile ainsi que ce qui manque mais sans spécialement suggérer de solution ; concentre-toi sur l’explication du résultat attendu.

A te relire.

Bonjour,

Pour moi ce qui est ok c'est :

- l'import dans le tableau de la feuille Base

- la création de Nouvelles Feuilles avec le Userform qui gère les choix des Checkbox et renomme la feuille.

Sur le principe de cette V7 il faudrait donc que lors de la mise à jour du fichier Nouvelle_Liste.xlsx (ajout, suppression ou mise à jour de lignes) et après appui sur le bouton Import il y ait :

- la mise à jour du tableau de l'onglet Base (ça fonctionne déjà)

- la mise à jour des tableaux des Nouvelles Feuilles créées-renommées en ayant conservé en mémoire les Checkbox sélectionnées à la création.

Suis-je plus clair ?

Thierry

Salut,

Par cette nouvelle demande, tu me permets d’écrire mon 5’555ème message

Admettons que tu aies créé un certain nombre de feuilles avec des critères bien précis (appelons-les les ‘Feuilles à critères’, comme par exemple une feuille nommée ‘Femmes de 31 ans’, etc.), que tu réactualises quelques jours plus tard la feuille ‘Base’ et que les feuilles à critères déjà existantes soient également réactualisées, vas-tu sans cesse créer de nouvelles feuilles à critères et en supprimer d'autres dans le futur ou est-ce que celles-ci seront définitives au bout d’un moment ?

A te relire.

Re,

5555 d'aide ? Chapeau bas !

En fait le principe étant de partager le fichier de synthèse entre plusieurs utilisateurs chacun créera ses propres "feuilles à critères".

Donc ces dernières deviendront définitives mais de façon personnelle pour chaque utilisateur.

Lorsque j'enverrai la mise à jour de l'extraction de la nouvelle liste, chacun pourra mettre à jour son fichier avec leurs propres "feuilles à critères".

Thierry