Voici le septième et dernier billet de blog d’une série qui vise à 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
Ce billet de blog examine la possibilité de fusionner 2 tableaux et de les inclure dans un jeu d’enregistrements. Les deux tableaux doivent contenir au moins un champ commun. Celui-ci est utilisé pour établir un lien entre les 2 zones de données. Nous montrons également la possibilité de passer les données en revue et de les adapter.
Le principe d’un jeu d’enregistrements ou recordset a déjà été examiné dans les billets de blog précédents. Nous ne devons plus que nous concentrer sur la manière d’écrire la requête. Ensuite, nous passerons en revue et évaluerons le jeu d’enregistrements, après quoi le résultat adapté sera sauvegardé.
Dans le premier exemple, les données fusionnées seront copiées dans une nouvelle feuille de calcul. Dans le second exemple, nous traiterons les enregistrements séparément.
La représentation suivante montre les données que nous utilisons et leur résultat.
Chaque image de l’exemple se trouve sur une feuille de calcul différente. Le cadre de droite est le résultat du code qui se retrouve sur une feuille de calcul vide existante.
Remarque: Avant toute nouvelle exécution, veillez à toujours effacer les résultats précédents ou à inclure cette action dans le code pour éviter une intervention manuelle.
Sub CopyWithADODB()
‘ Reference to: Microsoft ActiveX Data Objects 6.1 Library
Dim myConnection As String
Dim RS As ADODB.Recordset
Dim mySQL As String
Dim strPath As String
Dim wsMain As Worksheet
Debug.Print Now
Set wsMain = Worksheets(« Sheet3 »)
Application.ScreenUpdating = False
strPath = ActiveWorkbook.FullName
myConnection = « Provider=Microsoft.ACE.OLEDB.12.0; » & _
« Data Source= » & strPath & « ;Extended Properties=Excel 12.0 »
mySQL = « SELECT [Sheet1$].[Code],[Sheet1$].[Price] , [Sheet2$].[Units], [Sheet2$].[Tax] » & _
« FROM [Sheet1$] INNER JOIN [Sheet2$] ON [Sheet1$].[Code] = [Sheet2$].[Code] »
Set RS = New ADODB.Recordset
RS.Open mySQL, myConnection, adOpenForwardOnly, adLockOptimistic
wsMain.Range(« A1 »).CopyFromRecordset RS
RS.Close
Set RS = Nothing
Application.ScreenUpdating = True
Debug.Print Now
End Sub
Le code SQL se présente comme suit :
« SELECT [Sheet1$].[Code],[Sheet1$].[Price] , [Sheet2$].[Units], [Sheet2$].[Tax] » & _
« FROM [Sheet1$] INNER JOIN [Sheet2$] ON [Sheet1$].[Code] = [Sheet2$].[Code] »
Si vous observez attentivement la syntaxe, vous reconnaîtrez probablement la syntaxe utilisée par Excel pour les formules dans les tableaux. Dans ce cas, nous utilisons toutefois le nom de la feuille de calcul et le nom (titre) de la colonne au lieu du nom du tableau et du nom de la colonne.
C’est une autre solution très intéressante pour les utilisateurs qui ne sont pas encore capables ou qui ne veulent pas encore passer à Power BI. Avec une bonne connaissance du langage de requête SQL, vous disposez d’un large éventail de possibilités pour fusionner et analyser des données. Cette méthode est aussi ancienne qu’Excel.
Dans l’exemple suivant, nous allons multiplier l’unité par le prix pour utiliser le résultat de la multiplication au lieu des deux valeurs de base séparément.
Remarque: Il y a plusieurs solutions possibles, mais, dans ce blog, j’ai choisi de faire une boucle (loop) sur le jeu d’enregistrements et d’écrire le résultat du calcul dans Excel. Une autre solution pourrait prendre la forme d’un jeu d’enregistrements avec une colonne supplémentaire pour stocker l’opération. Après avoir parcouru le jeu d’enregistrements, vous pouvez en coller la totalité dans Excel. Ou vous écrasez une colonne existante avec la solution pour éviter une colonne supplémentaire. De cette façon, vous perdrez toutefois vos données de base originales.
Maintenant, nous devons parcourir le jeu d’enregistrements et sauvegarder ligne par ligne dans la feuille de calcul Excel. Une variable supplémentaire est donc absolument nécessaire pour suivre ce numéro de ligne.
Sub UpdateRecordSet()
‘ Reference to: Microsoft ActiveX Data Objects 6.1 Library
Dim myConnection As String
Dim RS As ADODB.Recordset
Dim mySQL As String
Dim strPath As String
Dim wsMain As Worksheet
Dim lngRow As Long
Set wsMain = Worksheets(« Sheet3 »)
Application.ScreenUpdating = False
strPath = ActiveWorkbook.FullName
myConnection = « Provider=Microsoft.ACE.OLEDB.12.0; » & _
« Data Source= » & strPath & « ;Extended Properties=Excel 12.0; »
mySQL = « SELECT [Sheet1$].[Code],[Sheet1$].[Price] , [Sheet2$].[Units], [Sheet2$].[Tax] » & _
« FROM [Sheet1$] INNER JOIN [Sheet2$] ON [Sheet1$].[Code] = [Sheet2$].[Code] » ‘* FROM “ & _
“[Sheet1$A1:SF10000] »
Set RS = New ADODB.Recordset
RS.Open mySQL, myConnection, adOpenForwardOnly, adLockOptimistic
lngRow = 1
Do Until RS.EOF
wsMain.Cells(lngRow, 1) = RS.Fields(0)
wsMain.Cells(lngRow, 2) = RS.Fields(1) * RS.Fields(2)
wsMain.Cells(lngRow, 3) = RS.Fields(3)
lngRow = lngRow + 1
RS.MoveNext
Loop
RS.Close
Set RS = Nothing
Application.ScreenUpdating = True
End Sub
C’était donc le dernier billet de blog d’une série de 7 qui vous ont montré les différentes possibilités de copier et de déplacer de plus grands blocs de données et les gains de temps qui y sont associés.
Gageons que vous pourrez utiliser les exemples développés comme base dans la suite de vos activités et arriver ainsi à quelques solutions élégantes en Excel.
Si vous souhaitez approfondir la question, vous êtes toujours les bienvenus dans une de nos formations Excel ouvertes. Nous espérons vous y retrouver très bientôt !
