Ottieni un elenco di nomi di file da una cartella utilizzando Excel (con e senza VBA)

Il mio primo giorno in una piccola società di consulenza, mi è stato assegnato un progetto a breve termine di tre giorni.Il lavoro è semplice.

Ci sono molte cartelle su un'unità di rete, ognuna con centinaia di file.

Ho dovuto seguire questi tre passaggi:

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

Sembra semplice, vero?

È semplice e richiede molto tempo.

Quello che ho fatto in tre giorni può essere fatto in pochi minuti se conosco la tecnica giusta.

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:Usando la tecnica mostrata di seguito, sarai in grado di ottenere solo i nomi dei file nella tua cartella Inizio.Non ottieni i nomi dei file nelle sottocartelle nella cartella principale.Ecco un modo per ottenere nomi di file da cartelle e sottocartelle usando Power Query

Utilizzare la funzione FILE per ottenere un elenco di nomi di file da una cartella

sentito parlarefunzione FILE?

Se non lo fai, non preoccuparti.

Viene da un foglio di calcolo Excel dell'infanzia (formule della versione 4).

Sebbene questa formula non funzioni con le celle del foglio di lavoro, funziona comunque con intervalli denominati.Useremo questo fatto per ottenere un elenco di nomi di file dalla cartella specificata.

Supponiamo ora di avere un file sul desktop chiamato " Cartella di prova ” e vuoi ottenere un elenco di nomi di file per tutti i file in quella cartella.

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

1. Nella cella A1, inserisci l'indirizzo completo della cartella seguito da un asterisco (*)
   • Ad esempio, se la tua cartella fosse nell'unità C, l'indirizzo sarebbe qualcosa di simile
     C:\Utenti\IL TUO NOME\Desktop\Cartella di prova\*
   • Se non sei sicuro di come ottenere l'indirizzo della cartella, utilizza il metodo seguente:
     • • Nella cartella da cui vuoi ottenere il nome del file, crea una nuova cartella di lavoro di Excel o apri una cartella di lavoro esistente nella cartella e usa la formula seguente in qualsiasi cella.Questa formula ti darà l'indirizzo della cartella con un asterisco (*) alla fine.È ora possibile copiare e incollare (incollare come valore) questo indirizzo in qualsiasi cella della cartella di lavoro (in questo esempio A1) in cui si desidera il nome del file.

         =SOSTITUISCI(CELL("nomefile"),TROVA("[",CELL("nomefile")),LEN(CELL("nomefile")),"*")

         [Se hai creato una nuova cartella di lavoro in una cartella per utilizzare la formula precedente e ottenere l'indirizzo della cartella, potrebbe essere necessario eliminarla in modo che non sia nell'elenco dei file in quella cartella]

2. Vai alla scheda "Formule" e fai clic sull'opzione "Definisci nomi".
3. Nella finestra di dialogo Nuovo nome, utilizzare 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 un elenco di file, utilizzeremo un intervallo denominato nella funzione INDICE.Vai alla cella A3 (o qualsiasi cella con cui vuoi che inizi l'elenco dei nomi) e inserisci la seguente formula:

   =SEERRORE(INDICE(ElencoNomeFile,ROW()-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 un'estensione specifica, cambia semplicemente l'asterisco con quell'estensione di file.Ad esempio, se vuoi solo il file excel, puoi usare *xls* invece di *

Quindi l'indirizzo della cartella che devi usare èC:UsersSumitDesktopCartellaTest*xls*

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

Come funziona?

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

Nella formula INDICE, diamo i nomi dei file come una matrice e utilizziamo la funzione RIGA per restituire il primo, il secondo, il terzo, ecc. nomi di file.

Nota che ho usatoRIGA()-2, poiché iniziamo dalla terza riga.Quindi, quando il numero di riga è 4, ROW()-2 sarà 1 per la prima istanza, 2 per la seconda e così via.

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

Ora, devo dire che il metodo sopra è un po' complicato (ci sono molti passaggi).

Tuttavia, è molto meglio che farlo manualmente.

Tuttavia, se ti senti a tuo agio con 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.

Vantaggi dell'utilizzo delle funzioni definite dall'utente (UDF)是voipuòvolerefunzioneSalva in cartelle di lavoro macro personali e riutilizza facilmente senza dover ripetere questi passaggi ancora e ancora.Puoi anche creare componenti aggiuntivi e condividere questa funzionalità con altri.

Ora lascia che ti dia prima il codice VBA che creerà una funzione per ottenere un 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 Set 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 Next MyFile GetFileNames = Risultato Funzione di fine

Il codice precedente creerà una funzione GetFileNames che può essere utilizzata nel foglio di lavoro (proprio come una normale funzione).

Dove mettere questo codice?

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

Come utilizzare questa funzione?

Di seguito sono riportati i passaggi per utilizzare questa funzionalità 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 vuoi l'elenco, inserisci la seguente formula (l'ho inserita nella cella A3):

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

• Copia e incolla la formula nella cella sottostante per ottenere un elenco di tutti i file.

Nota che ho inserito la posizione della cartella in una cella e poi dentroOttieni nomi fileQuesta cella viene utilizzata in una formula.Puoi anche codificare l'indirizzo della cartella nella formula in questo modo:

=IFERROR(INDEX(GetFileNames("C:\Utenti\IL TUO NOME\Desktop\Cartella di prova"),ROW()-2),"")

Nella formula sopra, abbiamo usato ROW()-2 e iniziamo con la terza riga.Ciò garantisce che quando copio la formula nella cella sottostante, aumenterà di 1.Se inserisci la formula nella prima riga della colonna, puoi semplicemente usare ROW().

Come funziona questa formula?

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

La funzione INDICE viene utilizzata per elencare un nome file per cella, iniziando dal primo.

La funzione IFERROR viene utilizzata per restituire blank invece di #REF!Errore visualizzato quando una formula viene copiata in una cella ma non ci sono più nomi di file da elencare.

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

La formula sopra è utile quando si desidera ottenere un elenco di tutti i nomi di file da una cartella in Excel.

Tuttavia, cosa succede se si desidera ottenere solo il nome di un file video, o solo un file Excel o solo un file che contiene una determinata parola chiave.

In questo caso, puoi utilizzare una funzione leggermente diversa.

Il codice seguente ti consentirà di ottenere tutti i nomi di file che contengono una parola chiave specifica (o 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 To MyFiles.Count) i = 1 per ogni MyFile in MyFiles If InStr(1, MyFile.Name, FileExt) <> 0 Quindi Result(i) = MyFile.Name i = i + 1 End If Next MyFile ReDim Mantieni risultato (da 1 a i - 1) GetFileNamesbyExt = Risultato Fine funzione

Il codice sopra creerà una funzione che può essere utilizzata nel foglio di lavoro" Ottieni nomi file per estensione " (proprio come le normali funzioni).

Questa funzione accetta due parametri: la posizione della cartella e la parola chiave dell'estensione.Restituisce un array di nomi di file corrispondenti all'estensione data.Se non viene specificata alcuna estensione o parola chiave, verranno restituiti 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 qualsiasi oggetto nella cartella di lavoro su cui stai lavorando, vai su Inserisci e fai clic su Modulo.Se non vedi Project Explorer, usa la scorciatoia da tastiera Control + R (tieni premuto il controllo e premi "R").
• Fare doppio clic sull'oggetto del 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 funzionalità 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.
• Nella cella, inserisci l'estensione (o la parola chiave) che desideri siano tutti i nomi di file.L'ho inserito nella cella B1.
• Nella cella in cui vuoi l'elenco, inserisci la seguente formula (l'ho inserita nella cella A3):

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

• Copia e incolla la formula nella cella sottostante per ottenere un elenco di tutti i file.

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