Obtenir la liste des noms de fichiers à partir d'un dossier dans Excel (avec et sans VBA)

Lors de mon premier jour de travail dans un petit cabinet de conseil, j'ai été affecté à un court projet pendant trois jours.

Le travail était simple.

Il y avait de nombreux dossiers sur le lecteur réseau et chaque dossier contenait des centaines de fichiers.

J'ai dû suivre ces trois étapes :

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

Cela semble simple, n'est-ce pas ?

C'était - Simple et une énorme perte de temps.

Ce qui m'a pris trois jours aurait pu être fait en quelques minutes si je connaissais les bonnes techniques.

Dans ce tutoriel, je vais vous montrer différentes manières de rendre tout ce processus super rapide et super facile (avec et sans VBA).

Limitations des méthodes présentées dans ce tutoriel : Avec les techniques indiquées ci-dessous, vous ne pourrez obtenir que les noms des fichiers dans le dossier principal. Vous n'obtiendrez 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 sous-dossiers à l'aide de Power Query

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

Entendu parler de Fonction FICHIERS avant?

Ne vous inquiétez pas si vous ne l'avez pas fait.

C'est depuis l'enfance des feuilles de calcul Excel (une formule de la version 4).

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

Maintenant, supposons que vous ayez un dossier avec le nom - 'Dossier de test' sur le bureau 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 de ce dossier :

1. Dans la cellule A1, entrez l'adresse complète du dossier suivie d'un astérisque (*)
   • Par exemple, si votre dossier dans le lecteur C, l'adresse ressemblerait à
     C:\Users\Sumit\Desktop\Test Folder\*
   • Si vous ne savez pas comment obtenir l'adresse du dossier, utilisez la méthode suivante :
     • • Dans le dossier à partir duquel vous souhaitez obtenir les noms de fichiers, créez un nouveau classeur Excel ou ouvrez un classeur existant dans le dossier et utilisez la formule ci-dessous dans n'importe quelle cellule. Cette formule vous donnera l'adresse du dossier et ajoute un astérisque (*) à la fin. Vous pouvez maintenant copier-coller (coller en tant que valeur) cette adresse dans n'importe quelle cellule (A1 dans cet exemple) du classeur dans lequel vous voulez les noms de fichiers.

         =REPLACE(CELL("nom de fichier"),RECHERCHE("[",CELL("nom de fichier")),LEN(CELL("nom de fichier")),"*")

         [Si vous avez créé un nouveau classeur dans le dossier pour utiliser la formule ci-dessus et obtenir l'adresse du dossier, vous souhaiterez 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 le nom".
3. Dans la boîte de dialogue Nouveau nom, utilisez les détails suivants
   • Nom : FileNameList (n'hésitez pas à choisir le nom que vous voulez)
   • Portée : Cahier d'exercices
   • Fait référence à : =FILES(Sheet1!$A$1)
4. Maintenant, pour obtenir la liste des fichiers, nous allons utiliser la plage nommée dans une fonction INDEX. Accédez à la cellule A3 (ou à n'importe quelle cellule dans laquelle vous souhaitez que la liste des 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 particulière, modifiez simplement l'astérisque avec cette extension de fichier. Par exemple, si vous voulez uniquement des fichiers Excel, vous pouvez utiliser *xls* au lieu de *

Donc, l'adresse du dossier que vous devez utiliser serait C:\Users\Sumit\Desktop\Test Folder\*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 de l'extension spécifiée dans le dossier spécifié.

Dans la formule INDEX, nous avons donné les noms de fichiers sous forme de tableau et nous retournons les 1er, 2e, 3e noms de fichier et ainsi de suite en utilisant la fonction ROW.

Notez que j'ai utilisé LIGNE()-2, car nous avons commencé à partir de la troisième rangée. Ainsi, ROW()-2 serait 1 pour la première instance, 2 pour la deuxième instance lorsque le numéro de ligne est 4, et ainsi de suite.

Regarder la vidéo - Obtenir la liste des noms de fichiers d'un dossier dans Excel

Utilisation de VBA Obtenir une liste de tous les noms de fichiers d'un dossier

Maintenant, je dois dire que la méthode ci-dessus est un peu complexe (avec un certain nombre d'étapes).

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

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

L'avantage d'utiliser un User réaffiné Function (UDF) est que vous pouvez enregistrer la fonction dans un classeur de macros personnel et la réutiliser facilement sans répéter les étapes encore et encore. Vous pouvez également créer un complément et partager cette fonction avec d'autres.

Maintenant, permettez-moi d'abord de vous donner le code VBA qui créera une fonction pour obtenir la 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 Pour chaque MyFile In MyFiles Result(i) = MyFile.Name i = i + 1 Next MyFile GetFileNames = Résultat End Function

Le code ci-dessus créera une fonction GetFileNames qui peut être utilisée dans les feuilles de calcul (tout comme les fonctions normales).

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 l'un des objets du classeur dans lequel vous travaillez, accédez à Insérer et cliquez sur Module. Si vous ne voyez pas l'explorateur de projet, utilisez le raccourci clavier Ctrl + R (maintenez la touche Ctrl enfoncée et appuyez sur la touche « 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 fonction dans une feuille de calcul :

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

  =IFERREUR(INDEX(GetFileNames($A$1),LIGNE()-2),"")

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

Notez que j'ai entré l'emplacement du dossier dans une cellule, puis utilisé cette cellule dans le Obtenir les noms de fichiers formule. Vous pouvez également coder en dur l'adresse du dossier dans la formule comme indiqué ci-dessous :

=IFERROR(INDEX(GetFileNames("C:\Users\Sumit\Desktop\Test Folder"),ROW()-2),"")

Dans la formule ci-dessus, nous avons utilisé ROW()-2 et nous avons commencé à partir de la troisième ligne. Cela garantit que lorsque je copie la formule dans les cellules ci-dessous, elle sera incrémentée de 1. Si vous entrez la formule dans la première ligne d'une colonne, vous pouvez simplement utiliser ROW ().

Comment fonctionne cette formule ?

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

La fonction INDEX permet de 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 qui s'affiche lorsqu'une formule est copiée dans une cellule mais qu'il n'y a plus de noms de fichiers à lister.

Utilisation de VBA Obtenez une liste de tous les noms de fichiers avec une extension spécifique

La formule ci-dessus fonctionne très bien lorsque vous souhaitez obtenir une liste de tous les noms de fichiers d'un dossier dans Excel.

Mais que se passe-t-il si vous souhaitez obtenir uniquement les noms des fichiers vidéo, ou uniquement les fichiers Excel, ou uniquement les noms de fichiers contenant un mot-clé spécifique.

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

Vous trouverez ci-dessous le code qui vous permettra d'obtenir tous les noms de fichiers contenant un mot-clé spécifique (ou d'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 Pour chaque 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 créera une fonction 'GetFileNamesbyExt' qui peut être utilisé dans les feuilles de calcul (tout comme les fonctions normales).

Cette fonction prend deux arguments - l'emplacement du dossier et le mot-clé d'extension. Il renvoie un tableau de noms de fichiers qui correspondent à 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 l'un des objets du classeur dans lequel vous travaillez, accédez à Insérer et cliquez sur Module. Si vous ne voyez pas l'explorateur de projet, utilisez le raccourci clavier Ctrl + R (maintenez la touche Ctrl enfoncée et appuyez sur la touche « 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 fonction dans une feuille de calcul :

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

  =SIERREUR(INDEX(LitFichierNomsparExt($A$1,$B$1),LIGNE()-2),"")

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

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