Haal de lijst met bestandsnamen uit een map in Excel (met en zonder VBA)

Op mijn eerste dag in mijn baan bij een klein adviesbureau, werd ik drie dagen lang bemand voor een kort project.

Het werk was eenvoudig.

Er waren veel mappen op de netwerkschijf en elke map bevatte honderden bestanden.

Ik moest deze drie stappen volgen:

1. Selecteer het bestand en kopieer de naam.
2. Plak die naam in een cel in Excel en druk op Enter.
3. Ga naar het volgende bestand en herhaal stap 1 & 2.

Klinkt eenvoudig toch?

Het was - Eenvoudig en een enorme verspilling van tijd.

Wat me drie dagen kostte, had in een paar minuten kunnen worden gedaan als ik de juiste technieken kende.

In deze tutorial laat ik je verschillende manieren zien om dit hele proces supersnel en supergemakkelijk te maken (met en zonder VBA).

Beperkingen van de methoden die in deze zelfstudie worden getoond: Met de onderstaande technieken kunt u alleen de namen van de bestanden in de hoofdmap krijgen. U krijgt niet de namen van de bestanden in de submappen binnen de hoofdmap. Hier is een manier om namen van bestanden uit mappen en submappen op te halen met Power Query

De functie BESTANDEN gebruiken om een ​​lijst met bestandsnamen uit een map te krijgen

Gehoord van FILES-functie voordat?

Maak je geen zorgen als je dat niet hebt gedaan.

Het komt uit de kindertijd van Excel-spreadsheets (een formule van versie 4).

Hoewel deze formule niet werkt in de werkbladcellen, werkt deze nog steeds in benoemde bereiken. We zullen dit feit gebruiken om de lijst met bestandsnamen uit een opgegeven map te halen.

Stel nu dat je een map hebt met de naam - 'Testmap' op het bureaublad en u wilt een lijst met bestandsnamen krijgen voor alle bestanden in deze map.

Dit zijn de stappen die u de bestandsnamen uit deze map geven:

1. Voer in cel A1 het volledige adres van de map in gevolgd door een sterretje (*)
   • Als uw map bijvoorbeeld op de C-schijf staat, ziet het adres er als volgt uit:
     C:\Users\Sumit\Desktop\Testmap\*
   • Als u niet zeker weet hoe u het mapadres kunt krijgen, gebruikt u de volgende methode:
     • • Maak in de map waaruit u de bestandsnamen wilt halen een nieuwe Excel-werkmap of open een bestaande werkmap in de map en gebruik de onderstaande formule in een willekeurige cel. Deze formule geeft u het mapadres en voegt aan het einde een sterretje (*) toe. Nu kunt u dit adres kopiëren en plakken (plakken als waarde) in elke cel (A1 in dit voorbeeld) in de werkmap waarin u de bestandsnamen wilt hebben.

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

         [Als u een nieuwe werkmap in de map hebt gemaakt om de bovenstaande formule te gebruiken en het mapadres op te halen, wilt u deze misschien verwijderen zodat deze niet voorkomt in de lijst met bestanden in die map]
2. Ga naar het tabblad 'Formules' en klik op de optie 'Naam definiëren'.
3. Gebruik in het dialoogvenster Nieuwe naam de volgende details:
   • Naam: FileNameList (voel je vrij om de naam te kiezen die je wilt)
   • Toepassingsgebied: werkboek
   • Verwijst naar: =BESTANDEN(Blad1!$A$1)
4. Om nu de lijst met bestanden te krijgen, gebruiken we het genoemde bereik binnen een INDEX-functie. Ga naar cel A3 (of een cel waar u de lijst met namen wilt laten beginnen) en voer de volgende formule in:

   =IFERROR(INDEX(Bestandsnaamlijst,RIJ()-2,"")

5. Sleep dit naar beneden en je krijgt een lijst met alle bestandsnamen in de map

Wilt u bestanden uitpakken met een specifieke extensie??

Als u alle bestanden met een bepaalde extensie wilt krijgen, wijzigt u gewoon het sterretje met die bestandsextensie. Als u bijvoorbeeld alleen Excel-bestanden wilt, kunt u *xls* gebruiken in plaats van *

Dus het mapadres dat u moet gebruiken, is: C:\Users\Sumit\Desktop\Testmap\*xls*

Op dezelfde manier gebruikt u voor Word-documentbestanden *doc*

Hoe werkt dit?

De formule BESTANDEN haalt de namen op van alle bestanden van de opgegeven extensie in de opgegeven map.

In de INDEX-formule hebben we de bestandsnamen gegeven als de array en we retourneren de 1e, 2e, 3e bestandsnamen enzovoort met behulp van de ROW-functie.

Merk op dat ik heb gebruikt RIJ()-2, aangezien we vanaf de derde rij begonnen. Dus ROW()-2 zou 1 zijn voor de eerste instantie, 2 voor de tweede instantie als het rijnummer 4 is, enzovoort, enzovoort.

Bekijk video - Krijg een lijst met bestandsnamen uit een map in Excel

VBA gebruiken Een lijst met alle bestandsnamen uit een map krijgen

Nu moet ik zeggen dat de bovenstaande methode een beetje ingewikkeld is (met een aantal stappen).

Het is echter een stuk beter dan dit handmatig te doen.

Maar als je vertrouwd bent met het gebruik van VBA (of als je goed bent in het volgen van de exacte stappen die ik hieronder ga opsommen), kun je een aangepaste functie (UDF) maken waarmee je gemakkelijk de namen van alle bestanden kunt krijgen.

Het voordeel van het gebruik van een user NSverfijnd Function (UDF) is dat u de functie kunt opslaan in een persoonlijke macrowerkmap en deze eenvoudig opnieuw kunt gebruiken zonder de stappen steeds opnieuw te herhalen. U kunt ook een invoegtoepassing maken en deze functie met anderen delen.

Laat me je nu eerst de VBA-code geven die een functie zal maken om de lijst met alle bestandsnamen uit een map in Excel te krijgen.

Functie GetFileNames(ByVal FolderPath As String) As Variant Dim Resultaat 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") Stel MyFolder = MyFSO in. GetFolder(FolderPath) Set MyFiles = MyFolder.Files ReDim Resultaat(1 To MyFiles.Count) i = 1 For Each MyFile In MyFiles Result(i) = MyFile.Name i = i + 1 Next MyFile GetFileNames = Resultaat Eindfunctie

De bovenstaande code maakt een functie GetFileNames aan die in de werkbladen kan worden gebruikt (net als bij reguliere functies).

Waar plaats je deze code?

Volg de onderstaande stappen om deze code in de VB Editor te kopiëren.

• Ga naar het tabblad Ontwikkelaars.
• Klik op de Visual Basic-knop. Hiermee wordt de VB-editor geopend.
• Klik in de VB-editor met de rechtermuisknop op een van de objecten van de werkmap waarin u werkt, ga naar Invoegen en klik op Module. Als u de Projectverkenner niet ziet, gebruikt u de sneltoets Control + R (houd de control-toets ingedrukt en druk op de 'R'-toets).
• Dubbelklik op het Module-object en kopieer en plak de bovenstaande code in het modulecodevenster.

Hoe deze functie te gebruiken?

Hieronder staan ​​de stappen om deze functie in een werkblad te gebruiken:

• Voer in een willekeurige cel het mapadres in van de map waarvan u de bestandsnamen wilt weergeven.
• Voer in de cel waar u de lijst wilt hebben de volgende formule in (ik voer deze in cel A3) in:

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

• Kopieer en plak de formule in de onderstaande cellen om een ​​lijst met alle bestanden te krijgen.

Merk op dat ik de maplocatie in een cel heb ingevoerd en die cel vervolgens heb gebruikt in de Bestandsnamen ophalen formule. U kunt het mapadres ook hard coderen in de formule zoals hieronder weergegeven:

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

In de bovenstaande formule hebben we ROW()-2 gebruikt en zijn we vanaf de derde rij begonnen. Dit zorgde ervoor dat wanneer ik de formule in de onderstaande cellen kopieer, deze met 1 wordt verhoogd. Als u de formule in de eerste rij van een kolom invoert, kunt u eenvoudig ROW() gebruiken.

Hoe werkt deze formule?

De formule GetFileNames retourneert een matrix die de namen van alle bestanden in de map bevat.

De INDEX-functie wordt gebruikt om één bestandsnaam per cel weer te geven, beginnend bij de eerste.

De functie IFERROR wordt gebruikt om blanco te retourneren in plaats van de #REF! fout die wordt weergegeven wanneer een formule in een cel wordt gekopieerd, maar er zijn geen bestandsnamen meer om weer te geven.

VBA gebruiken Een lijst krijgen van alle bestandsnamen met een specifieke extensie

De bovenstaande formule werkt prima als u een lijst met alle bestandsnamen uit een map in Excel wilt krijgen.

Maar wat als u de namen wilt krijgen van alleen de videobestanden, of alleen de Excel-bestanden, of alleen de bestandsnamen die een specifiek trefwoord bevatten.

In dat geval kunt u een iets andere functie gebruiken.

Hieronder vindt u de code waarmee u alle bestandsnamen kunt krijgen met een specifiek trefwoord erin (of van een specifieke extensie).

Functie GetFileNamesbyExt(ByVal FolderPath As String, FileExt As String) As Variant Dim Resultaat As Variant Dim i As Integer Dim MyFile As Object Dim MyFSO As Object Dim MyFolder As Object Dim MyFiles As Object Stel MyFSO = CreateObject("Scripting.FileSystemObject") in MyFolder = MyFSO.GetFolder(FolderPath) Set MyFiles = MyFolder.Files ReDim Resultaat(1 To MyFiles.Count) i = 1 voor elk MyFile in MyFiles If InStr(1, MyFile.Name, FileExt) 0 Then Result(i) = MyFile .Name i = i + 1 End If Next MyFile ReDim ReDim Result behouden (1 To i - 1) GetFileNamesbyExt = Resultaat End Function

De bovenstaande code zal een functie creëren 'GetFileNamesbyExt' die in de werkbladen kunnen worden gebruikt (net als reguliere functies).

Deze functie heeft twee argumenten: de maplocatie en het extensiesleutelwoord. Het retourneert een reeks bestandsnamen die overeenkomen met de opgegeven extensie. Als er geen extensie of trefwoord is opgegeven, worden alle bestandsnamen in de opgegeven map geretourneerd.

Syntaxis: =GetFileNamesbyExt(“Maplocatie”,,”Extensie”)

Waar plaats je deze code?

Volg de onderstaande stappen om deze code in de VB Editor te kopiëren.

• Ga naar het tabblad Ontwikkelaars.
• Klik op de Visual Basic-knop. Hiermee wordt de VB-editor geopend.
• Klik in de VB-editor met de rechtermuisknop op een van de objecten van de werkmap waarin u werkt, ga naar Invoegen en klik op Module. Als u de Projectverkenner niet ziet, gebruikt u de sneltoets Control + R (houd de control-toets ingedrukt en druk op de 'R'-toets).
• Dubbelklik op het Module-object en kopieer en plak de bovenstaande code in het modulecodevenster.

Hoe deze functie te gebruiken?

Hieronder staan ​​de stappen om deze functie in een werkblad te gebruiken:

• Voer in een willekeurige cel het mapadres in van de map waarvan u de bestandsnamen wilt weergeven. Dit heb ik ingevuld in cel A1.
• Voer in een cel de extensie (of het trefwoord) in waarvoor u alle bestandsnamen wilt. Dit heb ik ingevuld in cel B1.
• Voer in de cel waar u de lijst wilt hebben de volgende formule in (ik voer deze in cel A3) in:

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

• Kopieer en plak de formule in de onderstaande cellen om een ​​lijst met alle bestanden te krijgen.

En jij? Alle Excel-trucs die u gebruikt om het leven gemakkelijk te maken. Ik leer graag van je. Deel het in het commentaargedeelte!