Les sources de données Excel avec VBA

Dans les versions les plus récentes d’Excel, nous pouvons ajouter des sources de données à partir d’une multitude de types différents (texte, classeur, web, ODBC, base de données SQL Server, …).

Pour ce billet, nous nous baserons sur un fichier texte assez basique qui contient une ligne d’entête et des lignes de données :

Fichier coordonnees_points.txt

x;y
1;20
15;5
3;2

Création depuis Excel

Pour ajouter une source de données depuis Excel, il faut se rendre dans l’onglet "Données".

Comme nous voulons importer un fichier texte, nous choisissons le type adéquate depuis le groupe "Récupérer et transformer des données" du ruban :

Un explorateur de fichiers s’ouvre alors nous permettant de sélectionner notre fichier.

Une fois ce dernier chargé, une fenêtre d’aperçu apparaît nous permettant, entres autres, de choisir un délimiteur si celui-ci n’est pas correctement détecté ainsi que d’accéder à la transformation des données.

Pour notre exemple, nous pouvons nous contenter de charger les données via le bouton approprié.

Les données sont chargées et nous pouvons remarquer qu’une liste de données a été créée dans un nouvel onglet (n°1 dans l’image ci-dessus). Tous les deux portent le nom du fichier choisi. Comme nous sommes placés dans ce tableau, nous voyons également deux onglets spécifiques au tableau et à la requête (n°2 ci-dessus). Enfin, tout à droite se trouve un panel "Requêtes et connexions" (n°3 dans l’image ci-dessus) qui contient notre nouvelle requête. La requête créée porte elle aussi le nom du fichier.

Création en VBA

Je vous avoue que je n’ajoute pas souvent des sources de données en VBA, parce que c’est plus simple et plus rapide depuis Excel, et que ce n’est pas une opération que j’ai déjà eue à devoir faire en masse.

Toutefois, nous pouvons imaginer devoir le faire dynamiquement depuis un programme alors voici ce qui dit l’enregistreur de macros pour la même chose :

Option Explicit

Sub Macro3()
'
' Macro3 Macro
'
'
    ActiveWorkbook.Queries.Add Name:="coordonnees_points", Formula:= _
        "let" & Chr(13) & "" & Chr(10) & "    Source = Csv.Document(File.Contents(""C:\Users\Utilisateur\Desktop\Rédactions\coordonnees_points.txt""),[Delimiter="";"", Columns=2, Encoding=1252, QuoteStyle=QuoteStyle.None])," & Chr(13) & "" & Chr(10) & "    #""En-têtes promus"" = Table.PromoteHeaders(Source, [PromoteAllScalars=true])," & Chr(13) & "" & Chr(10) & "    #""Type modifié"" = Table.TransformColumnTypes(#""En-têtes promus"",{{""x"", Int64.Type}, {""y""," & _
        " Int64.Type}})" & Chr(13) & "" & Chr(10) & "in" & Chr(13) & "" & Chr(10) & "    #""Type modifié"""
    ActiveWorkbook.Worksheets.Add
    With ActiveSheet.ListObjects.Add(SourceType:=0, Source:= _
        "OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=coordonnees_points;Extended Properties=""""" _
     , Destination:=Range("$A$1")).QueryTable
        .CommandType = xlCmdSql
        .CommandText = Array("SELECT * FROM [coordonnees_points]")
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .PreserveColumnInfo = True
        .ListObject.DisplayName = "coordonnees_points"
        .Refresh BackgroundQuery:=False
    End With
    Range("G5").Select
End Sub

En analysant le code ci-dessus, nous pouvons voir qu’un objet de type requête (Queries) est ajouté puis qu’une liste de données est créée (ListObject). Cette dernière est un peu particulière puisque sa propriété QueryTable (représentant une table de requête) est définie et lit la requête.

C’est donc vers les tables de requête qu’il faudrait se pencher pour faire cette opération en VBA. À noter qu’une table de requête n’est pas forcément à écrire avec une liste de données (l’objet Range possède aussi cette propriété) et qu’il n’y a pas toujours besoin d’un objet requête (Queries) non plus.

Au cours de cette section, nous avons étudié les moyens d’ajouter une source de données.

Comme indiqué en introduction, nous pouvons ajouter une étape de modifications (transformation, ajout de colonnes, …) pour les données lues.

Cela se fait via l’éditeur Power Query. Le but de ce billet n’est pas de vous présenter ce dernier, mais de voir la manière dont celui-ci s’insère dans l’utilisation des sources de données.

Pour modifier les données lues, il faut modifier la requête. Cela se fait soit depuis l’onglet, soit depuis le panel de droite, comme montré ci-dessous :

L’éditeur Power Query s’ouvre. Pour cet exemple, nous cliquons sur "Colonne personnalisée" de l’onglet "Ajouter une colonne". Ensuite, nous saisissons "Coordonnées" pour le nom de colonne et "(" & Number.ToText([x]) & ", " & Number.ToText([y]) & ")" pour la formule et validons.

Il ne nous reste maintenant plus qu’à fermer Power Query et charger le résultat obtenu :

Nous constatons que nos modifications ont bien été repercutées dans notre ListObject.

Cette section a montré comment Power Query était lié aux sources de données.

Les données d’origine sont susceptibles d’évoluer (ajout, suppression, modification).

En conséquence, il nous faudrait récupérer la nouvelle version de celles-ci. Pas de problème puisqu’une fois la connexion établie, nous n’avons qu’à l’actualiser !

Pour cette section, nous commençons par modifier le contenu de notre fichier texte ainsi :

Fichier coordonnees_points.txt

x;y
14;5
3;2
4;3

Actualisation

En VBA

Cette fois-ci, je présente l’approche VBA en premier, car c’est tout l’intérêt pour nos macros.

Il y a plusieurs moyens de procéder :

' Actualisation à partir de l'objet WorkbookConnection
ActiveWorkbook.Connections("Requête - coordonnees_points").Refresh
    
' Actualisation à partir de l'objet table de requête
Sheets("coordonnees_points").ListObjects("coordonnees_points").QueryTable.Refresh

' Actualisation pour tout le classeur
ActiveWorkbook.RefreshAll

Depuis Excel

Cela correspond aux éléments suivant dans l’interface :

Nous pouvons observer qu’en utilisant une de ces options, les données sont bien mises à jour.

Mode arrière-plan

Il est possible de définir le caractère bloquant ou non (respectivement synchrone et asynchrone) de l’actualisation soit via l’interface soit via un paramètre facultatif BackgroundQuery de Refresh.

Il peut être nécessaire de passer en synchrone pour attendre que les données soient bien chargées avant de faire d’autres opérations, en décochant cette case.

Événements

Les objets QueryTable possèdent deux événements possibles pour potentiellement annuler l’actualisation puis vérifier qu’elle s’est bien déroulée :

QueryTable_BeforeRefresh(Cancel As Boolean) ' Avant actualisation
QueryTable_AfterRefresh(Success As Boolean) ' Après actualisation

Pour les utiliser, il faut créer un module de classe et déclarer un objet QueryTable avec événements comme ceci :

Option Explicit

Private WithEvents mQTEvenement As QueryTable

Cela nous permet d’ajouter les procédures d’événement au moyen des zones d’objet et de procédures/événements :

Private Sub mQTEvenement_BeforeRefresh(Cancel As Boolean)
    MsgBox ("Avant !")
End Sub

Private Sub mQTEvenement_AfterRefresh(ByVal Success As Boolean)
    MsgBox ("Après !")
End Sub

Ensuite, il nous faut ajouter le liant entre la table de requête existante et la propriété en tant que variable membre :

Public Property Set qtEvenement(ByRef oQT As QueryTable)
    Set mQTEvenement = oQT
End Property

Pour résumer, cela donne pour cette classe :

Module de classe clsQTEvenement

Option Explicit

Private WithEvents mQTEvenement As QueryTable

Private Sub mQTEvenement_BeforeRefresh(Cancel As Boolean)
    MsgBox ("Avant !")
End Sub

Private Sub mQTEvenement_AfterRefresh(ByVal Success As Boolean)
    MsgBox ("Après !")
End Sub

Public Property Set qtEvenement(ByRef oQT As QueryTable)
    Set mQTEvenement = oQT
End Property

N’oublions pas l’instanciation de celle-ci et la liaison que nous ferons dans l’objet classeur pour cet exemple :

ThisWorkbook

Option Explicit

Dim oQTEvenement As clsQTEvenement

Private Sub Workbook_Open()
    Set oQTEvenement = New clsQTEvenement
    Set oQTEvenement.qtEvenement = Worksheets("coordonnees_points").ListObjects("coordonnees_points").QueryTable
End Sub

En relançant le classeur et en actualisant, nous nous rendons compte que ça fonctionne bien.

À travers cette section, nous avons vu l’actualisation des données.