Analysez vos données avec VBA

Les deux parties précédentes nous ont permis d’appréhender l'automatisation d’une analyse. Nous sommes partis des extractions brutes que nous avons transformées et concaténées, puis analysées.

Dans cette dernière partie, nous allons nous attarder sur deux outils que nous n’avons pas encore vus, mais qui sont souvent très utilisés dans les entreprises. La bonne nouvelle, c’est que le VBA peut également interagir avec ! Il s'agit d’Access et de Power Query.

Dans ce premier chapitre, nous allons voir ensemble pourquoi il est parfois préférable d’utiliser une base de données Access plutôt que des fichiers Excel. Nous verrons que nous allons pouvoir nous connecter à la base de données en VBA pour ajouter des données à certaines tables ou encore récupérer le résultat des requêtes SQL que nous aurons au préalable créées dans Access.

Appréhendez le concept de base de données relationnelle

Commençons par définir le concept même d’une base de données.

Reprenons le cas de notre agence immobilière qui a des données sur Marseille, Toulouse et Montpellier, où chaque ligne de la base de données correspond à une vente.

Posons-nous maintenant la question : qu'est-ce qu’il se passe si un bien est vendu deux fois ? L’intégralité des informations est rentrée dans une nouvelle ligne avec les mêmes informations (le bien n’a pas changé d’adresse), sauf la valeur du bien, qui a pu changer, et la date de la signature de l’acte de vente.

Si nous souhaitons optimiser nos données, nous n’avons pas besoin de stocker l’adresse une deuxième fois. C’est pourquoi nous avons créé une relation qui nous permet de garder l’information de la première vente, sans nous coûter plus de stockage pour la seconde vente.

Pour des applications vraiment gourmandes en ressources, on va souvent se tourner vers des bases de données performantes comme Oracle, MySQL, Microsoft SQL Server, PostGreSQL. Pour des applications plus simples, on utilise souvent Access, qui est directement inclus dans la suite Microsoft, ou des logiciels comme SQLite.

Créez des bases de données Microsoft Access

Nous allons donc créer cette base de données ensemble.

Nous voilà donc maintenant en présence d’une base de données, mais sans relation encore.

Pour créer la relation, Access a besoin de savoir quelles sont les colonnes qu’il doit utiliser. Dans notre cas, c’est la colonne “ID_VF”. (VF correspondant à Valeur Foncière.)

La table “Adresse” contient les adresses des biens et la table “VF” contient seulement la date, la nature de la vente et la valeur de cette vente. Ainsi, si un bien est vendu plusieurs fois, il ne va pas créer de doublon dans les adresses (cela permet d’optimiser les données).

Pour faire la jointure, il me suffit de prendre la colonne “ID_VF” de la table “Adresse” et de la mettre sur la ligne "ID_VF" de la table “VF”.

Si vous double cliquez sur la jointure, vous obtenez les informations suivantes :

Nous avons la table de gauche avec le nom de la colonne ("ID_VF") qui est jointe avec la table de droite par la colonne du même nom ("ID_VF").

Maintenant que notre base de données est prête et fonctionnelle, nous allons pouvoir l’exploiter.

Pour créer une requête, il y a deux possibilités :

• l’éditeur de requête Access ;

• le SQL.

Nous allons voir dans un premier temps l’éditeur de requête Access, qui permet de réaliser des requêtes, sans faire de code SQL.

Pour ce faire, vous devez :

• aller dans l’onglet “Créer”, puis "Création de requêtes” ;

• dans cette nouvelle fenêtre avec vos deux tables, sélectionner les deux et cliquer sur “Ajouter” ;

• cliquer sur “Fermer”.

Pour la création de la requête, je vous invite à suivre la vidéo ci-dessous :

Bien vu ! L’éditeur de requête Access permet de faire des requêtes simples. Quand on a besoin de faire des requêtes plus compliquées ou qu'on est habitué au SQL, c’est plus facile de l’écrire directement.

Par exemple, la requête que nous avons faite plus haut ne prend que quelques lignes.

SELECT Adresse.commune, count(VF.Valeur_fonciere)
FROM Adresse INNER JOIN VF ON Adresse.ID_VF = VF.[ID_VF]
GROUP BY Adresse.commune;

Dans notre cas, nous avons créé une base de données sur Access pour pouvoir utiliser facilement le VBA et récupérer des tables ou faire des calculs avec nos données. C’est une base de données qui a l’avantage d’avoir une interface graphique simple et qui permet, sans savoir écrire de SQL, de faire des requêtes (des calculs, des filtres, etc.) dans notre base de données.

Utilisez vos connaissances en VBA pour interagir avec Access

Maintenant que notre base de données est fonctionnelle, nous allons pouvoir l’utiliser en VBA.

Tout comme les autres applications de la suite Office, Access est accessible en VBA via Excel. (On peut également créer des macros directement dans Access.)

Nous allons donc repartir dans le VBE pour récupérer les données d’une table.

Pour cela, nous allons utiliser la méthode "OpenDatabase" de l’objet "Workbook".

Voici le code associé :

Sub extract_table()

Dim sTableName As String
Dim oWbk As Workbook
Dim sWbkName As String

'Ouvre la table sélectionnée dans un nouveau Workbook
sTableName = "Adresse"
Set oWbk = Workbooks.OpenDatabase(Filename:="I:\BDD Access\BDD - Reporting.accdb", CommandText:=Array(sTableName), CommandType:=xlCmdTable)

'Enregistre et ferme le fichier
sWbkName = "I:\BDD Access\test.xlsx"
oWbk.SaveAs sWbkName
oWbk.Close

End Sub

Comme vous pouvez le voir, c’est assez simple d’extraire des données d’une table dans Access. Vous pouvez également exécuter des macros VBA qui vont lancer une requête et transférer le résultat directement dans Excel ou encore extraire des données, les traiter et les renvoyer dans une nouvelle table sur Access.

Comme nous l'avons vu depuis le début de ce cours, il suffit de chercher les objets disponibles et leur fonctionnement pour interagir avec tous les logiciels.

À vous de jouer !

Toutes les personnes de l’entreprise sont contentes de votre travail. Vous avez réussi à automatiser le reporting et l’analyse. Plusieurs personnes souhaitent maintenant utiliser votre travail pour des analyses. C’est pourquoi votre chef vous demande désormais de créer une base de données SQL et de charger votre reporting directement sur cette base. Cela permettra, tous les matins, à tous les analystes de se connecter et d’aller chercher vos données pour faire leurs analyses également.

Votre chef vous demande donc de :

• créer une base de données ;

• charger les données agrégées directement avec du code VBA.

En résumé

• Les bases de données sont utilisées pour stocker des données de façon plus structurée.

• Une base de données est composée de plusieurs tables.

• Les tables sont liées entre elles par une jointure qui permet de faire le lien entre les tables.

• Le VBA permet d’extraire une table, de lancer une requête ou de charger des données.

Nous avons vu dans ce chapitre comment utiliser une base de données comme Access avec le VBA Excel. Nous allons voir dans le prochain chapitre qu’il existe des outils intégrés à Excel qui permettent de transformer les données sans utiliser de code.