Få en liste over filnavn fra en mappe ved hjelp av Excel (med og uten VBA)

På min første dag i et lite konsulentfirma ble jeg tildelt et tredagers korttidsprosjekt.Jobben er enkel.

Det er mange mapper på en nettverksstasjon, hver med hundrevis av filer.

Jeg måtte følge disse tre trinnene:

1. Velg filen og kopier navnet.
2. Lim inn navnet i en celle i Excel og trykk Enter.
3. Gå til neste fil og gjenta trinn 1 og 2.

Høres enkelt ut, ikke sant?

Det er enkelt og veldig tidkrevende.

Det jeg gjorde på tre dager kan gjøres på minutter hvis jeg kan den rette teknikken.

I denne opplæringen skal jeg vise deg forskjellige måter å gjøre hele prosessen superrask og superenkel (med og uten VBA).

Begrensninger for metodene vist i denne opplæringen:Ved å bruke teknikken som vises nedenfor, vil du bare kunne få navnene på filene i hjemmemappen.Du får ikke navn på filer i undermapper i hovedmappen.Her er en måte å få filnavn fra mapper og undermapper ved å bruke Power Query

Bruk FILER-funksjonen for å få en liste over filnavn fra en mappe

hørt om detFILES-funksjonen?

Hvis du ikke gjør det, ikke bekymre deg.

Det er fra et Excel-regneark fra barndommen (versjon 4-formler).

Selv om denne formelen ikke fungerer med regnearkceller, fungerer den fortsatt med navngitte områder.Vi vil bruke dette faktum til å få en liste over filnavn fra den angitte mappen.

Anta nå at du har en fil på skrivebordet som heter " Testmappe " og du vil ha en liste over filnavn for alle filene i den mappen.

Her er trinnene som vil gi deg filnavnene i denne mappen:

1. I celle A1 skriver du inn hele mappeadressen etterfulgt av en stjerne (*)
   • For eksempel, hvis mappen din var i C-stasjonen, ville adressen være noe sånt som
     C:\Users\DITT NAVN\Desktop\Testmappe\*
   • Hvis du ikke er sikker på hvordan du får tak i mappeadressen, bruk følgende metode:
     • • I mappen du vil hente filnavnet fra, opprett en ny Excel-arbeidsbok eller åpne en eksisterende arbeidsbok i mappen og bruk følgende formel i en hvilken som helst celle.Denne formelen vil gi deg mappeadressen med en stjerne (*) på slutten.Du kan nå kopiere og lime inn (lime inn som verdi) denne adressen i en hvilken som helst celle i arbeidsboken (A1 i dette eksemplet) der du vil ha filnavnet.

         =REPLACE(CELL("filnavn"),FINN("[",CELL("filnavn")),LEN(CELLE("filnavn")),"*")

         [Hvis du opprettet en ny arbeidsbok i en mappe for å bruke formelen ovenfor og få mappeadressen, må du kanskje slette den slik at den ikke er i listen over filer i den mappen]

2. Gå til "Formler"-fanen og klikk på "Definer navn".
3. I dialogboksen Nytt navn bruker du følgende detaljer
   • Navn: FileNameList (velg gjerne hvilket som helst navn du liker)
   • Omfang: Arbeidsbok
   • Refererer til: =FILER(Ark1!$A$1)
4. Nå for å få en liste over filer, vil vi bruke et navngitt område i INDEX-funksjonen.Gå til celle A3 (eller hvilken celle du vil at listen over navn skal begynne med) og skriv inn følgende formel:

   =IFERROR(INDEKS(FilnavnListe;RAD()-2),"")

5. Dra den ned og den vil gi deg en liste over alle filnavnene i mappen

Vil du trekke ut filer med en bestemt utvidelse? ?

Hvis du ønsker å få alle filer med en bestemt filtype, endrer du bare stjernen med den filtypen.Hvis du for eksempel bare vil ha excel-filen, kan du bruke *xls* i stedet for *

Så mappeadressen du må bruke erC:UsersSumitDesktopTest-mappe*xls*

På samme måte, for Word-dokumentfiler, bruk *doc*

Hvordan virker dette?

FILES-formelen henter navnene på alle filer med den angitte filtypen i den angitte mappen.

I INDEKS-formelen gir vi filnavnene som en matrise og bruker ROW-funksjonen til å returnere de første, andre, tredje, osv. filnavnene.

Merk at jeg brukteRAD()-2, siden vi starter på tredje linje.Så når radnummeret er 4, vil ROW()-2 være 1 for den første instansen, 2 for den andre, og så videre.

Få en liste over alle filnavn fra en mappe ved hjelp av VBA

Nå må jeg si at metoden ovenfor er litt komplisert (det er mange trinn).

Det er imidlertid mye bedre enn å gjøre dette manuelt.

Men hvis du er komfortabel med å bruke VBA (eller hvis du er flink til å følge de nøyaktige trinnene jeg vil liste nedenfor), kan du lage en egendefinert funksjon (UDF) som enkelt kan få navnene på alle filene.

Fordeler med å bruke brukerdefinerte funksjoner (UDF)是dukan將funksjonLagre i en personlig makroarbeidsbok og gjenbruk enkelt uten å måtte gjenta disse trinnene igjen og igjen.Du kan også lage tillegg og dele denne funksjonaliteten med andre.

La meg nå først gi deg VBA-koden som vil lage en funksjon for å få en liste over alle filnavn fra en mappe i Excel.

Funksjon 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) Sett MyFiles = MyFolder.Files ReDim Result(1 To MyFiles.Count) i = 1 For hver MyFile I MyFiles Result(i) = MyFile.Name i = i + 1 Neste MyFile GetFileNames = Resultat Sluttfunksjon

Koden ovenfor vil lage en funksjon GetFileNames som kan brukes i regnearket (akkurat som en vanlig funksjon).

Hvor skal jeg legge denne koden?

Følg trinnene nedenfor for å kopiere denne koden i VB-editoren.

Hvordan bruke denne funksjonen?

Her er trinnene for å bruke denne funksjonen i et regneark:

• I en hvilken som helst celle, skriv inn mappeadressen til mappen du vil vise filnavn fra.
• I cellen der du vil ha listen, skriv inn følgende formel (jeg skrev den inn i celle A3):

  =IFERROR(INDEKS(GetFileNames($A$1),RAD()-2),"")

• Kopier og lim inn formelen i cellen nedenfor for å få en liste over alle filene.

Merk at jeg skrev inn mappeplasseringen i en celle og deretter innGetFileNamesDenne cellen brukes i en formel.Du kan også hardkode mappeadressen i formelen slik:

=IFERROR(INDEX(GetFileNames("C:\Brukere\DITT NAVN\Desktop\Testmappe"),RAD()-2),"")

I formelen ovenfor brukte vi ROW()-2 og vi starter med den tredje raden.Dette sikrer at når jeg kopierer formelen i cellen under, vil den øke med 1.Hvis du skriver inn formelen i den første raden i kolonnen, kan du ganske enkelt bruke ROW().

Hvordan fungerer denne formelen?

GetFileNames-formelen returnerer en matrise som inneholder navnene på alle filene i mappen.

INDEX-funksjonen brukes til å liste ett filnavn per celle, og starter med det første.

IFERROR-funksjonen brukes til å returnere blank i stedet for #REF!Feil vises når en formel kopieres i en celle, men det er ikke flere filnavn å liste.

Få en liste over alle filnavn med en bestemt utvidelse ved å bruke VBA

Formelen ovenfor er nyttig når du ønsker å få en liste over alle filnavn fra en mappe i Excel.

Men hva om du bare ønsker å få navnet på en videofil, eller bare en Excel-fil, eller bare en fil som inneholder et bestemt nøkkelord.

I dette tilfellet kan du bruke en litt annen funksjon.

Koden nedenfor lar deg få alle filnavn som har et spesifikt nøkkelord (eller en bestemt utvidelse) i seg.

Funksjon 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) Sett MyFiles = MyFolder.Files ReDim Result(1 To MyFiles.Count) i = 1 For hver MyFile I 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

Koden ovenfor vil lage en funksjon som kan brukes i regnearket" GetFileNamesbyExt " (akkurat som vanlige funksjoner).

Denne funksjonen tar to parametere - mappeplasseringen og utvidelsesnøkkelordet.Den returnerer en rekke filnavn som samsvarer med den gitte utvidelsen.Hvis ingen utvidelse eller nøkkelord er spesifisert, vil den returnere alle filnavn i den angitte mappen.

Syntaks: =GetFileNamesbyExt("Mappeplassering","Utvidelse")

Hvor skal jeg legge denne koden?

Følg trinnene nedenfor for å kopiere denne koden i VB-editoren.

• Gå til utviklerfanen.
• Klikk på Visual Basic-knappen.Dette vil åpne VB-editoren.
• I VB-editoren høyreklikker du på et objekt i arbeidsboken du jobber med, går til Sett inn og klikker på Modul.Hvis du ikke ser Project Explorer, bruk hurtigtasten Control + R (hold kontroll og trykk "R").
• Dobbeltklikk på modulobjektet og kopier og lim inn koden ovenfor i modulkodevinduet.

Hvordan bruke denne funksjonen?

Her er trinnene for å bruke denne funksjonen i et regneark:

• I en hvilken som helst celle, skriv inn mappeadressen til mappen du vil vise filnavn fra.Jeg skrev dette inn i celle A1.
• I cellen skriver du inn filtypen (eller nøkkelordet) du vil at alle filnavnene skal være.Jeg skrev inn dette i celle B1.
• I cellen der du vil ha listen, skriv inn følgende formel (jeg skrev den inn i celle A3):

  =IFERROR(INDEKS(GetFileNamesbyExt($A$1,$B$1),RAD()-2),"")

• Kopier og lim inn formelen i cellen nedenfor for å få en liste over alle filene.

Hva med degEventuelle Excel-triks du bruker for å forenkle livet ditt.Jeg vil gjerne lære av deg.Del det i kommentarfeltet!