Ottieni l'elenco dei nomi dei file da una cartella in Excel (con e senza VBA)

Nel mio primo giorno di lavoro in una piccola società di consulenza, sono stato incaricato di un breve progetto per tre giorni.

Il lavoro era semplice.

C'erano molte cartelle sull'unità di rete e ogni cartella conteneva centinaia di file.

Ho dovuto seguire questi tre passaggi:

1. Seleziona il file e copia il suo nome.
2. Incolla quel nome in una cella in Excel e premi Invio.
3. Passa al file successivo e ripeti i passaggi 1 e 2.

Sembra semplice vero?

Era - Semplice e un'enorme perdita di tempo.

Quello che mi ha richiesto tre giorni avrebbe potuto essere fatto in pochi minuti se avessi conosciuto le tecniche giuste.

In questo tutorial, ti mostrerò diversi modi per rendere l'intero processo super veloce e super facile (con e senza VBA).

Limitazioni dei metodi mostrati in questo tutorial: Con le tecniche mostrate di seguito, sarai in grado di ottenere solo i nomi dei file all'interno della cartella principale. Non otterrai i nomi dei file nelle sottocartelle all'interno della cartella principale. Ecco un modo per ottenere i nomi dei file da cartelle e sottocartelle utilizzando Power Query

Utilizzo della funzione FILE per ottenere un elenco di nomi di file da una cartella

Sentito parlare di Funzione FILE Prima?

Non preoccuparti se non l'hai fatto.

Risale ai tempi dell'infanzia dei fogli di calcolo Excel (una formula della versione 4).

Sebbene questa formula non funzioni nelle celle del foglio di lavoro, funziona ancora negli intervalli denominati. Useremo questo fatto per ottenere l'elenco dei nomi di file da una cartella specificata.

Ora, supponiamo di avere una cartella con il nome - 'Cartella di prova' sul desktop e si desidera ottenere un elenco di nomi di file per tutti i file in questa cartella.

Ecco i passaggi che ti daranno i nomi dei file da questa cartella:

1. Nella cella A1, inserisci l'indirizzo completo della cartella seguito da un asterisco (*)
   • Ad esempio, se la tua cartella nell'unità C, l'indirizzo sarebbe simile
     C:\Users\Sumit\Desktop\Test Folder\*
   • Se non sei sicuro di come ottenere l'indirizzo della cartella, usa il seguente metodo:
     • • Nella cartella da cui si desidera ottenere i nomi dei file, creare una nuova cartella di lavoro Excel o aprire una cartella di lavoro esistente nella cartella e utilizzare la formula seguente in qualsiasi cella. Questa formula ti darà l'indirizzo della cartella e aggiunge un asterisco (*) alla fine. Ora puoi copiare e incollare (incollare come valore) questo indirizzo in qualsiasi cella (A1 in questo esempio) nella cartella di lavoro in cui desideri i nomi dei file.

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

         [Se hai creato una nuova cartella di lavoro nella cartella per utilizzare la formula sopra e ottenere l'indirizzo della cartella, potresti volerla eliminare in modo che non sia presente nell'elenco dei file in quella cartella]
2. Vai alla scheda "Formule" e fai clic sull'opzione "Definisci nome".
3. Nella finestra di dialogo Nuovo nome, usa i seguenti dettagli
   • Nome: FileNameList (sentiti libero di scegliere il nome che preferisci)
   • Ambito: cartella di lavoro
   • Si riferisce a: =FILES(Foglio1!$A$1)
4. Ora per ottenere l'elenco dei file, utilizzeremo l'intervallo denominato all'interno di una funzione INDICE. Vai alla cella A3 (o qualsiasi cella in cui desideri che inizi l'elenco dei nomi) e inserisci la seguente formula:

   =IFERROR(INDEX(ListaNomeFile,RIGA()-2),"")

5. Trascinalo verso il basso e ti darà un elenco di tutti i nomi di file nella cartella

Vuoi estrarre file con un'estensione specifica??

Se vuoi ottenere tutti i file con una particolare estensione, cambia semplicemente l'asterisco con quell'estensione di file. Ad esempio, se vuoi solo file excel, puoi usare *xls* invece di *

Quindi l'indirizzo della cartella che devi usare sarebbe C:\Users\Sumit\Desktop\Test Folder\*xls*

Allo stesso modo, per i file di documenti di Word, usa *doc*

Come funziona?

La formula FILES recupera i nomi di tutti i file dell'estensione specificata nella cartella specificata.

Nella formula INDEX, abbiamo assegnato i nomi dei file come array e restituiamo il primo, il secondo, il terzo nome del file e così via utilizzando la funzione RIGA.

Nota che ho usato RIGA()-2, poiché siamo partiti dalla terza fila in poi. Quindi ROW()-2 sarebbe 1 per la prima istanza, 2 per la seconda istanza quando il numero di riga è 4, e così via.

Guarda il video - Ottieni un elenco di nomi di file da una cartella in Excel

Utilizzo di VBA Ottieni un elenco di tutti i nomi di file da una cartella

Ora, devo dire che il metodo sopra è un po' complesso (con una serie di passaggi).

È, tuttavia, molto meglio che farlo manualmente.

Ma se hai dimestichezza con l'utilizzo di VBA (o se sei bravo a seguire i passaggi esatti che elencherò di seguito), puoi creare una funzione personalizzata (UDF) che può facilmente ottenere i nomi di tutti i file.

Il vantaggio di usare a tuser Dperfezionato Function (UDF) è che puoi salvare la funzione in una cartella di lavoro macro personale e riutilizzarla facilmente senza ripetere i passaggi ancora e ancora. Puoi anche creare un componente aggiuntivo e condividere questa funzione con altri.

Ora lascia che ti dia prima il codice VBA che creerà una funzione per ottenere l'elenco di tutti i nomi di file da una cartella in Excel.

Funzione 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 Imposta MyFSO = CreateObject("Scripting.FileSystemObject") Imposta MyFolder = MyFSO. GetFolder(FolderPath) Imposta MyFiles = MyFolder.Files ReDim Result(1 To MyFiles.Count) i = 1 per ogni MyFile in MyFiles Result(i) = MyFile.Name i = i + 1 MyFile successivo GetFileNames = Risultato End Function

Il codice sopra creerà una funzione GetFileNames che può essere utilizzata nei fogli di lavoro (proprio come le normali funzioni).

Dove mettere questo codice?

Segui i passaggi seguenti per copiare questo codice nell'editor VB.

• Vai alla scheda Sviluppatore.
• Fare clic sul pulsante Visual Basic. Questo aprirà l'editor VB.
• Nell'editor VB, fai clic con il pulsante destro del mouse su uno qualsiasi degli oggetti della cartella di lavoro in cui stai lavorando, vai su Inserisci e fai clic su Modulo. Se non vedi Esplora progetti, usa la scorciatoia da tastiera Ctrl + R (tieni premuto il tasto Ctrl e premi il tasto "R").
• Fare doppio clic sull'oggetto Modulo e copiare e incollare il codice sopra nella finestra del codice del modulo.

Come utilizzare questa funzione?

Di seguito sono riportati i passaggi per utilizzare questa funzione in un foglio di lavoro:

• In qualsiasi cella, inserisci l'indirizzo della cartella da cui desideri elencare i nomi dei file.
• Nella cella in cui desideri l'elenco, inserisci la seguente formula (lo sto inserendo nella cella A3):

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

• Copia e incolla la formula nelle celle sottostanti per ottenere un elenco di tutti i file.

Nota che ho inserito la posizione della cartella in una cella e poi ho usato quella cella nel Ottieni nomi file formula. Puoi anche codificare l'indirizzo della cartella nella formula come mostrato di seguito:

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

Nella formula sopra, abbiamo usato ROW()-2 e siamo partiti dalla terza riga in poi. Ciò ha assicurato che mentre copio la formula nelle celle sottostanti, verrà incrementata di 1. Nel caso in cui tu stia inserendo la formula nella prima riga di una colonna, puoi semplicemente usare ROW().

Come funziona questa formula?

La formula GetFileNames restituisce una matrice che contiene i nomi di tutti i file nella cartella.

La funzione INDICE viene utilizzata per elencare un nome di file per cella, a partire dal primo.

La funzione SEERRORE viene utilizzata per restituire uno spazio vuoto invece di #RIF! errore che viene mostrato quando una formula viene copiata in una cella ma non ci sono più nomi di file da elencare.

Utilizzo di VBA Ottieni un elenco di tutti i nomi di file con un'estensione specifica

La formula sopra funziona alla grande quando vuoi ottenere un elenco di tutti i nomi di file da una cartella in Excel.

Ma cosa succede se vuoi ottenere i nomi solo dei file video, o solo i file Excel, o solo i nomi dei file che contengono una parola chiave specifica.

In tal caso, puoi utilizzare una funzione leggermente diversa.

Di seguito è riportato il codice che ti consentirà di ottenere tutti i nomi di file con una parola chiave specifica (o con un'estensione specifica).

Funzione 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) Imposta MyFiles = MyFolder.Files ReDim Result(1 su MyFiles.Count) i = 1 per ogni MyFile in MyFiles If InStr(1, MyFile.Name, FileExt) 0 Then Result(i) = MyFile .Name i = i + 1 End If Next MyFile ReDim Mantieni risultato(1 in i - 1) GetFileNamesbyExt = Risultato End Function

Il codice sopra creerà una funzione 'Ottieni nomi file per estensione' che può essere utilizzato nei fogli di lavoro (proprio come le normali funzioni).

Questa funzione accetta due argomenti: la posizione della cartella e la parola chiave dell'estensione. Restituisce un array di nomi di file che corrispondono all'estensione data. Se non viene specificata alcuna estensione o parola chiave, restituirà tutti i nomi di file nella cartella specificata.

Sintassi: =GetFileNamesbyExt("Posizione cartella","Estensione")

Dove mettere questo codice?

Segui i passaggi seguenti per copiare questo codice nell'editor VB.

• Vai alla scheda Sviluppatore.
• Fare clic sul pulsante Visual Basic. Questo aprirà l'editor VB.
• Nell'editor VB, fai clic con il pulsante destro del mouse su uno degli oggetti della cartella di lavoro in cui stai lavorando, vai su Inserisci e fai clic su Modulo. Se non vedi Esplora progetti, usa la scorciatoia da tastiera Ctrl + R (tieni premuto il tasto Ctrl e premi il tasto "R").
• Fare doppio clic sull'oggetto Modulo e copiare e incollare il codice sopra nella finestra del codice del modulo.

Come utilizzare questa funzione?

Di seguito sono riportati i passaggi per utilizzare questa funzione in un foglio di lavoro:

• In qualsiasi cella, inserisci l'indirizzo della cartella da cui desideri elencare i nomi dei file. L'ho inserito nella cella A1.
• In una cella, inserisci l'estensione (o la parola chiave) per la quale desideri tutti i nomi dei file. L'ho inserito nella cella B1.
• Nella cella in cui desideri l'elenco, inserisci la seguente formula (lo sto inserendo nella cella A3):

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

• Copia e incolla la formula nelle celle sottostanti per ottenere un elenco di tutti i file.

E tu? Qualsiasi trucco di Excel che usi per semplificarti la vita. Mi piacerebbe imparare da te. Condividilo nella sezione commenti!