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

Bonjour Martial,

Tout d’abord je te remercie pour ton indulgence et pour tout l’intérêt que tu portes à mon projet.

J’ai encore quelques questions à te poser si tu veux bien.

Tu avais souligné dans un de tes précédents posts :

PS : En ajoutant une feuille à la fin du classeur, il fallait modifier les macros d'envoi de données et de tri.

J’ai regardé ton code et donc j’ai vu que tu avais rajouté dans le module « tri » -1 à la fin de la ligne :

For i = 2 To Sheets.Count – 1

Donc si je comprendre bien si je rajoute une autre colonne, ce qui fait du coup 2 colonnes après celles des CT il faudra que je mette «-2 » ?

Le Sheets(i) reprend le N° de chaque feuille de 2 jusqu'à l'avant-denière feuille. Comme tu n'as pas de civilité dans les feuilles CB EL et AM, il faut effacer de la colonne A jusqu'à la colonne H.

Je suis un peu perdue car je ne sais plus si "A3:H" concerne les colonnes de la feuille générale ou celles des conseillers.
Je pense que cela concerne les feuilles des CT, dans ce cas là pourquoi aller jusqu’à la colonne H alors que les données sont recopiées jusqu’à la colonne G dans les feuilles des CT ?

PS : je te remets les 3 fichiers, car j'ai rajouté le signet8 dans les fichiers Word.

Super, j’ai du coup rajouté le signet civilité qui manquait en début de la lettre (pas dans l’adresse) j’ai donc en tout 9 signets à présent.

J’ai tenté de mettre un code dans word pour l’accord avec les adjectifs comme je l’avais fait dans l’ancien modèle comme suit :

{IF {REF Civilité} = "Monsieur" "orienté" "orientée"} (voir dans les lettres en faisant ALT+F9)

Et j’ai remplacé par : {IF {REF Signet1} = "Monsieur" "orienté" "orientée"}

Mais ça ne fonctionne pas, je pense qu’il doit y a voir un moyen non ?


Pour en revenir au tableau, je rencontre encore des dysfonctionnements :

Au niveau de la mise en forme conditionnelle qui colorise les cellules selon le choix dans la liste déroulante.

J’ai du m’y reprendre à plusieurs fois car ça ne respectait pas les cellules concernées, j’ai remarqué que d'autres formats s'étaient rajoutés dans le tableau « mise en forme conditionnelle », j’ai tout effacé et tout repris dans la feuille CB.
J’ai fait un copier coller des listes déroulantes dans les feuilles EL et EM, mais cela ne colorise qu’une cellule, du coup j’ai tout effacé dans la feuille EL, je n’ai rien fait dans la feuille EM.
Et lorsque je vais dans le tableau mise en forme conditionnelle j’ai à nouveau une longue liste de format de cellules.

Peut on recopier une mise en forme prédéfinie vers une autre feuille sans etre obligé de tout ressaisir ?


Je reviens sur ce que j’ai dit plus haut :

Je pense que cela concerne les feuilles des CT, dans ce cas là pourquoi aller jusqu’à la colonne H alors que les données sont recopiées jusqu’à la colonne G dans les feuilles des CT ?

En faisant d’autres tests, j’ai remarqué dans la feuille CB, une fois que le stagiaire est rajouté et que je rempli à la suite les autres colonnes, et notamment la colonne H qui correspond à l’atelier n°1 , Si une ligne se rajoute au dessus de celle-ci, la croix disparait, donc j’ai modifié dans le code et j’ai mis "A3:G".

J’ai aussi le problème avec la formule que j’ai mise dans la colonne F « Bilan date échéance », car idem lorsqu’une ligne se rajoute elle s’efface.

J'ai voulu protéger ces données en verrouillant cette colonne mais ensuite j'ai eu un message d'erreur dans la macro. j'ai donc annuler cette manip.


Je te remets ici le fichier excel.

Au plaisir de te lire.

Loulou

 

[loulouange]

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

Je reviens avec de nouvelles modifications, suite à l'erreur concernant les formules dans la colonne F "bilan date échéance" des CT

J'ai interverti les colonnes pour mettre cette dernière à la fin, donc pas prise en compte dans la plage citée dans le code vba

"A3:G" devient "A3:F" (Les formules sont à présent dans la colonne G).

J'ai fait le test et je n'ai plus le problème de formule qui s'efface. Par contre toutes les colonnes qui sont renseignées à la suite de la colonne F donc de H à Q ne suivent pas si la ligne du stagiaire est déplacée selon l'ordre alphabétique.

je te remets donc la dernière version du fichier excel.

 

[Yaloo]

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

Bonsoir Loulou,

J'y regarde quand je rentre de vacances.

A+

Martial

 

[Yaloo]

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

J’ai regardé ton code et donc j’ai vu que tu avais rajouté dans le module « tri » -1 à la fin de la ligne :

For i = 2 To Sheets.Count – 1

Donc si je comprendre bien si je rajoute une autre colonne, ce qui fait du coup 2 colonnes après celles des CT il faudra que je mette «-2 » ?

Tu veux dire 2 feuilles et non colonne ?

Je suis un peu perdue car je ne sais plus si "A3:H" concerne les colonnes de la feuille générale ou celles des conseillers.
Je pense que cela concerne les feuilles des CT, dans ce cas là pourquoi aller jusqu’à la colonne H alors que les données sont recopiées jusqu’à la colonne G dans les feuilles des CT ?

"A3:H" doit correspondre aux feuilles des conseillers.

Super, j’ai du coup rajouté le signet civilité qui manquait en début de la lettre (pas dans l’adresse) j’ai donc en tout 9 signets à présent.

J’ai tenté de mettre un code dans word pour l’accord avec les adjectifs comme je l’avais fait dans l’ancien modèle comme suit :

{IF {REF Civilité} = "Monsieur" "orienté" "orientée"} (voir dans les lettres en faisant ALT+F9)

Et j’ai remplacé par : {IF {REF Signet1} = "Monsieur" "orienté" "orientée"}

Mais ça ne fonctionne pas, je pense qu’il doit y a voir un moyen non ?

Avec Word je ne sais pas si l'on peut faire ce que tu décris ci-dessus, par contre au post#18 j'avais émis une hypothèse, en ajoutant un signet à la fin du mot à accorder.

En faisant d’autres tests, j’ai remarqué dans la feuille CB, une fois que le stagiaire est rajouté et que je rempli à la suite les autres colonnes, et notamment la colonne H qui correspond à l’atelier n°1 , Si une ligne se rajoute au dessus de celle-ci, la croix disparait, donc j’ai modifié dans le code et j’ai mis "A3:G".

Au post#22 je parlais de ce genre de problème. Il faut peut-être travailler autrement, ne pas supprimer les données mais lorsque l'on cherche les données pour chaque conseiller, il faut vérifier si cette ligne existe, si tel n'est pas le cas dans ce cas-là on la rajoute à la fin puis on lance le tri.

Dis-moi ce que tu en pense.

A te relire

Martial

 

[loulouange]

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

Bonjour Martial, j'espère que tes vacances ont été bonnes

Pour ta première question en effet je voulais parler de feuille et non de colonne désolée.

Je me souviens de ton post#18 dans lequel tu me parlais de cette formule à mettre dans la macro, je pensais qu'en passant par word cela serai plus simple, si bien sur cela est possible.
Donc pour la macro, il faudra rajouter une ligne avec la fonction "IF colon 2="Monsieur" then ?? (valeur qui prendra la valeur signet 10 sinon 11 dans WORD alors ?

Je viens de relire ton post#22 sur le coup je n'avais compris exactement ce dont tu parlais, je pensais que le tri alphabétique risquait de ne pas se faire dans l'ordre. Là c'est plus clair à présent. Je pense que ta méthode d'ajouter la ligne à la fin du tableau du conseiller et faire le tri ensuite sur toute la ligne pourrait régler le problème.

Merci pour ton aide.

Dans l'attente de te lire, je te souhaite une bonne semaine.

Loulou

 

[Yaloo]

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

Bonsoir Loulou,

Je vois ça au plus tard mercredi, difficile de faire avant.

A+

Martial

 

[Yaloo]

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

Bonjour Loulou,

Je ne t'ai pas oublié, mais j'ai qq problèmes pour modifier ton fichier.

Il y avait plusieurs possibilités envisageable mais elles ne me convenaient pas :

- Soit on agit sur un bouton pour n'envoyer que la dernière ligne, mais si l'on oubli de le faire, alors les données ne sont pas envoyées.
- Soit on envoie les données lorsque toute la ligne est remplie, mais si par exemple il n'y a pas d'"adresse 2" alors les données ne seront jamais envoyées.

Pour finir, j'ai choisi d'envoyer les données que lorsque la colonne A est modifiée. La macro copie les données à la première ligne vide de la feuille du conseiller, puis fait le tri. Cette solution fonctionne, mais si tu modifies une ligne (dans "général") cela ne sera pas modifié dans la feuille du conseiller. Et si tu modifies un élément de la colonne A, cela va renvoyer une nouvelle ligne.
Par exemple, tu as une ligne pour le conseiller CT, dans "général", si tu changes CT par EL, cela va créer une ligne dans EL mais celle de CT ne sera pas enlevée.

A te relire

Martial

 

[Yaloo]

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

Re,

Voici la version avec l'accord de orienté et convoqué. Attention il ne faut pas champ.

A+

Martial

 

[loulouange]

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

Bonsoir,

Merci pour tes fichiers, je vais les étudier et reviens vers toi.

Bonne soirée.

Loulou

 

[loulouange]

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

Bonjour Martial,

J'ai regardé tes documents, j'ai eu un peu de mal au début mais c'est parce que tu as pris une ancienne version mais j'ai pu remettre les codes à jour (ça m'a fait un bon exercice )

Impec pour les signets permettant les accords !

Par contre pour la question du tri, il faudrait refaire comme auparavant, j'en avais parlé dans mon post#26

Par contre, lorsque je remplis la feuille "général" au départ, je ne sais pas quel conseiller va suivre la personne donc la case va rester vide un moment, j'aurais voulu faire le tri alphabétique meme avec la case vide.

J'ai relevé dans le code de la feuille "Général" ceci :

DerL = Sheets(CT).[B65536].End(xlUp)(2).Row

tu as rajouté (2) : à quoi cela correspond il ?

J'ai tenté de comprendre comment tu as fait pour que régler le problème de tri dans les feuilles des conseillers se fasse sur toute la ligne et évite ainsi que le contenu colonnes après la colonne G soit décalé.

J'ai constaté que tu avais modifié cette ligne :

.Range("A2:Q" & .[B65536].End(xlUp).Row).Sort [B2], xlAscending, [C2], , xlAscending, [D2], xlAscending, xlYes

J'ai du mal à comprendre la signification, pourrait tu me mettre une petite explications de cette règle ?

Pour finir, il est vrai que si l'on doit changer une info dans l'onglet "Général" elles ne seront pas mises à jour dans les onglets des conseillers.

En général il est rare que le nom du conseiller change, mais si cela arrivait il faudra être vigilent et bien faire le changement dans les autres onglets.

Ensuite ce qui peut changer se sont les coordonnées du bénéficiaire, les modifications ne se feront que dans l'onglet général. Etant donné que la colonne numéro de tel se trouve aussi dans celles des conseillers je vais la protéger pour être sure que la modification ne puisse se faire que dans l'onglet "général".

Sinon, là ou c'est un peu embêtant c'est lorque le bénéficiaire change de statut "Stade Acgpt" dans la colonne Y de l'onglet général et qui est repris dans les onglets des conseillers dans la colonne E.

Car il faudra le faire dans tous les onglets à chaque fois. Je ne vois pas comment faire autrement.

J'avais une autre question, j'ai remarqué que lorsque je faisait une action sur le tableau et que je me rends compte d'une erreur, je n'ai pas le moyen de revenir en arrière et d'annuler les dernière modifications, est ce normal ?

Je te remets ici tous les fichiers mis à jour.

Dans l'attente de te lire.

Loulou

 

[Yaloo]

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

Bonjour Loulou,

J'ai regardé tes documents, j'ai eu un peu de mal au début mais c'est parce que tu as pris une ancienne version mais j'ai pu remettre les codes à jour (ça m'a fait un bon exercice )

C'est bien, je vais me tromper plus souvent

Par contre pour la question du tri, il faudrait refaire comme auparavant, j'en avais parlé dans mon post#26

Envoyé par loulouange
Par contre, lorsque je remplis la feuille "général" au départ, je ne sais pas quel conseiller va suivre la personne donc la case va rester vide un moment, j'aurais voulu faire le tri alphabétique meme avec la case vide.

Dans ce cas-là, il faudra surement mettre un bouton, car si l'on fait une macro à chaque changement de données, cela risque de ne pas être évident à suivre.

DerL = Sheets(CT).[B65536].End(xlUp)(2).Row

tu as rajouté (2) : à quoi cela correspond il ?

Dans le cas d'une ligne, le (2) décale la valeur vers le bas d'une ligne (donc la première ligne vide)

.Range("A2:Q" & .[B65536].End(xlUp).Row).Sort [B2], xlAscending, [C2], , xlAscending, [D2], xlAscending, xlYes

Le point "." devant Range ou [B65536], permet de faire suite à With Sheets(i), on va donc trier les données de la feuille (i).
Pour sélectionner une plage de valeur, on utilise Range(PremièreCellule : DernièreCellule) : PremièreCellule = A2 ; DernièreCellule va être dans la colonne Q (d'où le :Q"), pour trouver la dernière ligne de la plage, nous cherchons la dernière ligne de la colonne B (celle qui contient les noms, on est sûr que cette cellule est remplie) [B65536].End(xlUp).Row

Ensuite ce qui peut changer se sont les coordonnées du bénéficiaire, les modifications ne se feront que dans l'onglet général. Etant donné que la colonne numéro de tel se trouve aussi dans celles des conseillers je vais la protéger pour être sure que la modification ne puisse se faire que dans l'onglet "général".

Il y a peut-être une solution, mais je n'ai pas le temps de la tester maintenant, ce serait de copier/coller avec liaison. Je testerai plus tard. Cette solution résoudra peut-être ton point N°2.

Sinon, là ou c'est un peu embêtant c'est lorsque le bénéficiaire change de statut "Stade Acgpt" dans la colonne Y de l'onglet général et qui est repris dans les onglets des conseillers dans la colonne E.

Je ne suis pas sûr d'avoir compris mais la solution que je te propose ci-dessus règlera peut-être ton problème.

J'avais une autre question, j'ai remarqué que lorsque je faisait une action sur le tableau et que je me rends compte d'une erreur, je n'ai pas le moyen de revenir en arrière et d'annuler les dernière modifications, est ce normal ?

Oui, car dès que tu modifies une cellule, la macro évènementielle Private Sub Worksheet_Change(ByVal Target As Range) se lance, et donc tu perds la possibilité de revenir en arrière.

Tu ne m'as pas mis le dernier fichier Excel. Est-ce bien celui que j'ai mis la dernière fois ?

A+

Martial

 

[loulouange]

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

oups je viens de voir que mon fichier excel n'a pas été pris en compte donc je te le remets.

Citation Envoyé par loulouange Voir le message
J'ai regardé tes documents, j'ai eu un peu de mal au début mais c'est parce que tu as pris une ancienne version mais j'ai pu remettre les codes à jour (ça m'a fait un bon exercice )
C'est bien, je vais me tromper plus souvent

je n'aurais pas du ? j'avais pourtant mis en pièces jointes mes derniers fichiers lors de mon précédent post. En fait c'est des petites choses comme par exemple dans les lettres types j'avais rajouté un signet Civilité en début de la lettre (pas celui de l'adresse) donc je l'ai remis.
et ensuite dans la macro comme les colonnes avaient changé de position il fallait mettre à jour les numéros.

Sinon, là ou c'est un peu embêtant c'est lorsque le bénéficiaire change de statut "Stade Acgpt" dans la colonne Y de l'onglet général et qui est repris dans les onglets des conseillers dans la colonne E.

Je ne suis pas sûr d'avoir compris mais la solution que je te propose ci-dessus règlera peut-être ton problème.

en fait dans l'onglet "général" lorsque je mets le stade de l'accompagnement, c'est la colonne qui contient une liste déroulante: Phase Accueil, Phase Acpgt, Sorite. etc...

Cette liste se retrouve aussi dans la feuille des conseillers.

Donc quand je choisis un élément dans liste déroulante dans l'onglet Général et que je renseigne le nom CT , la ligne va se recopier dans la feuille du conseiller concerné, et donc l'info contenu dans la colonne de la liste déroulante.

Par la suite je suis amenée à mettre à jour la situation du stagiaire ce qui implique que je devrais modifier mon choix dans la liste déroulante selon l'étape de l'accompagnement action que je souhaiterais faire à partir de la feuille Général si possible.
Mais comme la ligne à déja été copiée une fois les modifications ne seront pas prises en compte.

Pour le tri alphabétique, auparavant le tri se faisait dès que la colonne NOM était remplie, pourquoi ne peut on pas faire ainsi ? Autrement le choix de mettre un bouton me parait une bonne solution.

Je n'ai pas eu le temps de regarder en détails toutes tes réponses, et je te remercie d'avoir répondu à mes interrogations sur les formules, je vais les examiner en détails et te dirais si c'est enfin clair pour moi.

Voila pour ce soir, j'espère ne pas trop en demander. prends ton temps il n'y a pas d'urgence.

Encore merci à toi.

Bonne soirée.

 

[loulouange]

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

Bonjour Martial,

Je reviens vers toi après avoir analysé tes explications.

Pour la première :

Citation Envoyé par loulouange Voir le message
DerL = Sheets(CT).[B65536].End(xlUp)(2).Row

tu as rajouté (2) : à quoi cela correspond il ?
Dans le cas d'une ligne, le (2) décale la valeur vers le bas d'une ligne (donc la première ligne vide)

Si je comprends bien, si on ne faisait pas de tri automatiquement dans la colonne du conseiller, on aurait le résultat suivant à savoir :

Une fois que les données de la ligne située dans la feuille « général » sont recopiées dans la feuille du conseiller concerné :
Ces dernières se retrouvent tout en bas du tableau : nous avons une ligne vide entre le dernier bénéficiaire qui a été ajouté et le nouveau ?

Pour la deuxième :

Citation Envoyé par loulouange Voir le message
.Range("A2:Q" & .[B65536].End(xlUp).Row).Sort [B2], xlAscending, [C2], , xlAscending, [D2], xlAscending, xlYes
Le point "." devant Range ou [B65536], permet de faire suite à With Sheets(i), on va donc trier les données de la feuille (i).
Pour sélectionner une plage de valeur, on utilise Range(PremièreCellule : DernièreCellule) : PremièreCellule = A2 ; DernièreCellule va être dans la colonne Q (d'où le :Q"), pour trouver la dernière ligne de la plage, nous cherchons la dernière ligne de la colonne B (celle qui contient les noms, on est sûr que cette cellule est remplie) [B65536].End(xlUp).Row

J'ai bien compris l'explication concernant le début de cette ligne :
Range("A2:Q" & .[B65536].End(xlUp).Row).

On parle bien des colonnes se trouvant dans les feuilles des CT ?


Mais ce qui suit m'échappe encore :
Sort [B2], xlAscending, [C2], , xlAscending, [D2], xlAscending, xlYes



Désolée si mes questions peuvent paraître bêtes, j'aimerais bien comprendre ce que je fais afin de pouvoir les reproduirie.

Merci pour ton aide.

Loulou

 

[Yaloo]

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

Bonjour Loulou,

Tout d'abord tes questions ne sont pas bêtes, je préfère les personnes qui essaient de comprendre plutôt que d'appliquer sans réfléchir.

Si l'on met DerL = Sheets(CT).[B65536].End(xlUp).Row, DerL va correspondre à la dernière ligne pleine de la colonne B en rajoutant le (2), ça décale d'une ligne, nous obtenons la première ligne vide de la colonne B.

Pour ta deuxième question : Sort correspond au tri, mais il faut lui dire quoi trier, avec [B2], xlAscending on va trier à partir de la cellule B2 dans l'ordre croissant puis [C2], , xlAscending on va trier à partir de la cellule C2 dans l'ordre croissant etc... jusqu'à 3 possibilités. xlYes en fin de ligne permet d'indiquer à VBA si en ligne 2 il y a une ligne de titre ou pas, pour avoir plus de détail fais F1 (pour avoir l'aide) sur Sort.

Je cherche une solution pour tes tris, ajouts et conservation des données de tes feuilles, mais je crois vraiment qu'il faut passer par une base regroupant toutes les données et ensuite on dispatche dans chaque feuille conseiller, je vais essayer et si c'est concluant je te ferai passer le fichier.

A+

Martial

 

[Yaloo]

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

Bonjour Loulou ,

Voici ton fichier avec une base regroupant toutes tes données. Cela permet, entre autre, de pourvoir tout modifier, comme dit dans des posts précédant, si tu modifies un Nom de stagiaire ou un Conseiller, tout se fait automatiquement, tu n'auras rien à modifier manuellement dans plusieurs feuilles.

Pour ce faire, j'ai ajouté une feuille "Base" et dans chaque feuille une colonne Id pour Identifier correctement le "Stagiaire", il ne risque pas d'y avoir d'erreur, si par exemple, plusieurs stagiaires ont le même nom et un prénom différent.

Toutes les macros sont déplacées dans ThisWorkBook, entre il y en a 2.

Workbook_SheetActivate dès que l'on active une feuille, cette macro est déclenchée.
En fonction de la feuille affichée, les lignes de codes permettent de réaliser soit une mise à jour de la feuille s'il s'agit de "Général", soit une reconstruction complète de toutes les lignes suivant le conseiller.

Workbook_SheetChange dès que l'on modifie une cellule dans une feuille, même principe que précédemment, en fonction de la feuille, les celulles modifiées sont envoyées vers la Base, cela permet aussi de créer un nouveau "Stagiaire".

Par exemple, si tu crées un Stagiaire, un N° Id est créé puis le Nom est copié dans Base ou si tu modifies un conseiller, cette information est envoyée dans Base, lorsque tu activeras la feuille du conseiller ce stagiaire apparaitra dans la feuille.

Pour mes tests, j'avais créé une macro pour remettre les évènements actifs en cas de plantage. Je l'ai laissé, au cas où il y aurait un problème. Si rien ne se passe lorsque tu fais des modifs ou lorsque tu actives les feuilles. Tu lances la macro Relance ou tu fais Ctrl + n.

Tout n'est surement pas parfait, notamment, j'ai un doute dans Date bilan Sortie, Contrat Date fin et Bilan Date échéance, j'ai créé 3 colonnes dans Base, j'ai l'impression que certaines de ces colonnes sont identiques

A te relire

Martial

PS : La feuille Base doit pas être cachée ainsi que les colonnes Id.