Excel VBA | Get File Names and Details in folders and sub folders
Вставка
- Опубліковано 6 жов 2024
- Hit LIKE and SUBSCRIBE to our channel if you are interested in more such videos.
In this tutorial, we explained that how you can extract file details (name, size, type, path, created on, modified date) of all the files inside a folder and it's sub-folders.
We have used Recursion to solve this problem.
After watching this video, you'd have fair understanding of how to navigate through files and folders.
👍Several hours watching videos and I finally found a simple one that works.
Thanks for this. I've been trying to do things like this for a long time. I like your solution. It is straightforward and clear. I also learn a lot from your teaching technique where you show everything from setting up the references to seemingly writing the code on the fly and solving issues as they come up, just as in real life.
I am glad that you are happy with the content.😀
This is what I want .... thank you very much sir......hats off to u
Thank you Pranav. Subscribe and share to see more.
Very helpful indeed. Works perfectly. Many thanks!
works perfect! - how do i search specific file (based on filename) and find the latest filename based on last modified date and move to specified location?
Like your explanation - very well & simple
Thank you for this very interesting tutorial, I ask you, in the same subject, to make a video which explains, using vba, how to keep the ten recent files and delete the others I managed to make a backup folder but I still want to keep the ten recent copies only, thank you
Wow! This works! THANK YOU! Your explanation to each line was superb. Liked and Subscribed!
Thank you! Stay Connected. :-)
I am new to VBA and this works great! Thank you! How do I get the button in my Excel sheet to insert the path as shown in your example?
Did the way how to do that?
thank you so much for this great tutorial! it works for me only the get details without subfolder but when I tried to include the code for subfolder it didn't work. why? here's the code.
Option Explicit
Sub listAllFiles()
Dim objFileSystemObject As Scripting.FileSystemObject
Dim objFolder As Scripting.Folder
Set objFileSystemObject = CreateObject("Scripting.FileSystemObject")
Set objFolder = objFileSystemObject.GetFolder("C:\Users\hp\Documents\XXX")
Call GetFileDetails(objFolder)
End Sub
Function GetFileDetails(objFolder As Scripting.FileSystemObject)
Dim objFile As Scripting.File
Dim nextRow As Long
Dim objSubFolder As Scripting.Folder
'Set objFileSystemObject = CreateObject("Scripting.FileSystemObject")
'Set objFolder = objFileSystemObject.GetFolder("C:\Users\hp\Documents\XXX")
nextRow = Cells(Rows.Count, 1).End(xlUp).Row + 1
For Each objFile In objFolder.Files
Cells(nextRow, 1) = objFile.Name
Cells(nextRow, 2) = objFile.Path
Cells(nextRow, 3) = objFile.Size
Cells(nextRow, 4) = objFile.Type
Cells(nextRow, 5) = objFile.DateCreated
Cells(nextRow, 6) = objFile.DateLastModified
nextRow = nextRow + 1
For Each objSubFolder In objFolder.SubFolders
Call GetFileDetails(objFolder)
Next
Next
End Function
Thx for sharing as I was just looking into to do the same in VBA.
This is amazing! Have been looking for this for a long time. Thanks a lot :)
Great. Stay connected and share our posts to help in achieving our mission of teaching millions of people.
Very good explanation, How can I get the image Dimensions (Length and Width)? Thanks
This works very well, however I am having difficulty extracting certain metadata/file properties. "Subject, comments, title, and content status" I am unsure what makes these different from the properties that can be extracted.
Hi! First of all, thank you for your work, really appreciate.
I know nothing about VBA. I try to edit the code to add two more columns: Framerate and Video length, but it`s not working. How can I retrieve those file attributes?
Hi Kamal, Thanks for this video. But kindly walk us through how we can give file directory where we need to check ?
Hi Kamal, Thanks a lot for a very good 'take through' scripting video. Could we add folder and subfolder names to be included amidst in the rows in the 6 column fields? Could you please explain how to include folder and subfolder names listed between these file names.
Regards
Sharma
Very well explained this video.. Will you please make a video of how to search files from the excel sheet(file names will be typed in A1 by user) in sub-folders and paste them in to a destination folder..
Thanks for sharing such a wonderful macro. It helps me a lot.
Great to hear!
Nice video sir ...but i have one query ...how to connect path to text box ..for fetch data ??Please guide me how to create same sheet you have ...
Would be great if you could explain this. In the code you have left the "bluetooth" folder. Kindly show how to connect path to the text box in the file.
Set objFolder = objFSO.GetFolder(Sheets("Sheet1").Range("B2").Value)
Very helpful, thank you for sharing.
Thank you so much. Works great with my file.
Great to hear! :-)
Hi Kamal, thanks for the walk through. Can you also advise what code would be used if I also wanted to check whether these files are password protected or not?
Hi XtremeExcel, Is there a code to pulling the Username of who created the files or folders? Thanks for creating this video, it's a big help. Ken
just a newbie here: Q: At 7:25, you cut "Dim objFile As Scripting.File" and put it one line below, does it matter something? In this actual case, does it really matter you put in one line below after the folder line?
Great video, thanks. One ? How do I change the starting row for the report to start populating? If I want this to start in cell A5.
Great tutorial. However I am encountering one problem. I work in an organization where the number of files are huge and when I run the macro it gets to around 55000 rows after which I get an error. I think there is a stack overflow after certain number of files.
Maybe.. I have not tested with such huge number of files. Though it is good point to investigate. We can connect.
Thats Wonderful tutorial Sir.. Thank you
Thanks Vincent! Stay connected!
Thanks a lot this is very good session
Thanks a lot, I was looking for this solution for a quite long time. Just one question, how do you do the second line of your Excel, where you are able to insert the desired path?
I have a project where I have to find the latest xml file in specified folder (where multiple folders are there with same or diff file with different modified date)
Thank you so much I tried many codes but I couldn't able to enter the subfolders thanks a lot😃
Stay connected 😃
Very Good!
Hi thank you. It helped me for my small project.
Is it possible to add 2 columns, one with the files md4 hash and one the files md5 hash?
Thank you so match, Sir.
so nice of you.
@ 5:30 why do you use createobject that too after you have already referenced MS Scripting Runtime library. You could have just set objFSO = New Scripting.FileSystemObject.
yes John. you're right..
Hello! I love this video, and we actually have used this macro extensively. However, I had a question--how can I find out who the file OWNER is (aka the person who last used/saved the document)?
Hi! Thanks for the video! Is there's any way to get the author and last person thay modify each file?
Super nicely explained
thank you Vishakha.. stay connected for more such videos..!
Nice video, do you know how to pull folder, Filename info from sharepoint library? and how to upload to sharepoint library from local drive. Thank you
Thank you very much for this interssing and useful video..
is it possible to do same thing with a folder in a cloud? for example in google Drive.
That's a interesting thing to try. Would you like to give a chance?
Thanks for this is it possible to adapt this code to pull a list of subfolders only starting with 2020 for example?
Wow!! You are awesome!!
I am get an error on
End(x1up) on below line which show compiler error
nextRow = Cells(Rows.Count, 1).End(x1up).Row + 1
and one line is yellow,that is "Function GetFileDetails(objFolder As Scripting.Folder)"
what is the solution ???
it is not "x1Up" with number one "1", but with the letter "l" as "xlUp"...the letter "L" just looks like "1", but it is small letter "L"
@@cody_svk you saved my life there!!! Thank you
Super helpful... thanks!!!
Awesome video! Very helpful.
Great...thanks..here u have a new subscriber👏🏻
Thanks for connecting James.. :-)
Hey, thank your explanation, i need your help for find date of workbook's name and copy it to a column, example: "workbookname 01 2020" copy to column "date", 01/2020 on this format .
thanks in advance.
Thank you for such a good walkthrough. I have tried following, but it seems that the value for date and time that I get is in serial number. Is there any way that I could get the normal date and time format straight away?
yes.. use the function Format( , "DD/MM/YY")
Sir, Pls upload new video on cell protected after input value in cell. Sheet should be shared in office common drive not by Gmail etc.
Multiple users would be working on the same sheet.
point noted. will try to create and upload a tutorial for this soon.
Very helpful vedio for us
Sir can you send code. It's very helpful for me
Thanks
Hello Naresh. Thank you for writing to us. Please watch complete video and I am sure you would not require the code. :-) In case of questions, feel free to write to me.
THANK YOU VERY MUCH.... :)
Stay connected! :-)
Thank you so much.
Very helpful video !!!
Thank you bro...
Great video, i have a question, is it possible to remove the file type extension?
Yes it is possible
@@xtremeExcel thanks for the reply sir. 😊 Much appreciated, i have one more question, how can i automatically hyperlink the file names to its file path? Ive been trying it but has no luck. Thanks.
@@xtremeExcel Can you please us how to remove the file type extension. Thanks in advance.
Good explanation..can we get the no of rows used count.
yes. we can either use Worksheet.UsedRange property or we can use something like
Cells(rows.count,n).end(xlup).row - to get last row having data in nth column (n can be 1,2,3 ..)
or cells(r,columns.count).end(xlToLeft).column - to get last column having data in rth row.
how do you do the second line of your Excel, where you are able to insert the desired path?
Please Help
Hi can I perform the same function for multiple paths??
yeah.. this can be done.. Little modification in code would be required..
Thanks a lot, I love the Idea and tried myself step by step, but somehow my script is not working (sad).This line with ">" shows me all the time "Run-time error '13' Type mismatch" maybe you know why. Will apriciate any help:
nextrow = Cells(Rows.Count, 1).End(xlUp).Row + 1
> For Each objFile In objFolder.Files
Cells(nextrow, 1) = objFile.Name
Cells(nextrow, 2) = objFile.Path
Cells(nextrow, 3) = objFile.Size
Cells(nextrow, 4) = objFile.Type
Cells(nextrow, 5) = objFile.DateCreated
Cells(nextrow, 6) = objFile.DateLastModified
Thanks for the video. However I could not get it to read from my external drive. :(
is it shared drive or external hard drive?
is it possible to get a list of all directories and all its subdirectories as well ?? for example, i want ALL FILES and ALL DIRECTORIES INCLUDING SUBDIRECTORIES AND ALSO PARENT DIRECTORY of C:\Users\[computer user]\Downloads\ ??
Thank you so much. Need a small help. I need the file names in MS Teams SharePoint. When I give the SharePoint path, VBA says path not found. Can please help me on it. Thanks in advance
I have not used sharepoint much. Can you explain how can I replicate on my system?
Hi, thanks for the video.. I have tried But it took more than 2 min to run the macro for 2400 files+. How can I improved it?
Hello Neo,
Please send your file(with code) to me.
Regards,
Kamal
Hi Kamal, the 1st part is the same. I use sub instead of function which follow your initial script. Let me know if function or sub better. I have added some of the application like calcution, screenupdating, display statusbar etc to reduce the speed to 71sec. But how can I reduce further to below 10sec? Below is the 2nd part script.
Sub GetFileDetails(objFolder As Scripting.Folder, IncludeSubFolders As Boolean)
Dim objFile As Scripting.File
Dim objSubFolder As Scripting.Folder
Dim nextRow As Long
nextRow = Cells(Rows.Count, 1).End(xlUp).Row + 1
For Each objFile In objFolder.Files
If ((objFile.Attributes) = 32) Then
Cells(nextRow, 1) = objFile.Name
Cells(nextRow, 2) = objFile.ParentFolder
Cells(nextRow, 3) = Left(objFile.DateLastModified, InStr(objFile.DateLastModified, " ") - 1)
Cells(nextRow, 4) = objFile.Attributes
Application.StatusBar = (nextRow - 4) & " files found."
nextRow = nextRow + 1
End If
Next objFile
If IncludeSubFolders Then
For Each objSubFolder In objFolder.SubFolders
Call GetFileDetails(objSubFolder, True)
Next objSubFolder
End If
End Sub
Would you be able to share the complete code or the template file for ready to use kind
Great video! Would this work on a shared drive?
Not sure. You can give a try and let me know.. :-)
@@xtremeExcel It works :)
Hello Thanks for such a informative video.
When I tried using this code it is giving me "Runtime error 91 Object variable /Block variable not set"
Can someone help me out with this error ?
Thanks in advance
Hello Dinesh, please share your file.
Thank you
How is it if you just intend to get files beginning to a certain value, say example, "macro". It will just get all filenames beginning with "macro"
Macro enabled Excel spreadsheet to create folder names list with size in mb
I got permission denied if i want to read the root drive letter, e.g. d:\ etc. How do resolve it ? thx
amazing
How Can i add browse Code instead of Paste destination Path In VBA code & Object for Browse code in this Same Code.
Thanks a ton..
Thank you Raspreet :-) Stay Connected.
While doing same with taking path from shared folder from network...it too slow...how to resolve this..please help
Can we get audio file duration using this
Can you create a macro that can transfer all your VBA knowledge from your brain to my brain?? .... Thanks in advance
Yes I can write a macro that will play my videos on your laptop day and night. If you watch them all, that's possible :-p
i pretty much copy the code (except the folder path) on the video but somehow getting Compile error: Variable not defined. on the line 'Function GetFileDetails(objfolder As Scripting.Folder) why is that?
Seems like you have not included the reference library. Please share your file with me.
Hi How we defined the path by user
how to get these folders in the same order as they are in the main rather than alphabetical order?
Hi. I get "variable not defined" when running "xlOp"?
Hello Jonas, please share your file(and code) with me..
It's "xlUp" not xlOp
Share a file which can be embedded in Excel to run described function
Downloadable file available?
Hi there, Can someone help with that: "variable not defined" ...it refers to the Sub FetchFileNames(objFolder As Scripting.Folder, IncludeSubFolders As Boolean)
I basically copied the code shown @1:36
Hi, can please shown the full codes in module 1.
Showing error sub or function not defined
please share your file at my email id shared in the video/
How to use clearcontents and clear only contents from column A starting from cell A3 until last row
Range("A3:A"& cells(rows.count,1).end(xlUp).row)).clearContents
how i can get the code
I need only xlsx file name, is that possible
Hi can i get this code ?thank you
you'll find here.. github.com/kamalgirdher/xtremeExcel
Fork this repo and give a star if you like. :-)
Is that possible to share this file to my mail
Can you help me with recognize text ocr on multiple Ms word files? I need to be able to retitle and date multiple documents (documents are all Ms word and the page is setup the same), please? The date and title have to be from the document itself not the file details in Windows Explorer.
Hello Casandra - could you please share an example with me?
can we get files dimension from this ?
If you are asking for image files, then YES..
@@xtremeExcel : So can you tell me the Syntax of that ? I wanted to extract file name and dimensions from a folder.
really very useful, but how do I if I just want pdf files?
@Nicolas - you can add a if condition while writing in the cell. The condition would be checking if that file name ends with .pdf
Is it clear??
@@xtremeExcel Yes, thanks
This is very useful video. Can you share me the code? Thanks
@Suganthi. I do not have the code handy at the moment. Could you please follow step by step instructions in the video and create the file. It would be great if you share that file with me and I'll put it on a shared repository and make it available for all..
Thanks in advance..
Hi sir Please Add one Row File Duration / File Length
for media files?
@@xtremeExcel Yes
I'm attempting to retrieve the duration of audio and video files. Have any ideas?
technet.microsoft.com/library/ee176615.aspx Only resource I've found, but I have yet to figure out how to integrate it
Can u give me this code and excel sheet?
See if you could find it here github.com/kamalgirdher/xtremeExcel
@@xtremeExcel ji can i get a macro for copy the tables and update in all other excel sheet?
How to get last modified user ?
By default, Microsoft doesn't give option to track last modified user. However you can enable auditing to achieve this. Refer this post: support.microsoft.com/en-us/help/814595/how-to-audit-active-directory-objects-in-windows-server-2003
This is wat I can use, can you sent de code please
are you facing any challenge in following the code as explained in the video?