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.

 

[Yaloo]

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

Re,

Alors 2 choses :

    With oDoc
      'Pour i de 1 à 6, puisque nous avons 6 signets dans le document Word
      'et 6 colonnes dans le fichier Excel
      For i = 1 To 24
        'Le signet prend la valeur de la colonne, on fonction de i
        .Bookmarks("Signet" & i).Range = ListBox1.Column(i - 1)
      Next
    End With

Avec cette macro, tu envoies les valeurs des colonnes de 0 à 23 (i de 1 à 24 - 1), alors que tu n'as que 6 colonnes, dans les signets du document Word, mais dans ton document tu n'as pas tous les signets, il manque les 8, 11, 14, 17 etc...

Une fois ces 2 petites broutilles modifiées ça devrait aller beaucoup mieux

A+

 

[loulouange]

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

oui au départ c'était ta macro qui était basée sur 6 colonnes, donc j'ai remplacé par
i= 1 to 24 ce qui correspond pour moi aux colonnes de C à Y non ?

dans le document word j'ai inséré seulement les signets que j'avais besoin, et qui concerne les colonnes :

de C à O puis Q puis R et pour finir Z. je n'ai pas besoin des données des colonnes 14, 17 à 23.

je ne sais pas si ça possible problème s'il n'y pas de continuité dans les colonnes

 

[Yaloo]

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

Bonjour Loulou,

Si tu utilises une boucle, il faut qu'elle soit complète, donc dans ton cas il faudrait s'arrêter à une boucle de 13, puisque tu as 13 lettres de C à O, puis ensuite mettre tes 3 colonnes Q, R et Z séparément.
De plus, il faut que tu es au moins 13 colonnes dans ta ListBox (pour la boucle) puis 3 colonnes supplémentaires pour Q, R et Z (à moins de renvoyer des données des 13 premières colonnes).

Ça devrait donner quelque chose comme ça :

  Set oDoc = oWord.Documents.Open(ThisWorkbook.Path & "\Contrat Objectifs ADS Cévennes Las Rebes.docm")
    'Avec ce document
    With oDoc
      'Ta boucle te permettant de copier tes valeurs de la ListBox (colonne de C à O)
      'dans tes signets de 1 à 13
      For i = 1 To 13
        'Le signet prend la valeur de la colonne, on fonction de i
        .Bookmarks("Signet" & i).Range = ListBox1.Column(i - 1)
      Next
      'Puis tes données des colonnes Q, R et Z pour tes signets 14, 15 et 16
      .Bookmarks("Signet14").Range = ListBox1.Column(13)
      .Bookmarks("Signet15").Range = ListBox1.Column(14)
      .Bookmarks("Signet16").Range = ListBox1.Column(15)
    End With

A te relire

Martial

 

[loulouange]

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

Bonjour Martial, le forum,

Merci pour ta proposition de code, cela m'a bien aidée pour l'adapter à mon fichier. J'ai eu un peu de mal au début mais je suis contente d'y être parvenue !! et j'ai aussi pu corriger un problème de format de date, ce qui donne le code suivant :

  Set oDoc = oWord.Documents.Open(ThisWorkbook.Path & "\Contrat Objectifs ADS Cévennes Las Rebes.docm")
    'Avec ce document
   With oDoc
      'Ta boucle te permettant de copier tes valeurs de la ListBox (colonne de C à O)
     'dans tes signets de 1 à 13
     For i = 1 To 13
        'Le signet prend la valeur de la colonne, on fonction de i
       .Bookmarks("Signet" & i).Range = ListBox1.Column(i - 1)
      Next
      'Puis tes données des colonnes Q, R et Z pour tes signets 14, 15 et 16
    .Bookmarks("Signet14").Range = Format(DateValue(ListBox1.Column(14)), "dd/mm/yy")
      .Bookmarks("Signet15").Range = Format(DateValue(ListBox1.Column(15)), "dd/mm/yy")
      .Bookmarks("Signet16").Range = Format(DateValue(ListBox1.Column(23)), "dd/mm/yy")
      End With
'On rend Word visible
oWord.Visible = True
End Sub

Au début j'étais un peu perdue, mais j'ai compris que le numéro de la colonne du fichier source correspondait au numéro "-1" en réalité.

Je voulais savoir pourquoi on devait mettre cette ligne ?

.Bookmarks("Signet" & i).Range = ListBox1.Column(i - 1)

pourquoi "-1" ?

A présent mon fichier doc fonctionne très bien, après avoir compris qu'il fallait impérativement que tous les signets soient insérés dans le fichier word, pour mon cas : ceux qui sont cités en continuité de "de 1 à 13", (car le 11 n'était pas nécessaire mais je me suis débrouillée pour ne pas le voir dans word).

Je vais pouvoir continuer sur mes autres modèles à présent o)

Toutefois, j'ai un autre problème et oui encore désolée. Mais c'est pas faute d'avoir cherché et tester mais j'ai vraiment besoin que tu m'aides.

J'ai des formulaires mais au format Excel, et donc là je pense que se ne sont pas des Signets qui sont à insérer non ?

Par exemple je voudrais renvoyer l'info qui se trouve dans ma listebox "colonne2" c'est à dire le NOM vers le formulaire excel dans la cellule C5.


Une autre question mais là cela concerne l'apparence de l'useform, tu as mis un code pour que l'ascenseur soit toujours remonté vers le haut et c'est parfait. Mais je voulais savoir s'il était normal de devoir appuyer deux fois sur le bouton qui génère le fichier Word ?

je te mets ci-dessous des captures écrans pour mieux comprendre :

L'image 1 : j'ouvre l'useform uen
en cliquant sur le bouton "ADS Cevennes Las Rebes" une autre fenêtre s'ouvre (image 2).

L'image 2 : Ici je dois à nouveau cliquer sur ce même bouton "ADS Cevennes Las Rebes" pour voir le résultat de ma fusion dans le fichier Word.

Voila pour mes questions du jour.

Autrement, j'ai réussi à faire mes mises en forme conditionnelles avoir de texte et de cellules, j'avance et je progresse grâce à ce super forum et à ton aide précieuse, encore merci pour tout.


A te relire.

Loulou

 

[Yaloo]

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

Bonsoir Loulou,

Pourquoi le -1 ? Parce que certains Controls ou les Array commence à 0, c'est à dire que la première colonne est la colonne 0.
Ta boucle va de 1 à 13, tes signets vont de 1 à 13 mais ta ListBox va de de 0 à 12, d'où le décalage.
On pourrait aussi écrire :

  Set oDoc = oWord.Documents.Open(ThisWorkbook.Path & "\Contrat Objectifs ADS Cévennes Las Rebes.docm")
     'Avec ce document
   With oDoc
       'Ta boucle te permettant de copier tes valeurs de la ListBox (colonne de C à O)
     'dans tes signets de 0 à 12
     For i = 0 To 12
         'Le signet prend la valeur de la colonne, on fonction de i
       .Bookmarks("Signet" & i + 1).Range = ListBox1.Column(i)
       Next
       'Puis tes données des colonnes Q, R et Z pour tes signets 14, 15 et 16
    .Bookmarks("Signet14").Range = Format(DateValue(ListBox1.Column(14)), "dd/mm/yy")
       .Bookmarks("Signet15").Range = Format(DateValue(ListBox1.Column(15)), "dd/mm/yy")
       .Bookmarks("Signet16").Range = Format(DateValue(ListBox1.Column(23)), "dd/mm/yy")
       End With
'On rend Word visible
oWord.Visible = True
End Sub

Pour mettre une valeur de ListBox dans une cellule, il suffit d'écrire :

Range("C5") = ListBox1.Column(i)

ou

[C5] = ListBox1.Column(i)

ou

Cells(5, 3) = ListBox1.Column(i)

Pour le positionnement de l'UserForm, je ne comprends pas pourquoi en cliquant ça descend mais ça ne fait pas l'action demandée. J'y regarde, mais je suis en vacances demain, donc ce sera après.

Passe de bonnes fêtes

Martial

 

[loulouange]

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

Bonjour Martial,

Merci pour ton aide et tes explications, je vais pouvoir avancer avec l'useform avec les formulaires excel.

Je te souhaite de bonnes vacances, passes de bonnes fêtes.

Dans l'attente de te lire.

Loulou

 

[Yaloo]

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

Bonjour Loulou, le forum,

Pour ton clic sur ton bouton sans faire l'action du bouton, je ne vois vraiment pas.
Ça vient bien du Scroll, car si on l'enlève, ça fonctionne correctement, je ne comprends pas pourquoi l'action n'est pas réalisée lorsqu'il y a le Scroll .
Je continue mes recherches .... en espérant.

A+

Martial

 

[loulouange]

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

Bonjour Martial, le forum,

Tout d'abord , meilleurs vœux pour le site et pour tous ceux font vivre ce forum. Je vous souhaite bonheur et prospérité pour cette nouvelle année.

Désolée de ne pas être venue plus tôt j'avais pris aussi quelques jours de vacances.

Je viens de regarder mon useform, et j'ai compris d'où venait le problème. Dans les propriétés de l'useform j'avais mis en hauteur "500" pour le cadre où se trouvent les boutons. alors qu'au niveau du cadre général (listbox+boutons) j'avais noté 420 et pour le scroll 620. J'ai donc remplacé ces deux dernières valeurs par 800 et là je n'ai plus le problème de menu qui remonte.

Bon et bien va falloir que je fasse tous les boutons à présent en espérant avoir bien compris la procédure.

Merci encore pour ton aide.

A+

Loulou

 

[loulouange]

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

Bonsoir Martial,

Je reviens vers toi après avoir cherché sur le forum des réponses mais en vain.

Je travaille donc sur les fichiers Word à générer via l'useform. Pour les tests, les fichiers étaient jusqu'ici placés dans le même répertoire que mon fichier sources excel.

Mais en réalité, dans l'organisation de mes différents documents, ceux ci sont enregistrés dans des sous-répertoires , or lorsque je mets le chemin d'accès dans le code de l'useforme j'ai le message suivant :

erreur d'éxecution "424" - Objet requis

et la ligne ci dessous de mon code est surlignée en jaune :

Set oDoc = oWord.Documents.Open(ThisWorkbook.Path & "C:\TESTS USEFORM\Montpellier\Contrats\Contrat Objectifs ADS Cévennes Las Rebes.docm")

Pour éviter toute erreur, je suis allée dans mon fichier Word et fait un copier/coller dans les propriétés le chemin du fichier.


Or, j'ai vérifié, le fichier fonctionne parfaitement avec la fusion des signets si je le place au meme endroit que le fichier excel avec cette ligne :

Set oDoc = oWord.Documents.Open(ThisWorkbook.Path & "\Contrat Objectifs ADS Cévennes Las Rebes.docm")

Voila si tu as une idée, ça me serait d'une grande aide.

A plus pour te relire.

Loulou

 

[Yaloo]

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

Bonsoir Loulou,

Le Set oDoc = oWord.Documents.Open(ThisWorkbook.Path & "C:\TESTS USEFORM\Montpellier\Contrats\Contrat Objectifs ADS Cévennes Las Rebes.docm") me parait un peu surchargé, soit tu mets ThisWorkbook.path et à la rigueur un/des sous-répertoire(s) soit tu écris sans mettre ThisWorkbook.Path
Dans la ligne de ta macro, lorsque tu mets ThisWorkbook.Path cela veut déjà dire C:\Mon répertoire\mon sous-répertoire\ donc si tu mets : ThisWorkbook.Path & "C:\mon répertoire\mon sous-répertoire" il y a doublon.

En gros :

Set oDoc = oWord.Documents.Open(ThisWorkbook.Path & "\TESTS USEFORM\Montpellier\Contrats\Contrat Objectifs ADS Cévennes Las Rebes.docm")

Set oDoc = oWord.Documents.Open("C:\TESTS USEFORM\Montpellier\Contrats\Contrat Objectifs ADS Cévennes Las Rebes.docm")

A+

 

[loulouange]

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

Bonjour Martial,

Tout est bien plus clair à présent, je comprends mieux d'où venait le problème, j'ai testé tes deux lignes, la première m'affichait un message d'erreur mais la seconde fonctionne à merveille !!

Merci encore pour ton coup de pouce.

A+

Loulou

 

[loulouange]

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

Bonsoir Martial, le forum,

Je fais appel à toi à nouveau, mais j'aurais bien voulu trouver la solution par moi-même mais là j'abandonne tssss.

J'ai passé des heures à faire des essais, à lire des sujets qui ressembleraient au mien, mais je m'en rend bien compte que j'ai beaucoup de chose encore à apprendre.

J'ai tenté d'adapter le code pour l'useform pour fusionner des données de cellules vers un autre fichier excel.

Mon modèle de base s'appliquait sur un fichier word, mais dans celui ci il fallait donc tout modifier.
Mais cela ne fonctionne pas, j'ai eu plusieurs bugs, le fichier destination était ouvert mais pas moyen de le voir. e PUIS en faisant des modifications, je me suis rendue compte que le transfert des données se faisaient dans le même fichier, le fichier source donc.

Donc je pense qu'il faudrait préciser vers où envoyer les données,

Dons si tu veux bien enocre m'aider, voici le premier code que j'avais mis :

Private Sub CommandButton35_Click()
Dim oXls As Object
Dim i&
'Si aucune ligne de la ListBox1 n'est sélectionnée alors on a un message puis on sort
If ListBox1.ListIndex = -1 Then MsgBox "Il faut sélectionner une personne": Exit Sub
'On ferme l'UserForm1
Unload Me
  'Détermine l'Objet oExcel qui sera l'application
  Set oXls = CreateObject("Excel.Application")
  'Détermine l'Objet oXls qui sera le document Word de l'application oWord
  'On en profite pour l'ouvrir en même temps
  'Il faut mettre le chemin d'accès et le nom du fichier avec son extension
  'Le chemin d'accès, dans ce cas-là, est le même que celui de ce fichier Excel
  Set oXls = oXls.Workbooks.Open("C:\TESTS USEFORM\Suivi Admi\Fiche présences - ent + ateliers.xls")
  'Avec ce document
    With oXls
    [C6] = ListBox1.Column(2)
    [C6] = ListBox1.Column(3)
    [C5] = Format(DateValue(ListBox1.Column(12)), "dd/mm/yy")
    [AG5] = Format(DateValue(ListBox1.Column(14)), "dd/mm/yy")
   [AG6] = Format(DateValue(ListBox1.Column(15)), "dd/mm/yy")
    [T5] = Format(DateValue(ListBox1.Column(23)), "dd/mm/yy")
 End With
'On rend Excel visible
Xls.Visible = True
End Sub

Par la suite j'ai tenté cet autre code mais sans succès

 Private Sub CommandButton35_Click()
Dim oXls As Object
Dim i As Integer
Set wb1 = ThisWorkbook 'classeur destination
'Si aucune ligne de la ListBox1 n'est sélectionnée alors on a un message puis on sort
If ListBox1.ListIndex = -1 Then MsgBox "Il faut sélectionner une personne": Exit Sub
'On ferme l'UserForm1
Unload Me
  'Détermine l'Objet oExcel qui sera l'application
  Set wb1 = CreateObject("Excel.Application")
  'Détermine l'Objet oXls qui sera le document Excel de l'application oEXcel
  'On en profite pour l'ouvrir en même temps
  'Il faut mettre le chemin d'accès et le nom du fichier avec son extension
  'Le chemin d'accès, dans ce cas-là, est le même que celui de ce fichier Excel
  Set oXls = oXls.Workbooks.Open("C:\TESTS USEFORM\Suivi Admi\Fiche présences - ent + ateliers.xls")
  'Avec ce document
    With wb1
    For i = 0 To Me.ListBox1.ListCount - 1
    If Me.ListBox1.Selected(i) = True Then
   [C6] = ListBox1.Column(2)
    [C6] = ListBox1.Column(3)
    [C5] = Format(DateValue(ListBox1.Column(12)), "dd/mm/yy")
    [AG5] = Format(DateValue(ListBox1.Column(14)), "dd/mm/yy")
   [AG6] = Format(DateValue(ListBox1.Column(15)), "dd/mm/yy")
    [T5] = Format(DateValue(ListBox1.Column(23)), "dd/mm/yy")

  End If
  End With
     Next
  oXls.Visible = True
End Sub

Voila où j'en suis, je progresse mais parfois j'ai l'impression de faire un pas en avant et deux pas en arrière. J'aurais tant aimé y être parvenue.

En espérant te relire.

A+

Loulou

 

[Yaloo]

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

Bonsoir Loulou,

J'ai vraiment pas beaucoup de temps en ce moment, le boulot et un PC qui lâche, mais je peux essayer d'y regarder, pourrais-tu mettre les fichiers en pièces jointes, car juste avec les macros c'est difficile de voir ?

A te relire

Martial

 

[loulouange]

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

Bonjour Martial,

C'est sympa de penser à moi, je comprends que tu as tes priorités, tu as déjà fait énormément pour moi et je t'en suis très reconnaissante.

J'ai continué à chercher une solution à mon problème mais hélas pour l'instant pas de résultats concluants.

Je te remets les fichiers en pièces jointes afin que tu puisses regarder le problème quand tu auras un peu de temps.
Cela concerne l'useform de la feuille1, et le bouton "Fiche présences annuelle".

En te remerciant encore.

Bonne fin de journée.

A+

Loulou

 

[Yaloo]

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

Bonsoir Loulou,

Voici ton fichier, lorsque tu ouvres un fichier Excel, tu n'as pas besoin de créer un objet, puisqu'il s'agit de la même application.
Tu peux donc l'ouvrir directement. Dans la macro j'ai modifié dans ce sens. Comme on vient d'ouvrir le fichier celui-ci est le classeur actif, comme tu n'as qu'une seule feuille, les données ne peuvent se mettre que dans ce classeur et dans cette feuille.

Enfin, normalement ça doit fonctionner, j'ai modifié les cellules de réception, car elles ne me semblaient pas justes. Il ne faut oublier que la première colonne d'une ListBox est 0, donc tu devais avoir un décalage avec tes cellules.

De plus, dans ta macro, tu mets With oXls et après tu ne mets pas de point avant tes données, donc ça ne sert pas à grand chose (oh que c'est compliqué tout ça ...).

With oXls.Sheets(1)
.[C5] = ListBox1.Column(1)
.[C6] = ListBox1.Column(2)

A+

Martial