Philippe Tulliez

Préambule

C'est l'envie d'automatiser mes offres en 1999 qui m'a poussé à m'intéresser au VBA et à mieux connaître les applications Excel et Word.
Le travail pour réaliser cette application fut fastidieux car il y avait peu, voire aucune information sur le VBA pour Word à cette époque

Ma première réalisation de transfert des données d'excel vers Word était basée sur le publipostage mais rapidement cela s'est avéré un calvaire, suite notamment aux sécurités ajoutées par Microsoft dans les versions qui ont suivies lorsque l'on utilisait le publipostage.

En 2010, je me suis tourné vers une solution plus simple à gérer, à savoir copier/coller des données provenant de cellules nommées (gestionnaire des noms) vers des signets dans Word
Ce billet et le code publié est le résultat d'une expérience fait de petits problèmes tels que le formatage des cellules (par exemple Date, séparateurs de milliers, etc.), le résultat surprenant obtenu entre la collage d'une cellule ou d'un tableau qui m'a obligé à utiliser deux méthodes différentes en fonction du nombre de cellules à copier

Prérequis
Pour pouvoir suivre ce qui suit il faut savoir

• Utiliser le gestionnaire des noms dans Excel
• Gérer les signets dans Word
• Référencer une bibliothèque
• Utiliser les tableaux structurés dans excel

Concept

En pratique comment faire ?
Côté Excel, on utilisera le gestionnaire des noms pour nommer les cellules à copier, et côté Word, on utilisera les signets. Idéalement on utilisera des noms identiques de part et d'autre ce qui évitera de créer une table de correspondance (Exemple : Une cellule nommée ProjectNumber aura un signet portant le même nom).

On créera donc un document word dans lequel on placera les signets et que l'on utilisera comme modèle. J'ai opté de des sauver comme document (suffixe DocX) et pas comme modèle (suffixe Dotx) car cela évite de corriger l'emplacement proposé par la boîte de dialogue Sauver Sous.

Organisation des données
Pour éviter tout problème de maintenance, on placera dans un répertoire dédié à l'application le classeur Excel qui contient le code VBA et on créera un sous-répertoire nommé par exemple Template dans lequel on placera le ou les modèles contenant les signets à remplacer, un sous-répertoire nommé par exemple Document où l'on sauvera les documents modifiés, etc.

Le classeur Excel
Dans la feuille nommée Facture, illustration ci-dessous, se trouve tous les éléments à copier

Liste des cellules nommées

Adresse =Facture!$E$3
CodePostal =Facture!$E$4
Détail_Facture =t_InvoiceBody[#Tout]
RaisonSociale =Facture!$F$2
Ville =Facture!$F$4

Attention : La procédure publiée ne fonctionne qu'avec des cellules nommées de portabilité classeur



Le document Word
Comme expliqué plus haut, ce document bien que suffixé docx sert de modèle pour effectuer les transferts. Dans l'exemple il se nomme Courrier.docx
En surlignage jaune, les signets (Les textes surlignés ne sont pas indispensables et ne sont présents que dans le seul but de clarifier l'explication)
On peut apercevoir la boîte de dialogue des signets



Le document Word après traitement



Programmation

Référencement de la bibliothèque Word
Dans l'éditeur VBE, cliquer sur Outils/Références... et dans la boîte de dialogue Références - VBA Project sélectionner la bibliothèque Microsoft Word xx.x Object Library comme illustré ci-dessous.



Code de la procédure de transfert
Dans un module du classeur Excel, on placera cette fonction générique nommée TransferData_Excel_Word

Code VBA :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
Function TransferData_Excel_Word(oDocument As Word.Document, _
                                   BookmarksNames As Variant)
  ' Nécessite de référencer Microsoft Word xx.0 Object Library
  ' But : Copier la valeur et le format d'une plage nommée du classeur actif et la coller à l'endroit du signet du même nom
  '
  ' Author  : Philippe Tulliez (www.magicoffice.be)
  '
  ' Arguments
  '   oDocument      : Objet Application
  '   BookmarksNames : Table contenant le nom des signets
  '
  Dim Elem As Integer
  Dim Bm As String
  With oDocument
    For Elem = LBound(BookmarksNames) To UBound(BookmarksNames)
      Bm = BookmarksNames(Elem)
      If .Bookmarks.Exists(Bm) Then
         If Range(Bm).Count = 1 Then
            .Bookmarks(Bm).Range.Text = ActiveWorkbook.Names(Bm).RefersToRange.Text
          Else
             Range(Bm).Copy
            .Bookmarks(Bm).Select
            .Parent.Selection.PasteSpecial DataType:=wdPasteEnhancedMetafile, Placement:=wdInLine
             Application.CutCopyMode = False
         End If
      End If
    Next
  End With
End Function

Exemple de la procédure qui l'invoque

Code VBA :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
Sub Main()
  ' Nécessite de référencer Microsoft Word xx.0 Object Library
  ' Déclaration
  Const SubFolder As String = "\Template\"          ' Sous-répertoire des modèles
  Const TemplateName As String = "Courrier.docx"    ' Nom du modèle
  Dim oWrd As Word.Application                      ' Application Word
  Dim oDoc As Word.Document                         ' Document Word traité
  Dim RootFolder As String                          ' Répertoire racine
  Dim FullName As String                            ' Nom complet
  Dim CellsName As Variant                          ' Nom des cellules à copier
  ' Affectation
  Set oWrd = New Word.Application
  RootFolder = ThisWorkbook.Path                    ' Chemin du classeur
  FullName = RootFolder & SubFolder & TemplateName  ' Chemin + Nom complet où se trouve le modèle
  CellsName = Array("RaisonSociale", "Adresse", "CodePostal", "Détail_Facture", "Ville")
  ' Ouverture du modèle Word
  Set oDoc = oWrd.Documents.Add(Template:=FullName, NewTemplate:=False, DocumentType:=0)
  ' Transfert des données
  TransferData_Excel_Word oDocument:=oDoc, BookmarksNames:=CellsName
  ' Active le document et le rend visible
  With oWrd
  .Visible = True: .Activate
  End With
  '
  Set oWrd = Nothing: Set oDoc = Nothing
End Sub

Les plus

• Pour s'éviter un travail inutile, on préfixera toutes les cellules à transférer afin de pouvoir dynamiquement charger la liste des noms.
• Pour utiliser un objet de l'application Word depuis Excel nous avons deux solutions le Early ou le Late Binding.
  Le Early Binding est plus simple à utiliser et offre plusieurs avantages dont l'usage de l'intellisense pour les objets Word mais il nécessite de référencer la bibliothèque Word et sa portabilité descendante pose problème. Personnellement je développe en Early et je livre avec le Late ce qui évite les problèmes. (Voir le tutoriel de Maxence Hubiche intitulé Comprendre : Early ou Late Binding et mon billet titré La compilation conditionnelle ou comment développer en EarlyBinding et distribuer en late binding
• Idéalement, le mieux est de créer un module de classe, ce qui permet de simplement passer comme argument le nom du modèle, le nom sous lequel on souhaite sauver le document et un argument supplémentaire pour fermer ou pas l'application.

Tutoriels
A lire éventuellement

• Comprendre : Early ou Late Binding
• La compilation conditionnelle ou comment développer en EarlyBinding et distribuer en late binding
• Création et utilisation de classes personnalisées en VB 6.0 et VBA (partie 1)