Voici le quatrième billet de blog d’une série de sept destinés à 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 est une adaptation de l’exemple du billet précédent où nous avons utilisé 1 fichier pour transférer des données vers un fichier cible. Cette fois-ci, nous allons extraire des données de différents fichiers. Il faut donc se référer à un répertoire. Le nom du répertoire peut être inclus dans le code ou sélectionné par l’utilisateur final via une boîte de dialogue.
Dans un premier temps, nous allons examiner comment ouvrir plusieurs fichiers d’un répertoire à la suite les uns des autres, fermer correctement les fichiers après le transfert des données et nettoyer les variables objet utilisées. Ensuite, nous prêtons attention au transfert des données dont nous avons posé les bases dans le troisième billet de blog.
Nous commençons par sélectionner le répertoire dans lequel se trouvent les fichiers que nous allons ouvrir. Le premier exemple sera basé sur un nom de répertoire fixe. Le deuxième exemple donne à l’utilisateur du code la possibilité de choisir un répertoire.
Vous commencez par créer 3 variables. 1 pour le répertoire, 1 pour le nom du fichier que vous ouvrez à chaque fois et 1 dans lequel vous stockez le classeur.
Dim myfolder As String
Dim myFile As String
Dim wbData As Workbook
Via la fonction « Dir » intégrée, vous pouvez transférer un fichier ou un répertoire vers une variable. Si le répertoire ne contient pas de contenu demandé, vous obtiendrez une chaîne de caractères vide.
myfolder = « C:\Users\Pibo »
myFile = Dir(myfolder & « *.xlsx »)
Dans cet exemple, le caractère générique « * » est utilisé. Pendant l’exécution de cette commande, le premier fichier qui répond à la demande sera transféré à la variable. Nous attirons votre attention sur le fait qu’il ne s’agit que du nom du fichier. Vous utilisez cette variable pour ouvrir le fichier plus tard.
Si vous l’intégrez dans une boucle, voici comment cela se présente.
Do While myFile <> <> « »
Set wbData = Workbooks.Open(myfolder & « \ » & myFile)
‘VBA Coding
wbData.Close False
Set wbData = Nothing
‘VBA Coding
myFile = Dir
Loop
La commande « myFile = Dir » retournera le fichier suivant du répertoire à la variable. Cela peut être comparé à la commande « Next » dans une boucle « for-next ». La boucle s’arrête dès qu’il n’y a plus de fichier (myfile = “”).
Astuce: Essayez de fermer le fichier ouvert le plus rapidement possible. Si vous voulez effectuer des opérations sur les données, il est préférable de commencer par copier les données dans le fichier cible, puis de fermer votre fichier de données et d’effectuer les opérations dans le fichier cible.
Nous complétons le code à l’aide de 3 variables supplémentaires et de l’explication que vous pouvez trouver dans le blog précédent de cette série. Dans l’exemple ci-dessous, nous copions toutes les données du fichier ouvert dans le fichier cible. À cet effet, nous devons à chaque fois également déterminer le bon numéro de ligne pour y copier les nouvelles données.
Sub OpenMultipleFiles()
Dim fldr As FileDialog
Dim myfolder As String
Dim myFile As String
Dim wbData As Workbook
Dim wbSource As Workbook
Dim wsSource As Worksheet
Dim lngRow As Long
Application.ScreenUpdating = False
Set wbSource = ActiveWorkbook
Set wsSource = wbSource.Worksheets(1)
myfolder = « C:\Users\Pibo »
myFile = Dir(myfolder & « \*.xlsx »)
lngRow = wsSource.Cells(1, 1).CurrentRegion.Rows.Count + 1
Do While myFile <> « »
Set wbData = Workbooks.Open(myfolder & « \ » & myFile)
wbData.Sheets(1).Cells(1, 1).CurrentRegion.Copy wsSource.Cells(lngRow, 1)
wbData.Close False
Set wbData = Nothing
lngRow = wsSource.Cells(1, 1).CurrentRegion.Rows.Count + 1
myFile = Dir
Loop
Set wsSource = Nothing
Application.ScreenUpdating = True
End Sub
Dans cet exemple, j’ai ouvert 14 fichiers identiques avec 264 cellules de données chacun. Le code ci-dessus a environ besoin de 6 secondes sur mon appareil. Les fichiers ont une taille de 9 Ko. La majorité du temps perdu est liée à l’ouverture physique des fichiers. Dans l’exemple, cela prend aussi longtemps que l’ouverture du fichier lui-même. Et cela se produit 14 fois.
Dans le cinquième billet de blog de cette série, nous apprendrons comment enregistrer des données dans un ‘recordset’, un jeu d’enregistrements. Nous vous montrerons ensuite dans le sixième billet comment vous pouvez utiliser ce jeu d’enregistrements pour extraire des données d’un fichier sans l’ouvrir physiquement.
Petit plus : Si vous ne pouvez pas inclure un répertoire fixe dans votre code et que vous voulez laisser le choix à l’utilisateur, vous pouvez appliquer l’exemple ci-dessous.
Dim fldr As FileDialog
Dim myfolder As String
Dim myFile As String
Set fldr = Application.FileDialog(msoFileDialogFolderPicker)
With fldr
.Title = « Select a Folder »
.AllowMultiSelect = False
.InitialFileName = « C:\Users\Pibo »
If .Show <> -1 Then Exit Sub
myfolder = .SelectedItems(1)
End With
myFile = Dir(myfolder & « \*.xlsb »)
Le ‘FileDialog FolderPicker’ est une boîte de dialogue similaire à ‘File Open’ et est spécifiquement utilisée pour sélectionner un répertoire. L’utilisateur indique un répertoire (ne pas ouvrir) et valide avec « ok ».
Les autres propriétés du code :
- ‘AllowMultiselect’ : la valeur False indique qu’un seul répertoire peut être choisi.
- ‘InitialFileName’ : renvoie au répertoire affiché dans la boîte de dialogue.
- ‘Show’ : si l’utilisateur décide de cliquer sur ‘annuler’, show = true.
- ‘SelectedItems(1)’ : Le nom du premier élément de la sélection (ici un seul répertoire) est enregistré dans la variable « myfolder ».
Prochain billet de blog : Enregistrer les données dans un jeu d’enregistrements (5/7).
Si vous avez contracté le virus « Excel » voire « Power BI » après avoir lu ce billet de blog: n’hésitez pas à consulter notre offre de formations Excel ou Power BI.
