Obtenir une liste de noms de fichiers à partir d'un dossier à l'aide d'Excel (avec et sans VBA)

Lors de mon premier jour dans une petite société de conseil, on m'a confié un projet à court terme de trois jours.Le travail est simple.

Il existe de nombreux dossiers sur un lecteur réseau, chacun contenant des centaines de fichiers.

J'ai dû suivre ces trois étapes :

1. Sélectionnez le fichier et copiez son nom.
2. Collez le nom dans une cellule dans Excel et appuyez sur Entrée.
3. Passez au fichier suivant et répétez les étapes 1 et 2.

Cela semble simple, non ?

C'est simple et très chronophage.

Ce que j'ai fait en trois jours peut être fait en quelques minutes si je connais la bonne technique.

Dans ce tutoriel, je vais vous montrer différentes façons de rendre l'ensemble du processus super rapide et super facile (avec et sans VBA).

Limitations des méthodes présentées dans ce tutoriel :En utilisant la technique ci-dessous, vous ne pourrez obtenir que les noms des fichiers de votre dossier personnel.Vous n'obtenez pas les noms des fichiers dans les sous-dossiers du dossier principal.Voici un moyen d'obtenir des noms de fichiers à partir de dossiers et de sous-dossiers à l'aide de Power Query

Utilisez la fonction FILES pour obtenir une liste de noms de fichiers à partir d'un dossier

entendu à ce sujetFonction FICHIERS?

Si vous ne le faites pas, ne vous inquiétez pas.

C'est à partir d'un tableur Excel de l'enfance (formules version 4).

Bien que cette formule ne fonctionne pas avec les cellules de feuille de calcul, elle fonctionne toujours avec les plages nommées.Nous utiliserons ce fait pour obtenir une liste des noms de fichiers du dossier spécifié.

Maintenant, supposons que vous ayez un fichier sur votre bureau appelé " Dossier de test " et vous souhaitez obtenir une liste des noms de fichiers pour tous les fichiers de ce dossier.

Voici les étapes qui vous donneront les noms de fichiers dans ce dossier :

1. Dans la cellule A1, entrez l'adresse complète du dossier suivie d'un astérisque (*)
   • Par exemple, si votre dossier se trouvait dans le lecteur C, l'adresse serait quelque chose comme
     C:\Utilisateurs\VOTRE NOM\Bureau\Dossier Test\*
   • Si vous ne savez pas comment obtenir l'adresse du dossier, utilisez la méthode suivante :
     • • Dans le dossier dont vous souhaitez obtenir le nom de fichier, créez un nouveau classeur Excel ou ouvrez un classeur existant dans le dossier et utilisez la formule suivante dans n'importe quelle cellule.Cette formule vous donnera l'adresse du dossier avec un astérisque (*) à la fin.Vous pouvez maintenant copier-coller (coller comme valeur) cette adresse dans n'importe quelle cellule du classeur (A1 dans cet exemple) où vous voulez le nom de fichier.

         =REPLACE(CELL("filename"),FIND("[",CELL("filename")),LEN(CELL("filename")),"*")

         [Si vous avez créé un nouveau classeur dans un dossier pour utiliser la formule ci-dessus et obtenir l'adresse du dossier, vous devrez peut-être le supprimer afin qu'il ne figure pas dans la liste des fichiers de ce dossier]

2. Allez dans l'onglet "Formules" et cliquez sur l'option "Définir les noms".
3. Dans la boîte de dialogue Nouveau nom, utilisez les détails suivants
   • Nom : FileNameList (n'hésitez pas à choisir le nom de votre choix)
   • Portée : Cahier d'exercices
   • Fait référence à : =FILES(Sheet1!$A$1)
4. Maintenant, pour obtenir une liste de fichiers, nous allons utiliser une plage nommée dans la fonction INDEX.Allez à la cellule A3 (ou à la cellule par laquelle vous voulez que la liste de noms commence) et entrez la formule suivante :

   =SIERREUR(INDEX(ListeNomFichier,LIGNE()-2),"")

5. Faites-le glisser vers le bas et il vous donnera une liste de tous les noms de fichiers dans le dossier

Vous voulez extraire des fichiers avec une extension spécifique ? ?

Si vous souhaitez obtenir tous les fichiers avec une extension spécifique, changez simplement l'astérisque avec cette extension de fichier.Par exemple, si vous ne voulez que le fichier excel, vous pouvez utiliser *xls* au lieu de *

L'adresse du dossier que vous devez utiliser est doncC:UsersSumitDesktopTest Dossier*xls*

De même, pour les fichiers de documents Word, utilisez *doc*

Comment cela marche-t-il?

La formule FILES récupère les noms de tous les fichiers avec l'extension spécifiée dans le dossier spécifié.

Dans la formule INDEX, nous donnons les noms de fichiers sous forme de tableau et utilisons la fonction ROW pour renvoyer les premier, deuxième, troisième, etc. noms de fichiers.

Notez que j'ai utiliséLIGNE()-2, puisque nous commençons sur la troisième ligne.Ainsi, lorsque le numéro de ligne est 4, ROW()-2 sera 1 pour la première instance, 2 pour la seconde, et ainsi de suite.

Obtenir une liste de tous les noms de fichiers d'un dossier à l'aide de VBA

Maintenant, je dois dire que la méthode ci-dessus est un peu compliquée (il y a beaucoup d'étapes).

Cependant, c'est bien mieux que de le faire manuellement.

Cependant, si vous êtes à l'aise avec VBA (ou si vous savez suivre les étapes exactes que je vais énumérer ci-dessous), vous pouvez créer une fonction personnalisée (UDF) qui peut facilement obtenir les noms de tous les fichiers.

Avantages de l'utilisation des fonctions définies par l'utilisateur (UDF)是Vous可以將fonctionEnregistrez dans des classeurs de macros personnels et réutilisez-les facilement sans avoir à répéter ces étapes encore et encore.Vous pouvez également créer des modules complémentaires et partager cette fonctionnalité avec d'autres.

Maintenant, laissez-moi d'abord vous donner le code VBA qui créera une fonction pour obtenir une liste de tous les noms de fichiers d'un dossier dans Excel.

Fonction GetFileNames(ByVal FolderPath As String) As Variant Dim Result As Variant Dim i As Integer Dim MyFile As Object Dim MyFSO As Object Dim MyFolder As Object Dim MyFiles As Object Set MyFSO = CreateObject("Scripting.FileSystemObject") Set MyFolder = MyFSO. GetFolder(FolderPath) Set MyFiles = MyFolder.Files ReDim Result(1 To MyFiles.Count) i = 1 For Each MyFile In MyFiles Result(i) = MyFile.Name i = i + 1 Next MyFile GetFileNames = Result End Function

Le code ci-dessus créera une fonction GetFileNames qui peut être utilisée dans la feuille de calcul (tout comme une fonction normale).

Où mettre ce code ?

Suivez les étapes ci-dessous pour copier ce code dans l'éditeur VB.

Comment utiliser cette fonction ?

Voici les étapes à suivre pour utiliser cette fonctionnalité dans une feuille de calcul :

• Dans n'importe quelle cellule, entrez l'adresse de dossier du dossier à partir duquel vous souhaitez répertorier les noms de fichiers.
• Dans la cellule où vous voulez la liste, saisissez la formule suivante (je l'ai saisie dans la cellule A3) :

  =SIERREUR(INDEX(GetFileNames($A$1),ROW()-2),"")

• Copiez et collez la formule dans la cellule ci-dessous pour obtenir une liste de tous les fichiers.

Notez que j'ai entré l'emplacement du dossier dans une cellule, puis dansObtenir les noms de fichiersCette cellule est utilisée dans une formule.Vous pouvez également coder en dur l'adresse du dossier dans la formule comme ceci :

=IFERREUR(INDEX(GetFileNames("C:\Users\VOTRE NOM\Desktop\Dossier Test"),ROW()-2),"")

Dans la formule ci-dessus, nous avons utilisé ROW()-2 et nous commençons par la troisième ligne.Cela garantit que lorsque je copie la formule dans la cellule ci-dessous, elle sera incrémentée de 1.Si vous entrez la formule dans la première ligne de la colonne, vous pouvez simplement utiliser ROW().

Comment fonctionne cette formule ?

La formule GetFileNames renvoie un tableau contenant les noms de tous les fichiers du dossier.

La fonction INDEX est utilisée pour lister un nom de fichier par cellule, en commençant par le premier.

La fonction SIERREUR est utilisée pour renvoyer un blanc au lieu de #REF !Erreur affichée lorsqu'une formule est copiée dans une cellule mais qu'il n'y a plus de noms de fichiers à lister.

Obtenez une liste de tous les noms de fichiers avec une extension spécifique à l'aide de VBA

La formule ci-dessus est utile lorsque vous souhaitez obtenir une liste de tous les noms de fichiers à partir d'un dossier dans Excel.

Mais que se passe-t-il si vous voulez simplement obtenir le nom d'un fichier vidéo, ou simplement un fichier Excel, ou simplement un fichier contenant un certain mot-clé.

Dans ce cas, vous pouvez utiliser une fonction légèrement différente.

Le code ci-dessous vous permettra d'obtenir tous les noms de fichiers contenant un mot-clé spécifique (ou une extension spécifique).

Fonction GetFileNamesbyExt(ByVal FolderPath As String, FileExt As String) As Variant Dim Result As Variant Dim i As Integer Dim MyFile As Object Dim MyFSO As Object Dim MyFolder As Object Dim MyFiles As Object Set MyFSO = CreateObject("Scripting.FileSystemObject") Set MyFolder = MyFSO.GetFolder(FolderPath) Set MyFiles = MyFolder.Files ReDim Result(1 To MyFiles.Count) i = 1 For Each MyFile In MyFiles If InStr(1, MyFile.Name, FileExt) <> 0 Then Result(i) = MyFile.Name i = i + 1 End If Next MyFile ReDim Preserve Result(1 To i - 1) GetFileNamesbyExt = Result End Function

Le code ci-dessus va créer une fonction qui peut être utilisée dans la feuille de calcul" GetFileNamesbyExt " (tout comme les fonctions normales).

Cette fonction prend deux paramètres - l'emplacement du dossier et le mot-clé d'extension.Il renvoie un tableau de noms de fichiers correspondant à l'extension donnée.Si aucune extension ou mot-clé n'est spécifié, il renverra tous les noms de fichiers dans le dossier spécifié.

Syntaxe : =GetFileNamesbyExt("Emplacement du dossier","Extension")

Où mettre ce code ?

Suivez les étapes ci-dessous pour copier ce code dans l'éditeur VB.

• Allez dans l'onglet développeur.
• Cliquez sur le bouton Visual Basic.Cela ouvrira l'éditeur VB.
• Dans l'éditeur VB, cliquez avec le bouton droit sur n'importe quel objet du classeur sur lequel vous travaillez, accédez à Insérer et cliquez sur Module.Si vous ne voyez pas l'Explorateur de projets, utilisez le raccourci clavier Contrôle + R (maintenez le contrôle et appuyez sur "R").
• Double-cliquez sur l'objet module et copiez et collez le code ci-dessus dans la fenêtre de code du module.

Comment utiliser cette fonction ?

Voici les étapes à suivre pour utiliser cette fonctionnalité dans une feuille de calcul :

• Dans n'importe quelle cellule, entrez l'adresse de dossier du dossier à partir duquel vous souhaitez répertorier les noms de fichiers.Je l'ai entré dans la cellule A1.
• Dans la cellule, entrez l'extension (ou le mot-clé) que vous voulez que tous les noms de fichiers soient.Je l'ai entré dans la cellule B1.
• Dans la cellule où vous voulez la liste, saisissez la formule suivante (je l'ai saisie dans la cellule A3) :

  =SIERREUR(INDEX(GetFileNamesbyExt($A$1,$B$1),ROW()-2),"")

• Copiez et collez la formule dans la cellule ci-dessous pour obtenir une liste de tous les fichiers.

Et vousToutes les astuces Excel que vous utilisez pour vous simplifier la vie.J'aimerais apprendre de vous.Partagez-le dans la section des commentaires!