✅ Få listan över filnamn från en mapp i Excel (med och utan VBA)

Innehållsförteckning

Använda FILES -funktionen för att få en lista över filnamn från en mapp
Använda VBA Få en lista över alla filnamn från en mapp
Använda VBA Få en lista över alla filnamn med en specifik tillägg

Min första dag i mitt jobb på ett litet konsultföretag var jag bemannad på ett kort projekt i tre dagar.

Arbetet var enkelt.

Det fanns många mappar på nätverksenheten och varje mapp hade hundratals filer i den.

Jag var tvungen att följa dessa tre steg:

Välj filen och kopiera dess namn.
Klistra in det namnet i en cell i Excel och tryck på Retur.
Gå till nästa fil och upprepa steg 1 och 2.

Låter enkelt eller hur?

Det var - Enkelt och ett enormt slöseri med tid.

Det som tog mig tre dagar kunde ha gjorts på några minuter om jag visste rätt teknik.

I denna handledning kommer jag att visa dig olika sätt att göra hela processen supersnabb och superenkel (med och utan VBA).

Begränsningar av metoderna som visas i den här självstudien: Med teknikerna som visas nedan kommer du bara att kunna få namnen på filerna i huvudmappen. Du får inte namnen på filerna i undermapparna i huvudmappen. Här är ett sätt att få namn på filer från mappar och undermappar med Power Query

Använda FILES -funktionen för att få en lista över filnamn från en mapp

Hört talas om FILES -funktion innan?

Oroa dig inte om du inte har.

Det är från barndomsdagarna i Excel -kalkylblad (en version 4 -formel).

Även om denna formel inte fungerar i kalkylbladets celler, fungerar den fortfarande i namngivna intervall. Vi kommer att använda detta för att få listan över filnamn från en angiven mapp.

Antag nu att du har en mapp med namnet - 'Testmapp'På skrivbordet, och du vill få en lista med filnamn för alla filer i den här mappen.

Här är stegen som ger dig filnamnen från den här mappen:

I cell A1 anger du mappens fullständiga adress följt av ett stjärntecken (*)
- Till exempel om din mapp i C -enheten ser adressen ut
  C: \ Users \ Sumit \ Desktop \ Testmapp \*
- Om du inte är säker på hur du får mappadressen använder du följande metod:
  - - I den mapp som du vill hämta filnamnen från, skapa antingen en ny Excel -arbetsbok eller öppna en befintlig arbetsbok i mappen och använd formeln nedan i valfri cell. Denna formel ger dig mappadressen och lägger till ett stjärntecken (*) i slutet. Nu kan du kopiera-klistra in (klistra in som värde) denna adress i valfri cell (A1 i detta exempel) i arbetsboken där du vill ha filnamnen.
```
= ERSÄTT (CELL ("filnamn"), FIND ("[", CELL ("filnamn")), LEN (CELL ("filnamn")), "*")
```
      [Om du har skapat en ny arbetsbok i mappen för att använda ovanstående formel och få mappadressen, kanske du vill ta bort den så att den inte finns i listan över filer i den mappen]
Gå till fliken "Formler" och klicka på alternativet "Definiera namn".
Använd följande information i dialogrutan Nytt namn
- Namn: FileNameList (välj gärna vilket namn du vill)
- Omfattning: Arbetsbok
- Avser: = FILER (Sheet1! $ A $ 1)
För att få listan över filer använder vi det namngivna intervallet inom en INDEX -funktion. Gå till cell A3 (eller vilken cell som helst där du vill att listan med namn ska börja) och ange följande formel:
```
= IFERROR (INDEX (FileNameList, ROW ()-2), "")
```
Dra ner det och det kommer att ge dig en lista över alla filnamn i mappen

Vill du extrahera filer med en specifik tillägg ??

Om du vill få alla filer med ett särskilt tillägg, ändrar du bara asterisken med det filtillägget. Om du till exempel bara vill excel -filer kan du använda * xls * istället för *

Så den mappadress som du behöver använda skulle vara C: \ Users \ Sumit \ Desktop \ Testmapp \*xls*

På samma sätt, för word -dokumentfiler, använd *doc *

Hur fungerar detta?

FILES -formeln hämtar namnen på alla filer i det angivna tillägget i den angivna mappen.

I INDEX -formeln har vi angett filnamnen som arrayen och vi returnerar 1: a, 2: a, 3: e filnamnet och så vidare med hjälp av ROW -funktionen.

Observera att jag har använt RAD ()-2, när vi började från tredje raden och framåt. Så RAD ()-2 skulle vara 1 för den första instansen, 2 för den andra förekomsten när radnumret är 4, och så vidare och så vidare.

Titta på video - Hämta lista över filnamn från en mapp i Excel

Använda VBA Få en lista över alla filnamn från en mapp

Nu måste jag säga att ovanstående metod är lite komplex (med ett antal steg).

Det är dock mycket bättre än att göra detta manuellt.

Men om du är bekväm med att använda VBA (eller om du är duktig på att följa exakta steg som jag kommer att lista nedan) kan du skapa en anpassad funktion (UDF) som enkelt kan ge dig namnen på alla filer.

Fördelen med att använda en User Defined Function (UDF) är att du kan spara funktionen i en personlig makro -arbetsbok och enkelt återanvända den utan att upprepa stegen om och om igen. Du kan också skapa ett tillägg och dela denna funktion med andra.

Låt mig nu först ge dig VBA -koden som skapar en funktion för att få listan över alla filnamn från en mapp i Excel.

Funktion GetFileNames (ByVal FolderPath Som String) Som Variant Dim Resultat Som Variant Dim i As Integer Dim MyFile As Object Dim MyFSO Som Object Dim MyFolder Som Object Dim MyFiles Som Object Set MyFSO = CreateObject ("Scripting.FileSystemObject") Ange MyFolder = MyFSO. GetFolder (FolderPath) Ställ in MyFiles = MyFolder.Files ReDim -resultat (1 till MyFiles.Count) i = 1 för varje MyFile i MyFiles -resultat (i) = MyFile.Name i = i + 1 Nästa MyFile GetFileNames = Resultat Slutfunktion

Koden ovan skapar en funktion GetFileNames som kan användas i kalkylbladet (precis som vanliga funktioner).

Var ska man lägga den här koden?

Följ stegen nedan för att kopiera denna kod i VB Editor.

Gå till fliken Utvecklare.
Klicka på Visual Basic -knappen. Detta öppnar VB Editor.
I VB Editor, högerklicka på något av objekten i arbetsboken du arbetar i, gå till Infoga och klicka på Modul. Om du inte ser Project Explorer använder du kortkommandot Ctrl + R (håll ned kontrollknappen och tryck på "R" -knappen).
Dubbelklicka på modulobjektet och kopiera och klistra in ovanstående kod i modulkodfönstret.

Hur använder man denna funktion?

Nedan följer stegen för att använda den här funktionen i ett kalkylblad:

I vilken cell som helst anger du mappadressen för den mapp från vilken du vill lista filnamnen.
I cellen där du vill ha listan anger du följande formel (jag anger den i cell A3):
```
= IFERROR (INDEX (GetFileNames ($ A $ 1), ROW ()-2), "")
```
Kopiera och klistra in formeln i cellerna nedan för att få en lista över alla filer.

Observera att jag angav mapplatsen i en cell och sedan använde den cellen i GetFileNames formel. Du kan också hårdkoda mappadressen i formeln enligt nedan:

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

I ovanstående formel har vi använt ROW ()-2 och vi började från tredje raden och framåt. Detta såg till att när jag kopierar formeln i cellerna nedan kommer den att ökas med 1. Om du anger formeln i den första raden i en kolumn kan du helt enkelt använda RAD ().

Hur fungerar denna formel?

Formeln GetFileNames returnerar en array som innehåller namnen på alla filer i mappen.

INDEX -funktionen används för att lista ett filnamn per cell, med början från den första.

IFERROR -funktionen används för att returnera blank istället för #REF! fel som visas när en formel kopieras i en cell men det finns inga fler filnamn att lista.

Använda VBA Få en lista över alla filnamn med en specifik tillägg

Ovanstående formel fungerar utmärkt när du vill få en lista över alla filnamn från en mapp i Excel.

Men vad händer om du bara vill få namnen på videofilerna, eller bara Excel -filerna, eller bara filnamnen som innehåller ett specifikt sökord.

I så fall kan du använda en något annorlunda funktion.

Nedan finns koden som låter dig få alla filnamn med ett specifikt sökord i det (eller ett specifikt tillägg).

Funktion GetFileNamesbyExt (ByVal FolderPath Som String, FileExt Som String) Som Variant Dim Resultat Som Variant Dim i As Integer Dim MyFile Som Object Dim MyFSO Som Object Dim MyFolder Som Object Dim MyFiles Som Object Set MyFSO = CreateObject ("Scripting.FileSystemObject") Set MyFolder = MyFSO.GetFolder (FolderPath) Ställ in MyFiles = MyFolder.Files ReDim -resultat (1 till MyFiles.Count) i = 1 för varje MyFile i MyFiles If InStr (1, MyFile.Name, FileExt) 0 Därefter Resultat (i) = MyFile .Name i = i + 1 End If Next MyFile ReDim Bevara resultat (1 till i - 1) GetFileNamesbyExt = Resultat slutfunktion

Ovanstående kod skapar en funktion 'GetFileNamesbyExt'Som kan användas i kalkylblad (precis som vanliga funktioner).

Denna funktion tar två argument - mappplatsen och tilläggsordet. Det returnerar en rad filnamn som matchar det angivna tillägget. Om inget tillägg eller nyckelord har angetts returnerar det alla filnamn i den angivna mappen.

Syntax: = GetFileNamesbyExt ("Mappplats", "Tillägg")

Var ska man lägga den här koden?

Följ stegen nedan för att kopiera denna kod i VB Editor.

Gå till fliken Utvecklare.
Klicka på Visual Basic -knappen. Detta öppnar VB Editor.
I VB Editor, högerklicka på något av objekten i arbetsboken du arbetar i, gå till Infoga och klicka på Modul. Om du inte ser Project Explorer använder du kortkommandot Ctrl + R (håll ned kontrollknappen och tryck på "R" -knappen).
Dubbelklicka på modulobjektet och kopiera och klistra in ovanstående kod i modulkodfönstret.

Hur använder man denna funktion?

Nedan följer stegen för att använda den här funktionen i ett kalkylblad:

I vilken cell som helst anger du mappadressen för den mapp från vilken du vill lista filnamnen. Jag har skrivit detta i cell A1.
I en cell anger du tillägget (eller nyckelordet), som du vill ha alla filnamn för. Jag har angett detta i cell B1.
I cellen där du vill ha listan anger du följande formel (jag anger den i cell A3):
```
= IFERROR (INDEX (GetFileNamesbyExt ($ A $ 1, $ B $ 1), ROW ()-2), "")
```
Kopiera och klistra in formeln i cellerna nedan för att få en lista över alla filer.