Ci-dessous, nous examinerons un programme dans Excel VBA qui boucle dans tous les classeurs fermés et des feuilles de calcul dans un répertoire, et affiche tous les noms.

Télécharger Book1.xlsx, Book2.xlsx, Book3.xlsx, Book4.xlsx et Book5.xlsx et les ajouter à « C: \ test \ »

Situation:

Files in a Directory Example

Ajoutez les lignes de code suivantes au bouton de commande:

  1. Tout d’abord, nous déclarons deux variables de type String, un objet feuille de calcul et deux variables de type entier.

Dim directory As String, fileName As String, sheet As Worksheet, i As Integer, j As Integer

  1. Pour éviter le scintillement de l’écran, désactiver la mise à jour de l’écran.

Application.ScreenUpdating = False

  1. Initialiser le répertoire variable. Nous utilisons la fonction Dir pour trouver la première .xl ?? fichier stocké dans ce répertoire.

directory = "c:\test\"

fileName = Dir(directory & ".xl??")

Note: (?) La fonction Dir prend en charge l’utilisation de plusieurs caractères (*) et le caractère unique des caractères génériques pour rechercher tous les différents types de fichiers Excel.

  1. La variable fileName détient maintenant le nom du premier fichier Excel dans le répertoire. Ajouter une boucle Do While.

Do While fileName <> ""

Loop

Ajouter les lignes de code suivants (en 5, 6, 7, 8 et 9) de la boucle.

  1. Initialiser les variables de type entier et ajouter le nom du fichier Excel à la première colonne de la ligne i.

i = i + 1

j = 2

Cells(i, 1) = fileName

  1. Il n’y a aucun moyen simple d’extraire des données (ou les noms de feuilles) à partir de fichiers Excel fermés. Par conséquent, nous ouvrons le fichier Excel.

Workbooks.Open (directory & fileName)

  1. Ajouter tous les noms de feuille du fichier Excel pour les autres colonnes de la ligne i.

For Each sheet In Workbooks(fileName).Worksheets

Workbooks("files-in-a-directory.xlsm").Worksheets(1).Cells(i, j).Value = sheet.Name

j = j + 1

Next sheet

  1. Fermez le fichier Excel.

Workbooks(fileName).Close

  1. La fonction Dir est une fonction spéciale. Pour obtenir les autres fichiers Excel, vous pouvez utiliser à nouveau la fonction Dir sans arguments.

fileName = Dir()

Remarque: en l’absence d’autres noms de fichiers correspondent, la fonction Dir renvoie une chaîne de longueur nulle ( « »). Par conséquent, Excel VBA quittera la boucle Do While.

  1. Allumez l’écran de mise à jour à nouveau (en dehors de la boucle).

Application.ScreenUpdating = True

  1. Tester le programme.

Résultat:

Files in a Directory in Excel VBA