Blog ENI : Toute la veille numérique !
Accès illimité 24h/24 à tous nos livres & vidéos ! 
Découvrez la Bibliothèque Numérique ENI. Cliquez ici
💥 1 livre papier acheté 
= la version en ligne automatiquement offerte. Cliquez ici
  1. Livres et vidéos
  2. Apprenez le langage VBA
  3. Consolidation et partage de données
Extrait - Apprenez le langage VBA et devenez un expert sur Excel (versions 2019, 2021 et Microsoft 365)
Extraits du livre
Apprenez le langage VBA et devenez un expert sur Excel (versions 2019, 2021 et Microsoft 365) Revenir à la page d'achat du livre

Consolidation et partage de données

Consolidation de données diverses : description de l’exemple

Présentation de l’exemple

En informatique, la consolidation permet de regrouper les données provenant de différentes sources afin d’obtenir un rapport structuré.

L’exemple qui va suivre va permettre de consolider dans un premier temps plusieurs sources de données en une seule puis de travailler sur le tableau consolidé afin d’en extraire les informations clés.

Il s’agit d’un groupe immobilier composé de deux agences distinctes qui sont situées à Paris et à Reims. Bien que semblable dans l’organisation, la saisie des opérations en cours ne se fait pas de la même manière pour les deux agences. Elles utilisent cependant toutes les deux un fichier Excel retraçant leur activité.

L’objectif sera donc de consolider ces deux sources de données en un seul fichier regroupant l’ensemble des informations. L’agence immobilière souhaite également proposer un tableau récapitulatif de son activité.

Cet exemple ne propose pas d’interface utilisateur. Le résultat se présente sous la forme d’une source de données consolidées.

images/06sob00.PNG

Présentation des classeurs

Cet exemple se présente avec trois classeurs distincts :

  • Le classeur Immo-Paris.xlsx contient une feuille pour l’agence parisienne comportant les données suivantes :

Colonne Excel

Libellé

Valeur exemple

Colonne A

Date

Date de la réalisation de l’opération. Elle sera stockée sous le format Timestamp qui est très répandu en informatique : il s’agit d’un compteur numérique correspondant au nombre de secondes écoulées depuis le 1er janvier 1970.

Par exemple : 1er janvier 2022 : 1641016966

Un des avantages de ce format est la facilité d’effectuer la comparaison de date, puisque cela revient à faire une différence entre deux nombres

Colonne B

Agent

Nom de l’agent immobilier s’occupant de la vente

Colonne C

Arrondissement

Il s’agit des arrondissements si la commune en possède.

Colonne D

Commune

Paris, Ivry-sur-Seine…

Colonne E

Code Postal

75013, 94200

Colonne F

Type de bien

Maison, Appartement, Loft, Villa

Colonne...

Consolidation de données diverses : notions de cours

Manipulation de feuilles et classeurs

La manipulation de feuilles et classeurs consiste à manipuler des variables objets.

Gestion de l’application Excel

L’import va consister à ouvrir des classeurs Excel (Reims et Paris) puis à sélectionner les feuilles nécessaires. Toutes ces informations seront stockées dans des variables de type objet.

Dim ExcApp As Excel.Application 'Variable de gestion de l'application 
Dim WB As Excel.Workbook 'Variable classeur 
Dim WS As Excel.Worksheet 'Variable feuille de calcul 

La gestion de l’application n’est pas nécessaire si l’application est déjà ouverte. En revanche, le même mécanisme s’applique pour manipuler d’autres applications comme PowerPoint.

Sélection et ouverture d’un classeur Excel

Méthode GetOpenFileName

Pour la sélection du fichier Excel, il est possible d’utiliser la méthode GetOpenFileName de la classe Application qui permet l’ouverture d’une fenêtre de sélection de fichier. Cette méthode renvoie le chemin de l’application sélectionnée, et il peut être utilisé pour ouvrir le fichier sélectionné.

Nom_Fichier = Application.GetOpenFilename("Fichiers Excel (*.xlsm),  
*.xlsm") 'filtre sur les fichiers Excel 
If Nom_Fichier <> False Then 
‘Ouverture fichier 
Else 
Msgbox ("Fichier non sélectionné") 
End if 

Méthode FileDialog

Toutefois, l’exemple proposé utilisera une autre méthode plus complète Application.FileDialog qui permet tout type d’échange avec les répertoires et fichiers :

En fonction de l’argument associé à la méthode FileDialog, la boîte de dialogue prendra une forme différente :

  • Argument msoFileDialogFilePicker : sélection de fichier.

  • Argument msoFileDialogFolderPicker : sélection de dossier.

  • Argument msoFileDialogOpen : ouverture de fichier....

Consolidation de données diverses : réalisation de l’exemple

 Ouvrez le fichier Enoncé_6-ABC.xlsm qui contiendra les données consolidées. Les fichiers Immo_Paris.xlsx et Immo_Reims.xlsx seront utilisés dans l’import mais ils ne seront pas ouverts dans l’exemple.

Structure du code

Le code va être réalisé au sein d’une seule et unique procédure qui sera utilisée une seule fois pour récupérer les données des deux feuilles.

Il faut donc créer une procédure qui stockera l’ensemble du traitement et sera nommée InsertionDonnees.

 Insérez un module et saisissez les lignes de code suivantes :

Option Explicit
Sub InsertionDonnees 
'Le code sera inséré ici 
End sub 

Déclaration des variables feuille et classeur

Pour réaliser cet exemple, il va falloir dans un premier temps créer les variables de type objet pour stocker et manipuler les feuilles et classeurs.

Il sera nécessaire d’avoir six variables :

  • Trois variables de types Classeur :

  • Fichier Consolidé,

  • Fichier Reims,

  • Fichier Paris.

  • Trois variables de types Feuille :

  • Feuille Consolidée : au sein du fichier consolidé Enoncé_6-ABC.xlsx, feuille contenant les données consolidées.

  • Feuille Reims : au sein du fichier Immo_Reims.xlsx, feuille contenant les données du fichier Reims.

  • Feuille Paris : au sein du fichier Immo_Paris.xlsx, feuille contenant les données du fichier Paris.

 Déclarez les variables de la manière suivante :

'Définition des variables 
Dim WBReims As Excel.Workbook  
Dim WBParis As Excel.Workbook 
Dim WBFinal As Excel.Workbook 
Dim WSReims As Excel.Worksheet 
Dim WSParis As Excel.Worksheet 
Dim WSFinal As Excel.Worksheet 

La déclaration de variables peut être regroupée en une seule ligne par type de variable. Les noms de variables doivent être séparés par une virgule :

Dim WBFinal, WBReims, WBParis As Excel.Workbook 
Dim WSFinal, WSReims, WSParis As Excel.Worksheet 

Pour affecter des valeurs aux variables, il est nécessaire d’utiliser le mot clé Set qui permet d’assigner une référence à l’objet. Dans ce cas, WBFinal aura pour référence...

Partage des données : description de l’exemple

Présentation de l’exemple

L’objectif de cet exemple est de proposer une solution permettant aux deux agences immobilières la saisie des données. La problématique est que ce fichier Excel n’a pas vocation à être maintenu par une seule agence, mais il doit être accessible et modifiable par les deux agences et peut-être à terme, par une multitude d’agences.

Présentation des classeurs et outils utilisés

Dans le cadre de cet exemple, le fichier généré dans la première partie de ce chapitre sera utilisé puisqu’il contient les données saisies dans les agences. Toutefois, le fichier a subi quelques améliorations avec notamment un onglet Paramètres. Le fichier sur lequel nous allons nous baser est le fichier Enoncé_6-DEF.xlsm.

Cet exemple va requérir un compte sur Microsoft OneDrive (https://onedrive.live.com/) et dans le cadre de la dernière partie, il est nécessaire d’avoir Outlook 2021, Outlook Microsoft 365 installé sur votre poste. Si vous ne possédez pas Outlook, le code peut être facilement adapté pour d’autres solutions.

Le compte OneDrive permettra de créer un formulaire Excel en ligne dont les valeurs seront stockées au sein d’un fichier.

L’application...

Partage des données : notions de cours

Formulaire de tableau

L’option Formulaire est une fonctionnalité d’Excel permettant d’ajouter/modifier/supprimer des données dans une série de données. Généralement utilisée avec des tableaux, cette fonctionnalité peut être utilisée également avec une simple série de données. 

Avantages

L’avantage de cette fonctionnalité est de générer un formulaire de saisie et de modification de manière simple, juste via un clic. L’édition, l’ajout et la suppression sont simples d’accès et il est même possible de rechercher un élément.

Inconvénients

L’inconvénient majeur est le manque de possibilités d’aide à la saisie.

Il est impossible de qualifier la donnée à insérer. De plus, si vous avez appliqué des contraintes sur les données (onglet Données - Validation des données), vous risquez de ne pas pouvoir insérer vos données avec le formulaire. En effet, si la valeur saisie ne répond pas à la valeur attendue, l’ensemble de la ligne ne sera pas inséré.

Comment insérer le formulaire ?

 Ouvrez le fichier ExempleCours_Chapitre_6.xlsx.

Vous trouverez sur la feuille deux tableaux identiques en A1:C9 et H1:J9. Chaque tableau comporte trois colonnes listant les prénoms, le sexe et le nom de l’équipe.

Le premier tableau sur la plageA1:C9 est une plage de données, non déclarée en tant que tableau Excel. Aucune case ne porte de contrainte. Le second tableau sur la plage H1:J9, est un tableau Excel. Les colonnes concernant le sexe et le nom de l’équipe sont obligatoires : l’utilisateur doit choisir une des valeurs.

 Dans l’onglet Fichier, choisissez Options.

 Cliquez sur Personnaliser le ruban, puis dans la zone Choisir les commandes dans les catégories suivantes, sélectionnez Toutes les commandes.

 Dans la liste de gauche, sélectionnez Formulaire.

 Dans la liste de droite, sélectionnez Données puis cliquez sur Nouveau groupe.

images/06SOB03N.PNG

 Cliquez sur Ajouter>> pour insérer le bouton Formulaire dans ce nouveau groupe de l’onglet...

Partage des données : réalisation de l’exemple

 Commencez par ouvrir le fichier Enoncé_6-DEF.xlsm.

Créer un formulaire de saisie automatique pour faciliter la saisie des données

Le formulaire de saisie va permettre la saisie de données sur un tableau sans avoir à utiliser un formulaire VBA.

Afficher le formulaire

 Si vous n’avez pas ajouté le bouton Formulaire, ajoutez-le à l’onglet Données (référez-vous à la section Formulaire de tableau dans la section Partage des données : notions de cours de ce chapitre).

 Sélectionnez la cellule A1.

 Dans l’onglet Données, cliquez sur le bouton Formulaire.

images/06SOB32N.png

Le formulaire apparaît ainsi :

images/r06SOB04N.PNG

Modifier une donnée

L’agent Benoist vous informe qu’une vente n’a finalement pas eu lieu suite à un problème de dernière minute sur l’attribution du prêt. Il sait que l’offre est parue le 14/12/2021.

 Cliquez sur Critères puis saisissez 14/12/2021 dans la zone Date de parution de l’offre.

images/r06SOB05N.PNG

 Appuyez sur la touche Entrée, puis cliquez sur les boutons Précédente et Suivante pour accéder à l’opération effectuée par l’agent Benoist le 14/12/2021.

images/r06SOB06N.PNG

 Modifiez la valeur du champOpération réussie de VRAI en FAUX puis cliquez sur Fermer.

images/r06SOB07N.PNG

 Vérifiez la donnée avec la valeur de la cellule P1131 : elle est bien passée à FAUX.

Rechercher une donnée

L’agent Cruzel recherche une de ses ventes sur laquelle s’est glissée une erreur : le client avait un parking. Il se souvient que c’était la vente d’une villa d’environ 250 000 €. Utilisez l’outil pour effectuer la recherche

 Affichez à nouveau le formulaire en cliquant sur le bouton Formulaire dans l’onglet Données. Le formulaire apparaît.

 Cliquez sur le bouton Critères.

 Saisissez les informations qui permettent de trouver ces deux lignes :

images/r06SOB09N.PNG

 Après avoir vérifié que vous avez bien trouvé l’enregistrement, changez la valeur du champ Avec parking de FAUX à VRAI puis cliquez sur le bouton Fermer.

images/r06SOB08N.PNG

Créer une enquête partagée via OneDrive et la diffuser...