קבל את רשימת שמות הקבצים מתיקייה ב- Excel (עם ובלי VBA)

ביום הראשון שלי בעבודתי בחברת ייעוץ קטנה, איישתי בפרויקט קצר למשך שלושה ימים.

העבודה הייתה פשוטה.

היו הרבה תיקיות בכונן הרשת ובכל תיקיה היו בה מאות קבצים.

הייתי צריך לבצע את שלושת השלבים הבאים:

1. בחר את הקובץ והעתק את שמו.
2. הדבק שם זה בתא ב- Excel ולחץ על Enter.
3. עבור לקובץ הבא וחזור על שלב 1 ו -2.

נשמע פשוט נכון?

זה היה - פשוט ובזבוז זמן עצום.

מה שלקח לי שלושה ימים יכול היה להיעשות תוך כמה דקות אם הייתי יודע את הטכניקות הנכונות.

במדריך זה אראה לך דרכים שונות להפוך את כל התהליך הזה לסופר מהיר וסופר קל (עם ובלי VBA).

מגבלות השיטות המוצגות במדריך זה: בעזרת הטכניקות המוצגות להלן תוכל לקבל רק את שמות הקבצים בתוך התיקיה הראשית. לא תקבל את שמות הקבצים בתיקיות המשנה בתיקייה הראשית. להלן דרך לקבל שמות של קבצים מתיקיות ותיקיות משנה באמצעות Power Query

שימוש בפונקציה FILES כדי לקבל רשימה של שמות קבצים מתיקיה

שמעתי על פונקציית FILES לפני?

אל תדאג אם לא.

זה מימי ילדותם של גיליונות אלקטרוניים של Excel (נוסחה של גירסה 4).

נוסחה זו אמנם לא פועלת בתאי גליון העבודה, אך היא עדיין פועלת בטווחים עם שם. נשתמש בעובדה זו כדי לקבל את רשימת שמות הקבצים מתיקיה שצוינה.

עכשיו, נניח שיש לך תיקייה עם השם - 'תיקיית בדיקה'על שולחן העבודה, ואתה רוצה לקבל רשימה של שמות קבצים עבור כל הקבצים בתיקייה זו.

להלן השלבים שיעניקו לך את שמות הקבצים מתיקיה זו:

1. בתא A1, הזן את הכתובת המלאה של התיקיה ואחריה סימן כוכבית (*)
   • לדוגמה, אם התיקייה שלך בכונן C, הכתובת תיראה כך
     C: \ Users \ Sumit \ Desktop \ Test Folder \*
   • אם אינך בטוח כיצד להשיג את כתובת התיקיה, השתמש בשיטה הבאה:
     • • בתיקיה שממנה ברצונך לקבל את שמות הקבצים, צור חוברת עבודה חדשה של Excel או פתח חוברת עבודה קיימת בתיקיה והשתמש בנוסחה שלהלן בכל תא. נוסחה זו תיתן לך את כתובת התיקיה ותוסיף סימן כוכביות (*) בסוף. עכשיו אתה יכול להעתיק ולהדביק (להדביק כערך) את הכתובת הזו בכל תא (A1 בדוגמה זו) בחוברת העבודה שבה אתה רוצה את שמות הקבצים.

         = החלף (CELL ("שם קובץ"), FIND ("[", CELL ("שם קובץ")), LEN (CELL ("שם קובץ")), "*")

         [אם יצרת חוברת עבודה חדשה בתיקייה כדי להשתמש בנוסחה שלעיל ולקבל את כתובת התיקיה, ייתכן שתרצה למחוק אותה כך שלא תופיע ברשימת הקבצים בתיקייה זו]
2. עבור לכרטיסייה 'נוסחאות' ולחץ על האפשרות 'הגדר שם'.
3. בתיבת הדו -שיח שם חדש, השתמש בפרטים הבאים
   • שם: FileNameList (אתה מוזמן לבחור איזה שם שאתה אוהב)
   • היקף: חוברת עבודה
   • הכוונה ל: = קבצים (גיליון 1! $ A $ 1)
4. כעת כדי לקבל את רשימת הקבצים, נשתמש בטווח הנקוב בתוך פונקציית INDEX. עבור אל תא A3 (או כל תא שבו ברצונך להתחיל ברשימת השמות) והזן את הנוסחה הבאה:

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

5. גרור את זה למטה וזה ייתן לך רשימה של כל שמות הקבצים בתיקייה

רוצה לחלץ קבצים עם סיומת ספציפית ??

אם אתה רוצה לקבל את כל הקבצים עם סיומת מסוימת, פשוט שנה את הכוכבית עם סיומת הקובץ. לדוגמה, אם אתה רוצה קבצי אקסל בלבד, תוכל להשתמש ב- * xls * במקום ב- *

כך שכתובת התיקיה שאתה צריך להשתמש בה תהיה C: \ Users \ Sumit \ Desktop \ Test Folder \*xls*

באופן דומה, עבור קבצי מסמכי word, השתמש ב- *doc *

איך זה עובד?

נוסחת FILES מאחזרת את שמות כל הקבצים של התוסף שצוין בתיקייה שצוינה.

בנוסחת INDEX, נתנו את שמות הקבצים כמערך ומחזירים את שמות הקבצים הראשונה, השנייה, השלישית וכן הלאה באמצעות הפונקציה ROW.

שים לב שהשתמשתי שורה ()-2, כשהתחלנו מהשורה השלישית ואילך. אז ROW ()-2 יהיה 1 עבור המופע הראשון, 2 עבור המופע השני כאשר מספר השורה הוא 4, וכן הלאה וכן הלאה.

צפה בוידאו - קבל רשימת שמות קבצים מתיקיה ב- Excel

שימוש ב- VBA קבל רשימה של כל שמות הקבצים מתיקיה

עכשיו, אני חייב לומר שהשיטה לעיל מעט מורכבת (עם מספר שלבים).

עם זאת, זה הרבה יותר טוב מאשר לעשות זאת באופן ידני.

אבל אם אתה מרגיש בנוח להשתמש ב- VBA (או אם אתה מסוגל לבצע את השלבים המדויקים שאני אפרט להלן), תוכל ליצור פונקציה מותאמת אישית (UDF) שתוכל להביא לך בקלות את שמות כל הקבצים.

היתרון בשימוש א User דמשופשף וunction (UDF) היא שתוכל לשמור את הפונקציה בחוברת עבודה מאקרו אישית ולהשתמש בה בקלות מבלי לחזור על השלבים שוב ושוב. תוכל גם ליצור תוסף ולשתף פונקציה זו עם אחרים.

עכשיו תן לי קודם כל לתת לך את קוד ה- VBA שייצור פונקציה כדי לקבל את רשימת כל שמות הקבצים מתיקיה ב- Excel.

פונקציה GetFileNames (ByVal FolderPath כמחרוזת) כ- Variant Dim תוצאה כ- Variant Dim i As Integer Dim MyFile כאובייקט Dim MyFSO כאובייקט Dim MyFolder כאובייקט Dim MyFiles As Object Set MyFSO = CreateObject ("Scripting.FileSystemObject") הגדר MyFolder = MyFSO. GetFolder (FolderPath) הגדר את MyFiles = MyFolder.Files ReDim Result (1 ל- MyFiles.Count) i = 1 עבור כל MyFile בתוצאה MyFiles (i) = MyFile.Name i = i + 1 הבא MyFile GetFileNames = תוצאה סיום התוצאה

הקוד לעיל ייצור פונקציה GetFileNames שניתן להשתמש בה בדפי העבודה (בדיוק כמו פונקציות רגילות).

איפה לשים את הקוד הזה?

בצע את השלבים שלהלן כדי להעתיק קוד זה בעורך VB.

• עבור לכרטיסייה מפתחים.
• לחץ על הלחצן Visual Basic. פעולה זו תפתח את עורך VB.
• בעורך VB, לחץ באמצעות לחצן העכבר הימני על כל אחד מהאובייקטים של חוברת העבודה שבה אתה עובד, עבור אל הוספה ולחץ על מודול. אם אינך רואה את סייר הפרויקטים, השתמש בקיצור המקשים Control + R (החזק את מקש השליטה ולחץ על מקש 'R').
• לחץ פעמיים על אובייקט המודול והעתק והדבק את הקוד לעיל בחלון קוד המודול.

כיצד להשתמש בפונקציה זו?

להלן השלבים לשימוש בפונקציה זו בגליון עבודה:

• בכל תא, הזן את כתובת התיקיה של התיקיה ממנה ברצונך לרשום את שמות הקבצים.
• בתא שבה אתה רוצה את הרשימה, הזן את הנוסחה הבאה (אני מזין אותה בתא A3):

  = 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.

אבל מה אם אתה רוצה לקבל את השמות של קבצי הווידאו בלבד, או רק את קבצי האקסל, או רק את שמות הקבצים המכילים מילת מפתח ספציפית.

במקרה זה, תוכל להשתמש בפונקציה מעט שונה.

להלן הקוד שיאפשר לך לקבל את כל שמות הקבצים עם מילת מפתח ספציפית (או של סיומת ספציפית).

פונקציה GetFileNamesbyExt (ByVal FolderPath כמחרוזת, FileExt כמחרוזת) כתוצאה משתנה Dim כ Variant Dim i As Integer Dim MyFile As Object Dim MyFSO כ אובייקט Dim MyFolder As Object Dim MyFiles As Object Set MyFSO = CreateObject ("Scripting.FileSystemObject") להגדיר MyFolder = MyFSO.GetFolder (FolderPath) הגדר את MyFiles = MyFolder.Files ReDim Result (1 ל- MyFiles.Count) i = 1 עבור כל MyFile ב- MyFiles אם InStr (1, MyFile.Name, FileExt) 0 ואז התוצאה (i) = MyFile .Name i = i + 1 End If Next MyFile ReDim תשמור את התוצאה (1 עד i - 1) GetFileNamesbyExt = פונקציית סיום התוצאה

הקוד לעיל ייצור פונקציה 'GetFileNamesbyExt'שניתן להשתמש בו בדפי העבודה (בדיוק כמו פונקציות רגילות).

פונקציה זו לוקחת שני ארגומנטים - מיקום התיקיה ומילת המפתח של הסיומת. הוא מחזיר מערך של שמות קבצים התואמים את הסיומת הנתונה. אם לא צוינו סיומת או מילת מפתח, היא תחזיר את כל שמות הקבצים בתיקייה שצוינה.

תחביר: = GetFileNamesbyExt ("מיקום תיקיה", "הרחבה")

איפה לשים את הקוד הזה?

בצע את השלבים שלהלן כדי להעתיק קוד זה בעורך VB.

• עבור לכרטיסייה מפתחים.
• לחץ על הלחצן Visual Basic. פעולה זו תפתח את עורך VB.
• בעורך VB, לחץ באמצעות לחצן העכבר הימני על כל אחד מהאובייקטים של חוברת העבודה שבה אתה עובד, עבור אל הוספה ולחץ על מודול. אם אינך רואה את סייר הפרויקטים, השתמש בקיצור המקשים Control + R (החזק את מקש השליטה ולחץ על מקש 'R').
• לחץ פעמיים על אובייקט המודול והעתק והדבק את הקוד לעיל בחלון קוד המודול.

כיצד להשתמש בפונקציה זו?

להלן השלבים לשימוש בפונקציה זו בגליון עבודה:

• בכל תא, הזן את כתובת התיקיה של התיקיה ממנה ברצונך לרשום את שמות הקבצים. הזנתי את זה בתא A1.
• בתא, הזן את הסיומת (או מילת המפתח), שעבורה ברצונך לרשום את כל שמות הקבצים. הזנתי את זה בתא B1.
• בתא שבה אתה רוצה את הרשימה, הזן את הנוסחה הבאה (אני מזין אותה בתא A3):

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

• העתק והדבק את הנוסחה בתאים למטה כדי לקבל רשימה של כל הקבצים.

מה איתך? כל טריקים של אקסל שאתה משתמש בהם כדי להפוך את החיים לקלים. אשמח ללמוד ממך. שתף אותו בקטע ההערות!