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

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

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

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

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

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

Είναι απλό και πολύ χρονοβόρο.

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

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

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

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

άκουσε για αυτόΛειτουργία FILES;

Εάν δεν το κάνετε, μην ανησυχείτε.

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

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

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

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

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

         =REPLACE(CELL("όνομα αρχείου"),FIND("[",CELL("όνομα αρχείου")),LEN(CELL("όνομα αρχείου")),"*")

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

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

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

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

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

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

Άρα η διεύθυνση φακέλου που πρέπει να χρησιμοποιήσετε είναιC:UsersSumitDesktopTest Folder*xls*

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

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

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

Στον τύπο INDEX, δίνουμε τα ονόματα αρχείων ως πίνακα και χρησιμοποιούμε τη συνάρτηση ROW για να επιστρέψουμε τα ονόματα αρχείων πρώτου, δεύτερου, τρίτου κ.λπ.

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

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

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

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

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

Οφέλη από τη χρήση των Λειτουργιών που καθορίζονται από το χρήστη (UDF)是Εσείςμπορώ將λειτουργίαΑποθηκεύστε τα σε προσωπικά βιβλία εργασίας μακροεντολών και επαναχρησιμοποιήστε εύκολα χωρίς να χρειάζεται να επαναλαμβάνετε αυτά τα βήματα ξανά και ξανά.Μπορείτε επίσης να δημιουργήσετε πρόσθετα και να μοιραστείτε αυτήν τη λειτουργία με άλλους.

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

Συνάρτηση GetFileNames(ByVal FolderPath ως συμβολοσειρά) Ως παραλλαγή Dim αποτέλεσμα ως παραλλαγή Dim i ως ακέραιος αριθμός Dim MyFile ως αντικείμενο Dim MyFSO ως αντικείμενο Dim MyFolder ως αντικείμενο Dim MyFiles ως αντικείμενο Αντικείμενο MyFSO = CreateObject("Scripting.ObSOFileSy") GetFolder(FolderPath) Ορισμός MyFiles = MyFolder.Files ReDim Result(1 To MyFiles.Count) i = 1 Για κάθε MyFile In MyFiles Αποτέλεσμα(i) = MyFile.Name i = i + 1 Επόμενο MyFile GetFileNames = Αποτέλεσμα Τέλος Λειτουργία

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

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

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

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

Ακολουθούν τα βήματα για να χρησιμοποιήσετε αυτήν τη δυνατότητα σε ένα φύλλο εργασίας:

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

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

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

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

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

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

Πώς λειτουργεί αυτή η φόρμουλα;

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

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

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

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

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

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

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

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

Συνάρτηση GetFileNamesbyExt(ByVal FolderPath ως συμβολοσειρά, FileExt ως συμβολοσειρά) Ως παραλλαγή Dim αποτέλεσμα Ως παραλλαγή Dim i Ως ακέραιος Dim MyFile ως αντικείμενο Dim MyFSO ως αντικείμενο Dim MyFolder ως αντικείμενο Dim MyFiles ως αντικείμενο Set MyFSO =Scriateb("Scriateb) MyFolder = MyFSO.GetFolder(FolderPath) Ορισμός MyFiles = MyFolder.Files ReDim Result(1 To MyFiles.Count) i = 1 Για κάθε MyFile στο MyFiles If InStr(1, MyFile.Name, FileExt) <> 0 Στη συνέχεια Result(i) = MyFile.Name i = i + 1 End If Next MyFile ReDim Reserve Result(1 To i - 1) GetFileNamesbyExt = Αποτέλεσμα Τέλος Συνάρτηση

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

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

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

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

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

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

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

Ακολουθούν τα βήματα για να χρησιμοποιήσετε αυτήν τη δυνατότητα σε ένα φύλλο εργασίας:

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

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

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

Τι γίνεται με εσέναΤυχόν κόλπα του Excel που χρησιμοποιείτε για να απλοποιήσετε τη ζωή σας.Θα ήθελα πολύ να μάθω από εσάς.Μοιραστείτε το στην ενότητα σχολίων!