Voici le cinquième billet de blog d’une série de sept visant à approfondir un certain nombre de techniques qui permettent d’accélérer la transformation des données dans Excel VBA.
- Copier des cellules avec VBA
- Ajouter des données à un tableau
- Ouvrir un fichier et transférer des données vers le fichier cible
- Ouvrir une série de fichiers et transférer les données vers le fichier cible
- Sauvegarder des données dans un jeu d’enregistrements
- Lire les données de plusieurs fichiers avec un jeu d’enregistrements sans ouvrir les fichiers
- Réaliser un jeu d’enregistrement, sur la base des données de 2 feuilles de calcul différentes
Dans ce billet de blog, nous examinons la technique qui peut être utilisée pour sauvegarder des données dans un « recordset » ou jeu d’enregistrements. Ce dernier est une matrice virtuelle de lignes et de colonnes supportée par un certain nombre de techniques destinées à y naviguer. Avec un jeu d’enregistrements, vous créez donc un tableau virtuel dans la mémoire.
La technique des jeux d’enregistrements est principalement connue dans les environnements de bases de données et est, par exemple, indispensable dans MS Access lors de la programmation. Les jeux d’enregistrements sont remplis de requêtes SQL. Un exemple simple est « Select * from Table » ou ‘Select * from Range’ dans Excel.
Ce billet de blog présente une application d’un jeu d’enregistrements, mais n’est pas destiné à examiner en détail les possibilités des jeux d’enregistrements.
La structure d’un jeu d’enregistrements se présente comme suit.
‘BeginOfFile’ (BOF) et ‘EndOfFile’ (EOF) sont des propriétés qui indiquent si vous êtes avant le premier ou après le dernier enregistrement du recordset. Si vous sauvegardez des données dans un jeu d’enregistrements et que les deux sont ‘true’, cela signifie que votre jeu d’enregistrements est vide.
Chaque ligne est constituée de plusieurs colonnes qui dépendent de la structure de données que vous avez ajoutée au jeu.
Il existe 2 sortes de jeux d’enregistrements utilisables. En principe, ce choix dépend du type de base de données que vous utilisez. Ce type est souvent déterminé par la préférence personnelle du développeur qui travaillera avec lui.
Vous pouvez utiliser un jeu d’enregistrements du type ADO ou DAO. La méthode de navigation, de lecture des données ou d’adaptation des données est presque identique pour les deux. Dans ce billet de blog, nous utilisons ADO.
Les jeux d’enregistrements constituent une solution de rechange à l’extraction de données d’un autre fichier ou d’une autre feuille de calcul. Vous pouvez sauvegarder des données dans un jeu d’enregistrements et fermer immédiatement votre fichier de données. Ensuite, vous effectuez des opérations ou des analyses sur le jeu d’enregistrements.
Remarque: Un jeu d’enregistrements extrait les données d’un fichier. Ce n’est qu’alors que le fichier est ouvert. Cette méthode présente l’avantage que vous n’avez pas à suivre le fichier ultérieurement. Le fichier ne sera pas affiché dans le gestionnaire de tâches et en tant que développeur vous n’avez pas besoin de code pour fermer le fichier.
Dans ce blog, nous vous présentons quelques exemples où un jeu d’enregistrements est utilisé pour collecter et copier des données. Dans le premier exemple, nous ouvrons un grand fichier avec 5 000 lignes et 100 colonnes.
Avant de pouvoir commencer avec votre code, vous devez d’abord établir une référence à la bibliothèque ADO. (Microsoft ActiveX Data Objects 6.1 Library)
Pour ce faire, procédez comme suit dans l’interface VBA :
Vous trouverez la référence par ordre alphabétique dans la liste. Après la sélection et lors de la réouverture de l’écran ‘references’, la sélection sera affichée en haut de l’écran.
Le code VBA se présente comme suit :
Sub CopyWithADODB()
‘ Reference to: Microsoft ActiveX Data Objects 6.1 Library
‘5000 Rows & 10000 Columns (File size 12,3 MB Type:Binary)
Dim myConnection As String
Dim RS As ADODB.Recordset
Dim mySQL As String
Dim strPath As String
Application.ScreenUpdating = False
strPath = « C:\Xylos\BLOG\SPEED_BLOG_EXCEL\ALotOfData.xlsb »
myConnection = « Provider=Microsoft.ACE.OLEDB.12.0; » & _
« Data Source= » & strPath & « ;Extended Properties=Excel 12.0 »
mySQL = « SELECT * FROM [Sheet1$A1: CV5000] »
Set RS = New ADODB.Recordset
RS.Open mySQL, myConnection, adOpenUnspecified, adLockUnspecified
Range(« A1 »).CopyFromRecordset RS
RS.Close
Set RS = Nothing
Application.ScreenUpdating = True
End Sub
En écrivant ce code, vous pouvez rencontrer beaucoup d’obstacles. Le numéro de version Excel (ici 12.0) doit être correct et l’orthographe de la commande SQL doit être sans erreur. La connexion peut mentionner toute une série de paramètres, ce qui donne aussi plus rapidement des messages d’erreur. Il est préférable de commencer sans ces paramètres et de les ajouter ensuite au fur et à mesure que vous continuez à tester.
Comme solution de remplacement à une zone définie, vous pouvez également lire une feuille de calcul complète :
‘mySQL = « SELECT * FROM[Sheet1$] »
La méthode ‘CopyFromRecordset’ colle le contenu total du jeu d’enregistrements dans la cellule spécifiée.
Dans l’exemple suivant, nous effectuons la même action en utilisant la méthode par défaut pour ouvrir un fichier via ‘Workbooks.open’. L’exécution de ce code prend aussi longtemps que la méthode de travail avec le jeu d’enregistrements. Mais en cas de répétition, l’exécution ne prend que 3 secondes. Il s’agit probablement d’une astuce Excel, car la structure graphique est encore en mémoire. Si vous fermez Excel et exécutez le code une nouvelle fois, vous aurez à nouveau besoin de 12 secondes la première fois.
Vous pourriez conclure ce qui précède que les deux possibilités prennent le même temps. Cependant, la méthode de travail avec un jeu d’enregistrements sera un peu plus rapide. Les deux méthodes ouvriront le fichier, mais la commande Excel ‘Workbooks.open’ devra également prendre en charge le travail graphique. Lorsque vous ouvrez plusieurs fichiers en utilisant la méthode recordset, le gain de temps augmente progressivement.
Sub OpenFileCopyToDestination()
Dim wbData As Workbook
Dim wbMain As Workbook
Debug.Print Now
Set wbMain = ActiveWorkbook
Application.ScreenUpdating = False
Set wbData = Workbooks.Open(« C:\Xylos\BLOG\SPEED_BLOG_EXCEL\ALotOfData2.xlsb »)
wbData.Sheets(1).Cells(1, 1).CurrentRegion.Copy wbMain.Sheets(« Sheet1 »).Cells(1, 1)
wbData.Close False
Application.ScreenUpdating = True
Debug.Print Now
End Sub
Dans le prochain billet du blog, nous utiliserons la même technique pour ouvrir plusieurs fichiers via une boucle et transférer à nouveau les données vers le fichier cible.
Prochain billet de blog : Lire les données de plusieurs fichiers avec un jeu d’enregistrements sans ouvrir les fichiers (6/7).
Suivez-vous ce blog et avez-vous le sentiment que vous avez besoin d’une sérieuse mise à niveau de vos connaissances Excel ? Dans ce cas, n’hésitez pas à suivre une de nos formations Excel.
