Générer des courriers types word à partir d'excel

[loulouange]

Bonsoir,

Je me permets de faire appel à vos compétences en Excel afin de savoir s'il est possible de créer dans un tableau excel comprenant une liste de personnes avec coordonnées, un bouton ou autre permettant d'ouvrir un document spécifique dans Word reprenant des éléments dans le tableau excel.

Etant donné que se sont des lettres personnalisées, il n'y pas de possibilité de faire un publipostage.

Jusqu'ici je n'ai pu qu'améliorer les lettres types en appliquant des champs, (je n'y connaissais rien avant).

Je sais qu'Excel propose de grandes possibilités mais je crains que ce que je demande nécessite une très bonne maîtrise d'Excel.

Ceci dit, si vous pensez que ma demande est réalisable, et s'il existe un tuto pas à pas ou si vous pouviez m'expliquer au moins pour un exemple, je pourrais tenter de comprendre le fonctionnement des macros.

Pour vous permettre de mieux cerner ma demande, je vous mets en pièce jointe un tableau et deux exemples de lettres types, une pour absence injustifiée (AI) et l'autre pour absence justifiée (AJ).

Le tableau est exhaustif, d'autres lettres types et documents existent, le but étant de pouvoir en cliquant sur un bouton (dans chaque colonne sur chaque ligne ??) de générer le document et de reprendre l'adresse du destinataire.

Je vous remercie par avance pour vos suggestions et conseils qui me seront bien précieux.

 

[loulouange]

Re : Générer des courriers types word à partir d'excel

Bonsoir Martial,

Merci infiniment pour le gros travail que tu as fait !! Je n'ai pas le temps tout regarder ce soir dans le détail, j'ai vu donc que lorsque l'on clique sur une des feuilles des conseillers, une mise à jour se fait à chaque fois c'est ça ?

Je vais faire des essais pour mieux comprendre le fonctionnement et reviendrais te voi.

J'avais une tite question tout de même.

J'ai essayé de faire un tableau test, avec deux feuilles, une feuille active donc et la seconde qui reprend les noms et prénoms avec d'autres colonnes à remplir au fur et à mesure.

J'ai eu beau comparer tes différentes versions à savoir :

je n'ai pas réussi à trouver comment tu as fait pour résoudre le problème concernant l'histoire des lignes recopiées dans les feuilles des CT : les données qui se trouvaient à la suite de celles recopiées étaient décalées
par ex : si on met un croix dans la cellule L à la ligne 10 et qu'une ligne s'insère avec la macro tri dans le tableau et se positionne au dessus, la croix ne suit pas et du coup se retrouve avec celle qui vient d'etre rajoutée.

Voila si tu pouvais m'éclairer sur la question ça me serait d'une grande aide.

Encore merci.

Bonne soirée.

Loulou

 

[Yaloo]

Re : Générer des courriers types word à partir d'excel

Bonjour Loulou,

Pour les feuilles Conseiller, toutes les données sont rapatriées lors de l'activation de la feuille. Donc nous récupérons les coordonnées du Stagiaire ainsi que les valeurs se trouvant dans les colonnes de I à R dans la feuille Base. Ensuite le tableau complet est trié.

A+

 

[loulouange]

Re : Générer des courriers types word à partir d'excel

Bonjour Martial,

J'ai pris le temps de regarder ton fichier et tes explications, et revu tous les posts (ça en fait !!) pour bien comprendre et éviter de te reposer les mêmes questions

C'est incroyable ce que l'on peut faire avec excel, vraiment chapeau pour ton boulot, tu as du y passer du temps ça me touche beaucoup.

Donc dès que l'on fait une modification dans n'importe quelle feuille, tous les onglets se mettent à jour.
Bonne idée de mettre un ID pour chaque stagiaire, surtout que certains stagiaires reviennent et ne sont pas forcément dans la même action.

En ce qui concerne la feuille que tu as rajouté "Base", j'essaye de comprendre son utilité :

--> tu as fait une macro qui regroupe les données de la feuille générale + celles des CT.

As-tu crée la feuille "Base" pour pouvoir faire en sorte que la mise à jour de toutes les feuilles se fassent automatiquement lorsqu'il y a une modification ?

Est ce une passerelle ? j'essaye de comprendre et j'ai du mal à m'exprimer lol
Je veux dire quand on modifie une ligne sur une des feuilles (générale ou CT) est ce que dans un premier temps le changement se fait dans la feuille "Base" puis ensuite va se répercuter dans les autres feuilles ?

Pour répondre à ta question sur les dates des bilans, échéances etc... , se sont bien des données différentes

Bon je reviens encore avec ma question que j'avais posée l'autre jour, j'ai passé l'après midi à comprendre mais j'ai vrament du mal.

C'est au sujet des colonnes qui suivent à la suite de celles qui sont recopiées dans les feuilles CT.
Il y avait un décalage. Dans ton dernier fichier tu avais résolu le problème (fichier ci-joint "données sources essais (1)).

J'ai essayé de faire un tableau en me basant sur ton modèle, mais là pas de feuille par CT, toutes les données sont recopiées dans une seule feuille.
Apparemment j'ai réussi à faire ce transfert, mais le problème de ligne décalée est revenu :

Dans la feuille "Affectation" les données sont recopiées jusqu'à la colonne I. ensuite les colonnes qui suivent sont remplies sans macro.
Ici par exemple dans la colonne J il y a des croix et si un stagiaire se rajoute au dessus du dernier de la iiste : "WILLIAM", la croix ne suit pas et va re retrouver en face du nouveau stagiaire.

Je n'arrive pas à identifier les lignes dans la macro par rapport au changement que tu avais fait ,à savoir :

1er action : on recopie les données dans la feuille concernée
2eme action : on fait ensuite le tri alphabétique sur le tout le tableau


J'ai rajouté une feuille "Sept-2013", je voulais reporter dans les colonnes A B et C le "CT" "NOM" et "Prénom" mais je n'y suis pas parvenu, et de plus cela bloquait la macro de la feuille"Affectation" alors j'ai préféré laissé.

Voila j'espère que tu auras encore un peu de temps et de patience à m'accorder.

Ci-joint mon fichier, et celui que tu m'avais envoyée la dernière fois.

Au plaisir de te lire.

 

[Yaloo]

Re : Générer des courriers types word à partir d'excel

Bonjour Loulou,

Donc dès que l'on fait une modification dans n'importe quelle feuille, tous les onglets se mettent à jour.
Bonne idée de mettre un ID pour chaque stagiaire, surtout que certains stagiaires reviennent et ne sont pas forcément dans la même action.

En ce qui concerne la feuille que tu as rajouté "Base", j'essaye de comprendre son utilité :

--> tu as fait une macro qui regroupe les données de la feuille générale + celles des CT.

As-tu crée la feuille "Base" pour pouvoir faire en sorte que la mise à jour de toutes les feuilles se fassent automatiquement lorsqu'il y a une modification ?

Oui, toutes tes données sont stockées dans cette feuille, ensuite tu fais ton marché , pour tel type de feuille, tu récupères certaines données, pour tel autre, tu en récupères d'autres.

Est ce une passerelle ? j'essaye de comprendre et j'ai du mal à m'exprimer lol
Je veux dire quand on modifie une ligne sur une des feuilles (générale ou CT) est ce que dans un premier temps le changement se fait dans la feuille "Base" puis ensuite va se répercuter dans les autres feuilles ?

Les choses se font en 2 temps :
1 - Lorsque tu modifies ou ajoutes une donnée dans une feuille, celle-ci est reportées dans la feuille Base.
2 - Lorsque tu accèdes à une feuille, les données sont "piochées" dans la feuille Base pour être restituées dans ta feuille active.

Bon je reviens encore avec ma question que j'avais posée l'autre jour, j'ai passé l'après midi à comprendre mais j'ai vrament du mal.

C'est au sujet des colonnes qui suivent à la suite de celles qui sont recopiées dans les feuilles CT.
Il y avait un décalage. Dans ton dernier fichier tu avais résolu le problème (fichier ci-joint "données sources essais (1)).

C'était le but, puisqu'à chaque activation de feuille Conseiller tout est repris sur la feuille Base.

Dans la feuille "Affectation" les données sont recopiées jusqu'à la colonne I. ensuite les colonnes qui suivent sont remplies sans macro.
Ici par exemple dans la colonne J il y a des croix et si un stagiaire se rajoute au dessus du dernier de la iiste : "WILLIAM", la croix ne suit pas et va re retrouver en face du nouveau stagiaire.

Je n'arrive pas à identifier les lignes dans la macro par rapport au changement que tu avais fait ,à savoir :

1er action : on recopie les données dans la feuille concernée
2eme action : on fait ensuite le tri alphabétique sur le tout le tableau

Il faudrait que tu m'explique exactement ce que tu souhaites faire.
Tu pars de quelle feuille, tu copies quoi ? Dans quelle feuille ? A quel endroit ? etc...

Ci-joint mon fichier, et celui que tu m'avais envoyée la dernière fois.

Pourquoi celui-ci, le fichier avec les identifiants et la base ne fonctionne pas ?

A te relire

Martial

 

[loulouange]

Re : Générer des courriers types word à partir d'excel

Coucou Martial,

Ton fichier avec identifiant et la feuille base est nickel !! je vais le garder précieusement il est vraiment tip top !

j'ai donc apparemment bien saisi le principe, sans la feuille "BASE" que tu as créée on ne pourrait pas faire ces mises à jour automatique instantanément ?

Pour ce qui concerne mon autre question (c'est le fichier "suivi ateliers2") : cela concerne une autre action, avec des personnes et données différentes , je voulais comprendre comment tu avais fait pour éviter que les lignes soient décalées.
Je voulais donc partir d'un exemple plus "simple" comme le fichier que tu m'avais envoyé avec la correction de cette erreur, et celui que j'ai crée, j'aimerais comprendre ce que tu as changé dans la formule, afin que je puisse si besoin refaire un tableau dans le même style.

Je pars donc de la feuille Acrive nommée "Liste participants" et je voudrais recopier certaines données dans la feuille "Affectation" puis d'autres données dans "Sept-2013"

Mais je répète celui avec les identifiants est super et franchement je n'espérais pas un résultat aussi sophistiqué, encore merci
pour ce gros travail.

Voila j'espère que j'ai été assez claire et que tu pourras m'aider.

Bonne soirée.

Loulou

 

[Yaloo]

Re : Générer des courriers types word à partir d'excel

Bonjour Loulou,

A mon sens, il faut mieux passer par une feuille Base, je pense que c'est plus simple à gérer.

Veux-tu que j'y regarde ?

A+

 

[loulouange]

Re : Générer des courriers types word à partir d'excel

Bonjour Martial,

J'avais l'impression qu'avec ton système incluant la feuille "Base", la macro était plus complexe mais si tu penses que c'est mieux je veux bien que tu tentes quelque chose sur mon fichier "ateliers2".

Toutefois, j'aurais bien voulu comprendre mon erreur, et savoir pourquoi je n'ai pas réussi à faire en sorte que le tri se fasse sur toute la ligne et colonnes en respectant l'ordre des données qui suivaient (les croix) ?

Au plaisir de te lire.

Loulou

 

[Yaloo]

Re : Générer des courriers types word à partir d'excel

Re,

Tu n'as pas fait d'erreur, mais dans la macro de transfert (d'une feuille à l'autre) on commence par supprimer les données de la feuille2 jusqu'à la colonne F (donc on conserve les x) puis on colle ligne par ligne les nouvelles valeurs, mais ces valeurs ne se retrouvent pas forcément dans l'ordre initial de la feuille 2, donc lorsque l'on trie les x sont décalés.

On pourrait presque y arriver en contrôlant toutes les cellules de la ligne de la feuille 1 pour vérifier si elle existe déjà dans la feuille 2 et ne copier (dans la première ligne vide) que les lignes n'existant pas dans la feuille 2. Mais ce serait plus long et surtout problématique si une des cellules étaient modifiées, car cela recréerait une ligne déjà existante.

Je pense réellement que le système avec la feuille Base est LA solution.

J'y regarde au plus tôt.

A+

PS : Tu n'aura que ces 4 feuilles, ou tu vas en ajouter d'autres ?

 

[loulouange]

Re : Générer des courriers types word à partir d'excel

Pour le fichier "suiviateliers2", je vais devoir créer une feuille par mois donc de sept à déc.

Autrement, si je fais une modification, par exemple si je supprime une ligne dans les feuilles correspondant au mois il ne faut pas que cela se répercuté sur les autres feuilles.

 

[Yaloo]

Re : Générer des courriers types word à partir d'excel

Juste une petite idée comme ça, dans ta feuille Affectation au lieu de mettre des x, tu mets ton N° de semaine, tes données peuvent être envoyées directement dans la bonne semaine.

Dans le même temps, faire une feuille à l'année ou au semestre, et mettre des N° de semaines, lorsque tu actives ta feuille cela te cale à ton N° de semaine.

Ce n'est qu'une idée, je ne sais pas comment tu travailles ....

 

[Yaloo]

Re : Générer des courriers types word à partir d'excel

Voici le fichier avec ce que je te disais au post précédent.

A savoir, à l'activation ou à la modification des 2 premières feuilles, celles-ci se mettent à jour ou mettent à jour la feuille Base.

J'ai créé une feuille Suivi qui contient toute l'année (plutôt qu'avec un seul mois), lorsque tu l'actives, la macro récupère les données de la feuilles Base et les insères dans cette feuille, en plaçant des X en fonction de la date et de l'heure du RV saisie auparavant dans la feuille Affectation. La macro vient positionner la feuille en fonction du N° de semaine actuel.

J'en ai profité pour rajouter certaines données dans la feuille Menu Déroulant.

En espérant que cela va dans le bon sens

A te relire

Martial

 

[loulouange]

Re : Générer des courriers types word à partir d'excel

Bonjour Martial,

Quelle rapidité, même pas eu le temps de te répondre !

Mais excel n'a aucun secret pour toi !! je suis impressionnée de tout ce que tu arrives à faire.

J'ai regardé ton fichier, la mise en place de formules pour avoir les semaines et jours automatiquement est géniale ! J'ai vu que pour cela tu as rajouté des infos dans les menus déroulants.


Une tite question mais c'est un détail, comment fais tu pour que par exemple dans le tableau de la feuille "Suivi" :

- dans la cellule D3 où se trouve la formule "=nosemaine('Menu Déroulant'!F3)" le résultat qui est dans ce cas "1" est bien centré alors que tu n'as pas fusionné les cellules de D3 à G3 ?

Voila comment est géré le classeur :

Dans la feuille "Affectation" : seuls les conseillers la complète.
Chaque stagiaire doivent chaque semaine participer à 3 ateliers, généralement se sont toujours les memes.
Donc les conseillers renseignent par des croix les personnes affectées sur tel ou tel atelier.
Une fois par semaine, ils vérifient que la liste est à jour, car elle peut etre modifiée dans le cas d'un nouveau stagiaire ou d'un départ. Et aussi d'un changement d'atelier.

Dans la feuille "Suivi" : c'est moi qui gère cette partie :
Partons de la première semaine 1 Je vais faire un copier-coller (ou par macro) des colonnes K à U se trouvant dans la feuille Affectation.
Chaque jour je dois noter dans les cases les personnes qui ont été présentes ou absentes, donc je remplace les croix par des cases "P" (présent) ou "A"(absent). Ceci pour ensuite savoir qui doit être relancé suite à absence.

C'est pour cela que si une info change dans les autres feuilles cela ne doit pas se répercuter dans celle-ci. Je dois garder l'historique sur toute l'année des présences et absences.

Re,

Tu n'as pas fait d'erreur, mais dans la macro de transfert (d'une feuille à l'autre) on commence par supprimer les données de la feuille2 jusqu'à la colonne F (donc on conserve les x) puis on colle ligne par ligne les nouvelles valeurs, mais ces valeurs ne se retrouvent pas forcément dans l'ordre initial de la feuille 2, donc lorsque l'on trie les x sont décalés.

On pourrait presque y arriver en contrôlant toutes les cellules de la ligne de la feuille 1 pour vérifier si elle existe déjà dans la feuille 2 et ne copier (dans la première ligne vide) que les lignes n'existant pas dans la feuille 2. Mais ce serait plus long et surtout problématique si une des cellules étaient modifiées, car cela recréerait une ligne déjà existante.

Je pense réellement que le système avec la feuille Base est LA solution.

Excuse moi de revenir à la charge mais je suis restée un peu sur ma faim
J'aimerais vraiment comprendre comment tu y est arrivé, car dans le dernier fichier que tu m'avais envoyé, tu n'avais pas encore opté pour la feuille "Base" et pourtant ta macro fonctionne et il n'y pas de problème de colonne décalée par rapport à la ligne. Je te remets le fichier en question j'ai rajouté "tri ok" dans le nom pour éviter toute confusion.

Désolée d'insister mais ça me travaille trop !!

Merci pour ton aide précieuse.

A te relire.

Loulou

 

[Yaloo]

Re : Générer des courriers types word à partir d'excel

Bonjour Loulou,

Une tite question mais c'est un détail, comment fais tu pour que par exemple dans le tableau de la feuille "Suivi" :

- dans la cellule D3 où se trouve la formule "=nosemaine('Menu Déroulant'!F3)" le résultat qui est dans ce cas "1" est bien centré alors que tu n'as pas fusionné les cellules de D3 à G3 ?

Les cellules fusionnées mettent pas mal la pagaille dans les fichiers avec macros, donc moins j'utilise de cellules fusionnées et mieux je me porte. Pour arriver au résultat d'un centrage sur plusieurs colonnes, tu sélectionnes D3:G3 tu clic-droit, "Format de cellule", l'onglet "Alignement" et dans alignement du texte : horizontal tu sélectionnes "Centrer sur plusieurs colonnes" puis OK.

C'est pour cela que si une info change dans les autres feuilles cela ne doit pas se répercuter dans celle-ci. Je dois garder l'historique sur toute l'année des présences et absences.

Ok, c'est bien ce qui me semblait après avoir posté le fichier, il ne faut pas insérer de données directement, c'est à toi de le faire. Il suffit de supprimer la macro Worksheet_Activate de la feuille Suivi.

Excuse moi de revenir à la charge mais je suis restée un peu sur ma faim
J'aimerais vraiment comprendre comment tu y est arrivé, car dans le dernier fichier que tu m'avais envoyé, tu n'avais pas encore opté pour la feuille "Base" et pourtant ta macro fonctionne et il n'y pas de problème de colonne décalée par rapport à la ligne. Je te remets le fichier en question j'ai rajouté "tri ok" dans le nom pour éviter toute confusion.

Désolée d'insister mais ça me travaille trop !!

Ne t'excuses pas de demander , lors de l'envoi de données, je ne supprimais pas les données de la feuille Conseiller, mais j'ajoutais la ligne modifiée à la fin de la feuille Conseiller, avec DerL. Ensuite je faisais le tri, comme la dernière ligne ajoutée se trouvait en dernier, les x étaient trier en même temps que les données de la même ligne.

A+

Martial

 

[Yaloo]

Re : Générer des courriers types word à partir d'excel

Re,

Tu peux supprimer une partie de la macro, mais conserver quand même le calage sur la semaine. Mais il vaut mieux modifier la igne comme ça :

Application.Goto Cells(1, Application.Match(Sheets("Menu Déroulant").[H3] - 1, Plage, 0)), -1

avec la ligne ci-dessus, le planning est calé à la semaine précédente celle en cours.

A+

 

[loulouange]

Re : Générer des courriers types word à partir d'excel

Bonjour Martial,

Merci pour tes explications.

Je n'avais jamais pensé à utiliser la mise en forme du centrage sur plusieurs colonnes, j'ai encore beaucoup de choses à découvrir...

Bonjour Loulou,
Ne t'excuses pas de demander , lors de l'envoi de données, je ne supprimais pas les données de la feuille Conseiller, mais j'ajoutais la ligne modifiée à la fin de la feuille Conseiller, avec DerL. Ensuite je faisais le tri, comme la dernière ligne ajoutée se trouvait en dernier, les x étaient trier en même temps que les données de la même ligne.

Alors j'ai essayé de corriger mon erreur mais je suis perdue j'ai changé tout ce que j'ai pu par rapport à ton modèle mais ça plante la macro.

Voici ci-dessous la macro que j'ai et qui fonctionne à part l'histoire de ce fameux tri qui ne se fait pas dans l'ordre :


Option Explicit

Private Sub CommandButton1_Click()
UserForm1.Show
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
Dim CT$, i&, DerL&
'Si le N° de la colonne de la cellule active est supérieur à 8 alors on sort
If Target.Column > 8 Then Exit Sub
'Pour i de 2 jusqu'au nb de feuille
'*** Sheets.Count - 1 car tu as rajouté une feuille en dernière position.
For i = 2 To Sheets.Count - 2
'Pour chaque feuille N° i
'On cherche la dernière cellule pleine de la colonne A
DerL = Sheets(i).[B65536].End(xlUp).Row
'Si la dernière ligne est inférieure à 3, la feuille est vide donc on passe à la feuille suivante
If DerL < 3 Then GoTo Suite
'On efface les données de la feuille
Sheets(i).Range("A3:F" & DerL).ClearContents
Suite:
'On passe à la feuille suivante
Next
'Pour toutes les lignes de 3 jusqu'à la fin de la feuille active (général)
For i = 3 To [C65536].End(xlUp).Row
If Cells(i, 1) <> "" Then
'On récupère le nom du conseiller
CT = Cells(i, 1)
'On copie les données de la ligne i dans la feuille du conseiller dans la première ligne vide
DerL = Sheets("Affectation").[B65536].End(xlUp)(2).Row
Range(Cells(i, 1), Cells(i, 4)).Copy Sheets("Affectation").Cells(DerL, 1)
Cells(i, 9).Copy Sheets("Affectation").Cells(DerL, 5)
Cells(i, 10).Copy Sheets("Affectation").Cells(DerL, 7)
Cells(i, 11).Copy Sheets("Affectation").Cells(DerL, 8)
Cells(i, 13).Copy Sheets("Affectation").Cells(DerL, 9)
Cells(i, 20).Copy Sheets("Affectation").Cells(DerL, 6)
End If
'On passe à la ligne suivante
Next
'On lance la macro Tri, du module1
Tri
End Sub


Peux tu me dire où se trouvent mes erreurs dans cette macro ? je te mets ici le fichier pour que tu puisses mieux cerner le problème.


Pour ton dernier fichier avec Worksheet, j'ai tenté de faire comme tu m'as dit j'ai supprimé tout ce qu'il y avait dans le code de la feuille "suivi" et apparemment ça n'affiche plus les données comme auparavant.
J'ai voulu ensuite rajouter ta ligne mais là encore j'ai vraiment du mal pfff y a toujours une message d'erreur : dans la macro "END WITH" se met en surbrillance :


Private Sub Worksheet_Activate()
Dim i&, j&, c&, SemS&, Plage, SemB&
Application.DisplayAlerts = 0: Application.EnableEvents = 0
End With
Application.Goto Cells(1, Application.Match(Sheets("Menu Déroulant").[H3] - 1, Plage, 0)), -1
Application.DisplayAlerts = -1: Application.EnableEvents = -1
End Sub

Je crois vraiment que j'ai un problème de compréhension au niveau des étapes de la macro, je ne sais pas quand mettre les End Sub, Next, IF etc... dans l’exécution j'arrive à comprendre la démarche mais c'est comment passer d'une étape à une autre.


Au plaisir de te lire

Loulou