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

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

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

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

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

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

זה פשוט ודורש זמן רב.

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

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

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

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

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

אם לא, אל תדאג.

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

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

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

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

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

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

         [אם יצרת חוברת עבודה חדשה בתיקייה כדי להשתמש בנוסחה לעיל ולקבל את כתובת התיקיה, ייתכן שיהיה עליך למחוק אותה כדי שהיא לא תהיה ברשימת הקבצים שבתיקיה זו]

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

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

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

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

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

אז כתובת התיקיה שבה אתה צריך להשתמש היאC:UsersSumitDesktopTest Folder*xls*

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

איך זה עובד?

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

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

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

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

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

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

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

יתרונות השימוש בפונקציות בהגדרת משתמש (UDFs)是אתהפחית將פוּנקצִיָהשמור בחוברת מאקרו אישית ועשה שימוש חוזר בקלות מבלי לחזור על השלבים הללו שוב ושוב.אתה יכול גם ליצור תוספות ולשתף את הפונקציונליות הזו עם אחרים.

כעת הרשה לי קודם כל לתת לך את קוד ה-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 = MyFSO. GetFolder(FolderPath) הגדר MyFiles = MyFolder.Files ReDim Result(1 To MyFiles.Count) i = 1 עבור כל MyFile ב-MyFiles Result(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.

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

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

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

פונקציה 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) הגדר MyFiles = MyFolder.Files ReDim Result(1 To MyFiles.Count) i = 1 עבור כל MyFile ב-MyFiles If InStr(1, MyFile.Name, FileExt) <> 0 Then Result(i) = MyFile.Name i = i + 1 End If Next MyFile ReDim Preserve Result(1 To i - 1) GetFileNamesbyExt = Result End Function

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

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

תחביר: =GetFileNamesbyExt("Folder Location","Extension")

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

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

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

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

להלן השלבים לשימוש בתכונה זו בגליון עבודה:

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

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

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

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