Mise à jour par Brad Jones le 25 août 2017.
Au cours de votre semaine de travail, il vous arrivera probablement de nombreuses fois de copier et coller des informations de Microsoft Excel dans Word, ou l'inverse.
C'est ainsi que les gens produisent souvent des rapports écrits basés sur des données accumulées et mises à jour dans une feuille de calcul. Excel est un excellent programme pour tout, de la création de graphiques d'aspect professionnel à la configuration d'un suivi de gestion de projet, mais lorsqu'il s'agit de rédiger un rapport, Word est un bien meilleur outil.
Dans cet article, nous allons nous plonger dans les scripts VBA en arrière-plan qui vous permettent de programmer des connexions entre les données stockées dans un fichier Excel et les documents Word dans lesquels vous produisez des rapports.
Il est étonnamment facile d'intégrer des données Microsoft Excel dans Microsoft Word une fois que vous savez comment ajouter les bonnes références et comment présenter la syntaxe du code VBA d'arrière-plan.
Dans cet exemple, je vais commencer avec une feuille de calcul Excel assez simple. En pratique, le fichier Excel peut être composé de plusieurs feuilles de calcul contenant de nombreuses données, peu importe.
Tant que vous savez où trouver les données dans la feuille de calcul, vous pourrez y accéder et les récupérer à l'aide de VBA.
Voici à quoi ressemble mon exemple de feuille de calcul. Il s'agit d'une liste des dépenses totales qui ont été calculées tout au long de l'année.
Imaginons que vous ayez un responsable qui souhaite voir un rapport bien formaté décrivant les dépenses, regroupant des éléments similaires et présentant les informations dans une mise en page un peu plus esthétique.
Pour ce faire, vous pouvez incorporer des objets tels que des zones de texte et des étiquettes dans votre document Word. Lorsque vous êtes dans Word, cliquez simplement sur le Développeur l'onglet de menu, puis sélectionnez "Mode Conception" dans les Contrôles section. Utiliser les outils hérités icône déroulante pour insérer différents éléments dans votre document.
Utilisez ce menu pour insérer un Libellé .
Une fois que vous avez placé l'étiquette dans le document où vous le souhaitez (ce qui n'est pas toujours une tâche facile), vous êtes prêt à programmer le flux de données. Mais d'abord, vous devrez nommer l'étiquette afin que le VBA puisse l'identifier. Faites un clic droit sur l'étiquette et allez dans Propriétés . Trouvez le (nom) champ et appelez-le quelque chose dont vous vous souviendrez.
Maintenant, ajoutez un bouton de commande à partir des mêmes outils hérités liste déroulante et double-cliquez dessus pour ouvrir l'éditeur VBA. Lorsque votre code fonctionnera plus tard, vous pourrez le modifier afin que le code s'exécute sur Document Open() un événement. Vous le verrez dans les listes déroulantes d'objets de la fenêtre de l'éditeur.
Pour commencer à connecter Word à Excel, vous devez vous assurer que vous pouvez référencer ce dernier dans VBA.
Cliquez sur Outils , puis Références . Faites défiler la liste jusqu'à ce que vous voyiez la bibliothèque d'objets Microsoft Excel 16.0 et sélectionnez-le.
Une fois que vous avez fait cela, le reste n'est qu'une question d'écriture d'un script VBA ridiculement simple pour extraire les données d'une feuille de calcul Excel et mettre automatiquement à jour la légende de l'étiquette avec les données.
Voici le code :
Sous-commande privé CommandButton1_Click()Dim objExcel As New Excel.Application
Dim exWb As Excel.Workbook
Définir exWb =objExcel.Workbooks.Open("C:\Users\Brad\Desktop\expenses.xlsx")
ThisDocument.total_expenses.Caption =exWb.Sheets("Sheet1").Cells(12, 2)
exWb.Fermer
Définir exWb =Rien
Fin du sous-titre
Vous voyez comment cela fonctionne ? L'objet d'application Excel "exWb" ouvre le fichier Microsoft Excel au chemin que vous lui fournissez, et il ira directement dans la feuille spécifique et le numéro de cellule, extraira les données et le placera dans la propriété Caption de l'étiquette que j'ai nommé dépenses_totales . Tout ce que vous devez modifier dans votre script est le chemin d'accès au fichier et le nom du libellé.
Pour tester votre bouton de commande, enregistrez votre document, en n'oubliant pas de spécifier que vous voulez un document compatible avec les macros Word pour que votre code fonctionne.
Voici la macro VBA en action.
Le plus difficile avec la gestion des étiquettes dans Word est qu'il est parfois difficile de l'aligner à la fin d'une phrase ou à côté de tout autre texte.
Une façon de surmonter cela consiste en fait à incorporer une partie du texte à côté des données dans le code VBA lui-même. Comme vous pouvez le voir ici, j'ai mis le texte statique directement dans la légende lorsque je crée l'étiquette elle-même.
Maintenant, tout ce que vous avez à faire est d'inclure ce texte lorsque vous mettez à jour l'étiquette avec votre script VBA, et d'ajouter simplement les données du fichier Microsoft Excel à la fin de ce texte.
Voici à quoi ressemblerait ce type de code.
Dim objExcel As New Excel.ApplicationDim exWb As Excel.Workbook
Définir exWb =objExcel.Workbooks.Open("c:\Users\Brad\Desktop\expenses.xlsa")
ThisDocument.total_expenses.Caption =exWb.Sheets("Sheet1").Cells(12, 2)
ThisDocument.total_hotels.Caption ="Hôtels :" &exWb.Sheets("Sheet1").Cells(5, 2)
ThisDocument.total_dining.Caption ="Dîner au restaurant :" &exWb.Sheets("Sheet1").Cells(2, 2)
ThisDocument.total_tolls.Caption ="Péages :" &exWb.Sheets("Sheet1").Cells(3, 2)
ThisDocument.total_fuel.Caption ="Carburant :" &exWb.Sheets("Sheet1").Cells(10, 2)
exWb.Fermer
Définir exWb =Rien
Vous pouvez utiliser le symbole de concaténation de chaînes "&" pour placer le texte statique avec les données extraites de la feuille Excel. Voici à quoi ressemblent les résultats finaux dans le document Word mis à jour :
Si vous voulez tester vos capacités, pourquoi ne pas automatiser encore plus votre rapport ? Vous pouvez supprimer ce bouton de commande gris et laid de votre document Microsoft Word en exécutant simplement le script de mise à jour des données sur Document.Open() — tout le processus se déroulera dans les coulisses.
Cela signifie que dans de nombreux cas, vous pouvez créer le document initial une seule fois, puis ne plus jamais avoir à le recréer. Tout ce que vous aurez à faire est de l'ouvrir, et toutes les étiquettes seront automatiquement mises à jour pour vous avec les données du fichier Microsoft Excel mis à jour. Cliquez simplement sur Imprimer , et soumettez le rapport à votre responsable. Un travail de 30 minutes s'est transformé en une impression d'une minute !
Pouvez-vous penser à d'autres utilisations intéressantes de cette technique d'intégration de données à l'aide de VBA ? Partagez certaines de vos propres idées et réflexions dans la section des commentaires ci-dessous.
Crédit image :Punyaphat Larpsomboon via Shutterstock.com
