Λήψη της λίστας των ονομάτων αρχείων από έναν φάκελο στο Excel (με και χωρίς VBA)

Την πρώτη μου μέρα στη δουλειά μου σε μια μικρή εταιρεία συμβούλων, στελεχώθηκα σε ένα σύντομο έργο για τρεις ημέρες.

Η δουλειά ήταν απλή.

Υπήρχαν πολλοί φάκελοι στη μονάδα δίσκου δικτύου και κάθε φάκελος είχε εκατοντάδες αρχεία.

Έπρεπε να ακολουθήσω αυτά τα τρία βήματα:

1. Επιλέξτε το αρχείο και αντιγράψτε το όνομά του.
2. Επικολλήστε αυτό το όνομα σε ένα κελί στο Excel και πατήστε Enter.
3. Μεταβείτε στο επόμενο αρχείο και επαναλάβετε το βήμα 1 & 2.

Ακούγεται απλό σωστά;

--Ταν - Απλό και τεράστιο χάσιμο χρόνου.

Αυτό που μου πήρε τρεις μέρες θα μπορούσε να είχε γίνει σε λίγα λεπτά αν ήξερα τις σωστές τεχνικές.

Σε αυτό το σεμινάριο, θα σας δείξω διαφορετικούς τρόπους για να κάνετε όλη αυτή τη διαδικασία εξαιρετικά γρήγορη και εξαιρετικά εύκολη (με και χωρίς VBA).

Περιορισμοί των μεθόδων που εμφανίζονται σε αυτό το σεμινάριο: Με τις τεχνικές που φαίνονται παρακάτω, θα μπορείτε να λάβετε μόνο τα ονόματα των αρχείων στον κύριο φάκελο. Δεν θα λάβετε τα ονόματα των αρχείων στους υποφακέλους στον κύριο φάκελο. Ακολουθεί ένας τρόπος για να λάβετε ονόματα αρχείων από φακέλους και υποφακέλους χρησιμοποιώντας το Power Query

Χρήση της λειτουργίας FILES για λήψη λίστας ονομάτων αρχείων από ένα φάκελο

Ακούστηκε για FILES συνάρτηση πριν?

Μην ανησυχείτε αν δεν έχετε.

Είναι από την παιδική ηλικία των υπολογιστικών φύλλων Excel (τύπος έκδοσης 4).

Ενώ αυτός ο τύπος δεν λειτουργεί στα κελιά του φύλλου εργασίας, εξακολουθεί να λειτουργεί σε ονομασμένες περιοχές. Θα χρησιμοποιήσουμε αυτό το γεγονός για να λάβουμε τη λίστα με τα ονόματα αρχείων από έναν καθορισμένο φάκελο.

Τώρα, ας υποθέσουμε ότι έχετε έναν φάκελο με το όνομα - ‘Δοκιμή φακέλου«Στην επιφάνεια εργασίας και θέλετε να λάβετε μια λίστα με ονόματα αρχείων για όλα τα αρχεία σε αυτόν τον φάκελο.

Ακολουθούν τα βήματα που θα σας δώσουν τα ονόματα αρχείων από αυτόν τον φάκελο:

1. Στο κελί Α1, εισαγάγετε την πλήρη διεύθυνση του φακέλου ακολουθούμενη από ένα αστερίσκο (*)
   • Για παράδειγμα, εάν ο φάκελός σας στη μονάδα δίσκου C, τότε η διεύθυνση θα μοιάζει
     C: \ Users \ Sumit \ Desktop \ Test Folder \*
   • Εάν δεν είστε σίγουροι πώς να λάβετε τη διεύθυνση φακέλου, χρησιμοποιήστε την ακόλουθη μέθοδο:
     • • Στο φάκελο από τον οποίο θέλετε να λάβετε τα ονόματα αρχείων, είτε δημιουργήστε ένα νέο βιβλίο εργασίας του Excel είτε ανοίξτε ένα υπάρχον βιβλίο εργασίας στο φάκελο και χρησιμοποιήστε τον παρακάτω τύπο σε οποιοδήποτε κελί. Αυτός ο τύπος θα σας δώσει τη διεύθυνση φακέλου και προσθέτει ένα σύμβολο αστερίσκων (*) στο τέλος. Τώρα μπορείτε να αντιγράψετε-επικολλήσετε (επικολλήσετε ως τιμή) αυτήν τη διεύθυνση σε οποιοδήποτε κελί (Α1 σε αυτό το παράδειγμα) στο βιβλίο εργασίας στο οποίο θέλετε τα ονόματα αρχείων.

         = ΑΝΤΙΚΑΤΑΣΤΑΣΗ (ΚΥΤΤΑΡΙ ("όνομα αρχείου"), ΕΥΡΕΣΗ ("[", ΚΥΤΤΑΡΙ ("όνομα αρχείου")), LEN (ΚΥΤΤΑΡΙ ("όνομα αρχείου")), "*")

         [Εάν έχετε δημιουργήσει ένα νέο βιβλίο εργασίας στο φάκελο για να χρησιμοποιήσετε τον παραπάνω τύπο και να λάβετε τη διεύθυνση φακέλου, μπορεί να θέλετε να το διαγράψετε έτσι ώστε να μην εμφανίζεται στη λίστα των αρχείων σε αυτόν το φάκελο]
2. Μεταβείτε στην καρτέλα "Τύποι" και κάντε κλικ στην επιλογή "Ορισμός ονόματος".
3. Στο πλαίσιο διαλόγου Νέο όνομα, χρησιμοποιήστε τις ακόλουθες λεπτομέρειες
   • Όνομα: FileNameList (μη διστάσετε να επιλέξετε όποιο όνομα σας αρέσει)
   • Πεδίο εφαρμογής: Τετράδιο εργασίας
   • Αναφέρεται σε: = FILES (Sheet1! $ A $ 1)
4. Τώρα για να λάβετε τη λίστα των αρχείων, θα χρησιμοποιήσουμε το ονομαστικό εύρος εντός μιας συνάρτησης INDEX. Μεταβείτε στο κελί A3 (ή σε οποιοδήποτε κελί όπου θέλετε να ξεκινήσει η λίστα με τα ονόματα) και εισαγάγετε τον ακόλουθο τύπο:

   = IFERROR (INDEX (FileNameList, ROW ()-2), "")

5. Σύρετε το προς τα κάτω και θα σας δώσει μια λίστα με όλα τα ονόματα αρχείων στο φάκελο

Θέλετε να εξαγάγετε αρχεία με μια συγκεκριμένη επέκταση ;;

Εάν θέλετε να λάβετε όλα τα αρχεία με μια συγκεκριμένη επέκταση, απλώς αλλάξτε τον αστερίσκο με αυτήν την επέκταση αρχείου. Για παράδειγμα, εάν θέλετε μόνο αρχεία excel, μπορείτε να χρησιμοποιήσετε * xls * αντί για *

Έτσι, η διεύθυνση φακέλου που πρέπει να χρησιμοποιήσετε θα ήταν C: \ Users \ Sumit \ Desktop \ Test Folder \*xls*

Ομοίως, για αρχεία εγγράφων word, χρησιμοποιήστε *doc *

Πως λειτουργεί αυτό?

Ο τύπος FILES ανακτά τα ονόματα όλων των αρχείων της καθορισμένης επέκτασης στον καθορισμένο φάκελο.

Στον τύπο INDEX, δώσαμε τα ονόματα αρχείων ως πίνακα και επιστρέφουμε το 1ο, 2ο, 3ο όνομα αρχείων και ούτω καθεξής χρησιμοποιώντας τη συνάρτηση ROW.

Σημειώστε ότι έχω χρησιμοποιήσει ΣΕΙΡΑ ()-2, καθώς ξεκινήσαμε από την τρίτη σειρά και μετά. Έτσι ROW ()-2 θα ήταν 1 για την πρώτη περίπτωση, 2 για τη δεύτερη περίπτωση όταν ο αριθμός σειράς είναι 4, και ούτω καθεξής και ούτω καθεξής.

Παρακολούθηση βίντεο - Λήψη λίστας ονομάτων αρχείων από ένα φάκελο στο Excel

Χρησιμοποιώντας το VBA Λάβετε μια λίστα με όλα τα ονόματα αρχείων από έναν φάκελο

Τώρα, πρέπει να πω ότι η παραπάνω μέθοδος είναι λίγο περίπλοκη (με μια σειρά βημάτων).

Ωστόσο, είναι πολύ καλύτερο από το να το κάνετε χειροκίνητα.

Αλλά αν είστε άνετοι με τη χρήση του VBA (ή αν είστε καλοί στο να ακολουθείτε τα ακριβή βήματα που πρόκειται να παραθέσω παρακάτω), μπορείτε να δημιουργήσετε μια προσαρμοσμένη λειτουργία (UDF) που μπορεί εύκολα να σας δώσει τα ονόματα όλων των αρχείων.

Το όφελος από τη χρήση α Uσερ ρεοριοθετημένος φάunction (UDF) είναι ότι μπορείτε να αποθηκεύσετε τη λειτουργία σε ένα προσωπικό βιβλίο μακροεντολών και να την επαναχρησιμοποιήσετε εύκολα χωρίς να επαναλάβετε τα βήματα ξανά και ξανά. Μπορείτε επίσης να δημιουργήσετε ένα πρόσθετο και να μοιραστείτε αυτήν τη λειτουργία με άλλους.

Τώρα επιτρέψτε μου να σας δώσω πρώτα τον κωδικό VBA που θα δημιουργήσει μια συνάρτηση για να λάβετε τη λίστα με όλα τα ονόματα αρχείων από ένα φάκελο στο Excel.

Λειτουργία 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 GetFolder (FolderPath) Ορίστε MyFiles = MyFolder.Files ReDim Result (1 To MyFiles.Count) i = 1 Για κάθε MyFile σε MyFiles Αποτέλεσμα (i) = MyFile.Name i = i + 1 Next MyFile GetFileNames = Result End function

Ο παραπάνω κώδικας θα δημιουργήσει μια συνάρτηση GetFileNames που μπορεί να χρησιμοποιηθεί στα φύλλα εργασίας (όπως και οι κανονικές συναρτήσεις).

Πού να βάλω αυτόν τον κωδικό;

Ακολουθήστε τα παρακάτω βήματα για να αντιγράψετε αυτόν τον κώδικα στο VB Editor.

• Μεταβείτε στην καρτέλα Προγραμματιστής.
• Κάντε κλικ στο κουμπί Visual Basic. Αυτό θα ανοίξει το πρόγραμμα επεξεργασίας VB.
• Στο πρόγραμμα επεξεργασίας VB, κάντε δεξί κλικ σε οποιοδήποτε από τα αντικείμενα του βιβλίου εργασίας στο οποίο εργάζεστε, μεταβείτε στην επιλογή Εισαγωγή και κάντε κλικ στην ενότητα. Εάν δεν βλέπετε το Project Explorer, χρησιμοποιήστε τη συντόμευση πληκτρολογίου Control + R (κρατήστε πατημένο το πλήκτρο ελέγχου και πατήστε το πλήκτρο ‘R’).
• Κάντε διπλό κλικ στο αντικείμενο Module και αντιγράψτε και επικολλήστε τον παραπάνω κώδικα στο παράθυρο κωδικού module.

Πώς να χρησιμοποιήσετε αυτήν τη λειτουργία;

Παρακάτω είναι τα βήματα για να χρησιμοποιήσετε αυτήν τη συνάρτηση σε ένα φύλλο εργασίας:

• Σε οποιοδήποτε κελί, εισαγάγετε τη διεύθυνση φακέλου του φακέλου από τον οποίο θέλετε να παραθέσετε τα ονόματα αρχείων.
• Στο κελί όπου θέλετε τη λίστα, εισαγάγετε τον ακόλουθο τύπο (τον εισάγω στο κελί Α3):

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

• Αντιγράψτε και επικολλήστε τον τύπο στα παρακάτω κελιά για να λάβετε μια λίστα με όλα τα αρχεία.

Σημειώστε ότι εισήγαγα τη θέση του φακέλου σε ένα κελί και στη συνέχεια χρησιμοποίησα αυτό το κελί στο GetFileNames τύπος. Μπορείτε επίσης να κωδικοποιήσετε τη διεύθυνση φακέλου στον τύπο όπως φαίνεται παρακάτω:

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

Στον παραπάνω τύπο, χρησιμοποιήσαμε ROW ()-2 και ξεκινήσαμε από την τρίτη σειρά και μετά. Αυτό διασφάλισε ότι καθώς αντιγράφω τον τύπο στα παρακάτω κελιά, θα αυξηθεί κατά 1. Σε περίπτωση που εισάγετε τον τύπο στην πρώτη σειρά μιας στήλης, μπορείτε απλά να χρησιμοποιήσετε το ROW ().

Πώς λειτουργεί αυτός ο τύπος;

Ο τύπος GetFileNames επιστρέφει έναν πίνακα που περιέχει τα ονόματα όλων των αρχείων στο φάκελο.

Η συνάρτηση INDEX χρησιμοποιείται για να παραθέσει ένα όνομα αρχείου ανά κελί, ξεκινώντας από το πρώτο.

Η συνάρτηση IFERROR χρησιμοποιείται για να επιστρέψει κενό αντί για #REF! σφάλμα που εμφανίζεται όταν αντιγράφεται ένας τύπος σε ένα κελί, αλλά δεν υπάρχουν άλλα ονόματα αρχείων για λίστα.

Χρησιμοποιώντας το VBA Λάβετε μια λίστα με όλα τα ονόματα αρχείων με μια συγκεκριμένη επέκταση

Ο παραπάνω τύπος λειτουργεί τέλεια όταν θέλετε να λάβετε μια λίστα με όλα τα ονόματα αρχείων από ένα φάκελο στο Excel.

Αλλά τι γίνεται αν θέλετε να λάβετε τα ονόματα μόνο των αρχείων βίντεο ή μόνο των αρχείων Excel ή μόνο τα ονόματα αρχείων που περιέχουν μια συγκεκριμένη λέξη -κλειδί.

Σε αυτήν την περίπτωση, μπορείτε να χρησιμοποιήσετε μια ελαφρώς διαφορετική λειτουργία.

Παρακάτω είναι ο κώδικας που θα σας επιτρέψει να λάβετε όλα τα ονόματα αρχείων με μια συγκεκριμένη λέξη -κλειδί (ή συγκεκριμένης επέκτασης).

Λειτουργία 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.FileSy) MyFolder = MyFSO.GetFolder (FolderPath) Set MyFiles = MyFolder.Files ReDim Result (1 To MyFiles.Count) i = 1 Για κάθε MyFile στα MyFiles If InStr (1, MyFile.Name, FileExt) 0 Στη συνέχεια Αποτέλεσμα (i) = MyFile .Name i = i + 1 End If Next MyFile ReDim Reserve Reserve (1 To i - 1) GetFileNamesbyExt = Result End End

Ο παραπάνω κώδικας θα δημιουργήσει μια συνάρτηση "GetFileNamesbyExt«Που μπορεί να χρησιμοποιηθεί στα φύλλα εργασίας (όπως και οι κανονικές συναρτήσεις).

Αυτή η συνάρτηση λαμβάνει δύο ορίσματα - τη θέση του φακέλου και τη λέξη -κλειδί επέκτασης. Επιστρέφει μια σειρά από ονόματα αρχείων που ταιριάζουν με τη δεδομένη επέκταση. Εάν δεν έχει καθοριστεί επέκταση ή λέξη -κλειδί, θα επιστρέψει όλα τα ονόματα αρχείων στον καθορισμένο φάκελο.

Σύνταξη: = GetFileNamesbyExt ("Θέση φακέλου", "Επέκταση")

Πού να βάλω αυτόν τον κωδικό;

Ακολουθήστε τα παρακάτω βήματα για να αντιγράψετε αυτόν τον κώδικα στο πρόγραμμα επεξεργασίας VB.

• Μεταβείτε στην καρτέλα Προγραμματιστής.
• Κάντε κλικ στο κουμπί Visual Basic. Αυτό θα ανοίξει το πρόγραμμα επεξεργασίας VB.
• Στο πρόγραμμα επεξεργασίας VB, κάντε δεξί κλικ σε οποιοδήποτε από τα αντικείμενα του βιβλίου εργασίας στο οποίο εργάζεστε, μεταβείτε στην επιλογή Εισαγωγή και κάντε κλικ στην ενότητα. Εάν δεν βλέπετε το Project Explorer, χρησιμοποιήστε τη συντόμευση πληκτρολογίου Control + R (κρατήστε πατημένο το πλήκτρο ελέγχου και πατήστε το πλήκτρο ‘R’).
• Κάντε διπλό κλικ στο αντικείμενο Module και αντιγράψτε και επικολλήστε τον παραπάνω κώδικα στο παράθυρο κωδικού module.

Πώς να χρησιμοποιήσετε αυτήν τη λειτουργία;

Παρακάτω είναι τα βήματα για να χρησιμοποιήσετε αυτήν τη συνάρτηση σε ένα φύλλο εργασίας:

• Σε οποιοδήποτε κελί, εισαγάγετε τη διεύθυνση φακέλου του φακέλου από τον οποίο θέλετε να παραθέσετε τα ονόματα αρχείων. Το έχω καταχωρίσει στο κελί Α1.
• Σε ένα κελί, εισαγάγετε την επέκταση (ή τη λέξη -κλειδί), για την οποία θέλετε όλα τα ονόματα αρχείων. Το έχω εισαγάγει στο κελί Β1.
• Στο κελί όπου θέλετε τη λίστα, εισαγάγετε τον ακόλουθο τύπο (τον εισάγω στο κελί Α3):

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

• Αντιγράψτε και επικολλήστε τον τύπο στα παρακάτω κελιά για να λάβετε μια λίστα με όλα τα αρχεία.

Εσυ ΠΩΣ ΕΙΣΑΙ? Οποιαδήποτε κόλπα του Excel χρησιμοποιείτε για να κάνετε τη ζωή εύκολη. Θα ήθελα πολύ να μάθω από εσάς. Μοιραστείτε το στην ενότητα σχολίων!