VBA/SQL vs Power Query : deux solutions complémentaires

Posté par le

Power Query, module intégré à Excel, prépare les données brutes (extraction, la transformation et le chargement ou ETL) en vue d’une analyse plus approfondie des données dans Excel. Power Query a beaucoup d’adeptes (dont moi), notamment ceux qui préconisent le no-code. En effet, cet outil ne nécessite pas de connaissance en programmation pour retraiter les données.

BG_4ex

Toutefois, dans certains cas Power Query n’offre pas de réponse à nos besoins… En tous cas, je ne suis pas parvenu dans le cas présent à trouver une solution à mon problème : obtenir une balance comparative sur quatre exercices… La difficulté réside dans le fait de lier le plan de comptes (calculé à partir des quatre balances) à l’aide de jointures aux quatre balances.

Heureusement, j’ai trouvé une solution avec un peu de VBA et de SQL :

Requete_SQL = "SELECT [PLAN_COMPTES].CodeEntité, [PLAN_COMPTES].CompteNum, [PLAN_COMPTES].CompteLib, [_BG_N].Solde As SoldeN, [_BG_N1].Solde As SoldeN1, SoldeN-SoldeN1 As VarAbs, IIf(SoldeN1<>0,VarAbs/SoldeN1,0) As VarRel, [_BG_N2].Solde As SoldeN2, [_BG_N3].Solde As SoldeN3, "
Requete_SQL = Requete_SQL & "[_BG_N].SoldeSup90j As SoldeSup90j_N, [_BG_N].EF_LibelléEF As LibelléEF_N, [_BG_N].RubEF As RubEF_N, [_BG_N1].RubEF As RubEF_N1, [_BG_N].Anomalies As Anomalies_N, CycleCode, "
Requete_SQL = Requete_SQL & "IIf([_BG_N].Cpte3 Is Not Null,[_BG_N].Cpte3, IIf([_BG_N1].Cpte3 Is Not Null,[_BG_N1].Cpte3, IIf([_BG_N2].Cpte3 Is Not Null,[_BG_N2].Cpte3,[_BG_N3].Cpte3))) As Cpte3, "
Requete_SQL = Requete_SQL & "IIf([_BG_N].Cpte2 Is Not Null,[_BG_N].Cpte2, IIf([_BG_N1].Cpte2 Is Not Null,[_BG_N1].Cpte2, IIf([_BG_N2].Cpte2 Is Not Null,[_BG_N2].Cpte2,[_BG_N3].Cpte2))) As Cpte2, "
Requete_SQL = Requete_SQL & "IIf([_BG_N].Cpte1 Is Not Null,[_BG_N].Cpte1, IIf([_BG_N1].Cpte1 Is Not Null,[_BG_N1].Cpte1, IIf([_BG_N2].Cpte1 Is Not Null,[_BG_N2].Cpte1,[_BG_N3].Cpte1))) As Cpte1, "
Requete_SQL = Requete_SQL & "[_BG_N].Secteur, [_BG_N].Périmètre "
Requete_SQL = Requete_SQL & "FROM ((((("
'--- PLAN DE COMPTES AVEC LIBELLE DE COMPTE
Requete_SQL = Requete_SQL & "SELECT [PLAN_COMPTES_COMPTES].CodeEntité, [PLAN_COMPTES_COMPTES].CompteNum, [PLAN_COMPTES_COMPTES].Cpte3, "
'--- CompteLib
Requete_SQL = Requete_SQL & "IIf([_BG_N].CompteLib Is Not Null,[_BG_N].CompteLib, "
Requete_SQL = Requete_SQL & "IIf([_BG_N1].CompteLib Is Not Null,[_BG_N1].CompteLib, "
Requete_SQL = Requete_SQL & "IIf([_BG_N2].CompteLib Is Not Null,[_BG_N2].CompteLib, "
Requete_SQL = Requete_SQL & "[_BG_N3].CompteLib))) AS CompteLib "
'--- PLAN_DE_COMPTES_COMPTES (CompteNum sans CompteLib)
Requete_SQL = Requete_SQL & "FROM ((("
'---
Requete_SQL = Requete_SQL & "(SELECT CodeEntité, CompteNum, Cpte3 FROM ("
Requete_SQL = Requete_SQL & "SELECT CodeEntité, CompteNum, Cpte3, Cpte1 FROM [" & CheminBDD & BDD_N & "].[_BG] "
Requete_SQL = Requete_SQL & "UNION ALL "
Requete_SQL = Requete_SQL & "SELECT CodeEntité, CompteNum, Cpte3, Cpte1 FROM [" & CheminBDD & BDD_N1 & "].[_BG] "
Requete_SQL = Requete_SQL & "UNION ALL "
Requete_SQL = Requete_SQL & "SELECT CodeEntité, CompteNum, Cpte3, Cpte1 FROM [" & CheminBDD & BDD_N2 & "].[_BG] "
Requete_SQL = Requete_SQL & "UNION ALL "
Requete_SQL = Requete_SQL & "SELECT CodeEntité, CompteNum, Cpte3, Cpte1 FROM [" & CheminBDD & BDD_N3 & "].[_BG]) "
'--- CRITERES Cpte1 <>8 et 9
Requete_SQL = Requete_SQL & "WHERE Cpte1<>""9"" AND Cpte1<>""8"" "
'--- REGROUPEMENT
Requete_SQL = Requete_SQL & "GROUP BY CodeEntité, CompteNum, Cpte3) As PLAN_COMPTES_COMPTES "
'------ LEFT JOIN
Requete_SQL = Requete_SQL & "LEFT JOIN [" & CheminBDD & BDD_N & "].[_BG] As [_BG_N] ON ([_BG_N].CompteNum = [PLAN_COMPTES_COMPTES].CompteNum) AND ([_BG_N].CodeEntité = [PLAN_COMPTES_COMPTES].CodeEntité)) "
Requete_SQL = Requete_SQL & "LEFT JOIN [" & CheminBDD & BDD_N1 & "].[_BG] As [_BG_N1] ON ([_BG_N1].CompteNum = [PLAN_COMPTES_COMPTES].CompteNum) AND ([_BG_N1].CodeEntité = [PLAN_COMPTES_COMPTES].CodeEntité)) "
Requete_SQL = Requete_SQL & "LEFT JOIN [" & CheminBDD & BDD_N2 & "].[_BG] As [_BG_N2] ON ([_BG_N2].CompteNum = [PLAN_COMPTES_COMPTES].CompteNum) AND ([_BG_N2].CodeEntité = [PLAN_COMPTES_COMPTES].CodeEntité)) "
Requete_SQL = Requete_SQL & "LEFT JOIN [" & CheminBDD & BDD_N3 & "].[_BG] As [_BG_N3] ON ([_BG_N3].CompteNum = [PLAN_COMPTES_COMPTES].CompteNum) AND ([_BG_N3].CodeEntité = [PLAN_COMPTES_COMPTES].CodeEntité) "
Requete_SQL = Requete_SQL & ") As PLAN_COMPTES "
'--- LEFT JOIN
Requete_SQL = Requete_SQL & "LEFT JOIN [" & CheminBDD & BDD_N & "].[_BG] As [_BG_N] ON ([_BG_N].CompteNum = [PLAN_COMPTES].CompteNum) AND ([_BG_N].CodeEntité = [PLAN_COMPTES].CodeEntité)) "
Requete_SQL = Requete_SQL & "LEFT JOIN [" & CheminBDD & BDD_N1 & "].[_BG] As [_BG_N1] ON ([_BG_N1].CompteNum = [PLAN_COMPTES].CompteNum) AND ([_BG_N1].CodeEntité = [PLAN_COMPTES].CodeEntité)) "
Requete_SQL = Requete_SQL & "LEFT JOIN [" & CheminBDD & BDD_N2 & "].[_BG] As [_BG_N2] ON ([_BG_N2].CompteNum = [PLAN_COMPTES].CompteNum) AND ([_BG_N2].CodeEntité = [PLAN_COMPTES].CodeEntité)) "
Requete_SQL = Requete_SQL & "LEFT JOIN [" & CheminBDD & BDD_N3 & "].[_BG] As [_BG_N3] ON ([_BG_N3].CompteNum = [PLAN_COMPTES].CompteNum) AND ([_BG_N3].CodeEntité = [PLAN_COMPTES].CodeEntité)) "
Requete_SQL = Requete_SQL & "LEFT JOIN [" & CheminPAD & BDD_PAD & "].[BG_Cycles] ON ([BG_Cycles].Cpte3 = [PLAN_COMPTES].Cpte3) "
'--- TRI DES BALANCES
Requete_SQL = Requete_SQL & "ORDER BY [PLAN_COMPTES].CodeEntité, [PLAN_COMPTES].CompteNum;"

Comme quoi, il ne faut pas opposer les outils informatiques mais bien en tirer le meilleur de chacun en toute circonstance ;-).

Share Button
The following two tabs change content below.
Mon profil TwitterMon profil FacebookMy LinkedIn profile

Benoît RIVIERE

Après seize années passées en cabinet d’expertise-comptable et de commissariat aux comptes (où j’ai exercé comme expert-comptable et chef de mission audit), j’ai pris le poste de directeur comptable d’un groupe de distribution automobile en novembre 2014. Au cours de ma carrière, j’ai acquis une expérience significative en audit et en exploitation des systèmes d’information (analyse de données, automatisation des tâches, programmation informatique) au service de la production des comptes annuels et consolidés. C’est cette expérience personnelle et ma passion pour l’informatique que je partage sur ce blog. Mon CV / Réalisations personnelles et projets informatiques / Ma collection / Me contacter
Mon profil TwitterMon profil FacebookMy LinkedIn profile

Derniers articles parBenoît RIVIERE (voir tous)

Autour du même sujet :

  1. Programmation (VBA, SQL…) : les opérateurs
  2. SGBDR ACCESS : Programmer des requêtes SQL dans des macros VBA
  3. S’initier à l’analyse de données et l’automatisation des tâches
  4. Audit des comptes consolidés : validation de l’imputation des comptes individuels dans les états financiers consolidés
  5. EXCEL, VBA : importer un fichier texte dans Excel (cas pratique)

Laisser un commentaire

Votre adresse e-mail ne sera pas publiée. Les champs obligatoires sont indiqués avec *

Ce site utilise Akismet pour réduire les indésirables. En savoir plus sur comment les données de vos commentaires sont utilisées.