Excel VBA | Get File Names and Details in folders and sub folders

[@compscript7973]

👍Several hours watching videos and I finally found a simple one that works.

[@davesextraneousinformation9807]

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.

[@xtremeExcel]

I am glad that you are happy with the content.😀

[@DalpatMistry]

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?

[@DalpatMistry]

Like your explanation - very well & simple

[@time_capsul.e]

This is what I want .... thank you very much sir......hats off to u

[@xtremeExcel]

Thank you Pranav. Subscribe and share to see more.

[@khaledjwahri6516]

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

[@knass28]

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?

[@kartiksoni4702]

Did the way how to do that?

[@sharmapokkuluri6221]

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

[@vincysigma]

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

[@greenheysg45]

Very helpful indeed. Works perfectly. Many thanks!

[@MrShivdin]

Very good explanation, How can I get the image Dimensions (Length and Width)? Thanks

[@Basavaraja_NT]

Hi Kamal, Thanks for this video. But kindly walk us through how we can give file directory where we need to check ?

[@Mohairp53]

Thx for sharing as I was just looking into to do the same in VBA.

[@davidmacminn8658]

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.

[@safiafaustino9559]

Wow! This works! THANK YOU! Your explanation to each line was superb. Liked and Subscribed!

[@xtremeExcel]

Thank you! Stay Connected. :-)

[@gagarzbipom4254]

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?

[@princechhabra4059]

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?

[@viralpatel3798]

Thanks for sharing such a wonderful macro. It helps me a lot.

[@xtremeExcel]

Great to hear!

[@michaelwade882]

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.

[@glennsanbuenaventura9716]

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

[@miles_mtb]

Very helpful, thank you for sharing.

[@k102stone]

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

[@AlamMT]

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

[@niteshd2291]

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.

[@guidovandewaal1345]

Set objFolder = objFSO.GetFolder(Sheets("Sheet1").Range("B2").Value)

[@teosmi]

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?

[@arielzabatecuizon5147]

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?

[@aliciaojeda5718]

Hi! Thanks for the video! Is there's any way to get the author and last person thay modify each file?

[@paulogoncalves60]

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?

[@latasalagaonkar6302]

Thank you so much I tried many codes but I couldn't able to enter the subfolders thanks a lot😃

[@xtremeExcel]

Stay connected 😃

[@vincysigma]

Thats Wonderful tutorial Sir.. Thank you

[@xtremeExcel]

Thanks Vincent! Stay connected!

[@kalite02]

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.

[@xtremeExcel]

That's a interesting thing to try. Would you like to give a chance?

[@hanshotfirst2363]

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)?

[@selvan1264]

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

[@KeshviGx]

Thanks for this is it possible to adapt this code to pull a list of subfolders only starting with 2020 for example?

[@benvbrunschot]

This is amazing! Have been looking for this for a long time. Thanks a lot :)

[@xtremeExcel]

Great. Stay connected and share our posts to help in achieving our mission of teaching millions of people.

[@vijaymanjrekar6289]

Thanks a lot this is very good session

[@RandyValencia]

Thank you so much. Works great with my file.

[@xtremeExcel]

Great to hear! :-)

[@barunpradhan1278]

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.

[@xtremeExcel]

Maybe.. I have not tested with such huge number of files. Though it is good point to investigate. We can connect.

[@vishcreative0206]

Super nicely explained

[@xtremeExcel]

thank you Vishakha.. stay connected for more such videos..!

[@Twenty-rl5lc]

Good explanation..can we get the no of rows used count.

[@xtremeExcel]

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.

[@whattoputin]

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?

[@xtremeExcel]

yes.. use the function Format( , "DD/MM/YY")

[@MohAboAbdo]

Thank you so match, Sir.

[@xtremeExcel]

so nice of you.

[@jhuniiidaganzo3588]

Great video, i have a question, is it possible to remove the file type extension?

[@xtremeExcel]

Yes it is possible

[@jhuniiidaganzo3588]

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

[@karthickgopinath1466]

@@xtremeExcel Can you please us how to remove the file type extension. Thanks in advance.

[@naresh90sharma]

Very helpful vedio for us Sir can you send code. It's very helpful for me Thanks

[@xtremeExcel]

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.

[@marouamrabet7463]

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.

[@deepanknautiyal5725]

Hi can I perform the same function for multiple paths??

[@xtremeExcel]

yeah.. this can be done.. Little modification in code would be required..

[@teahk3878]

I got permission denied if i want to read the root drive letter, e.g. d:\ etc. How do resolve it ? thx

[@mintmo]

Great video! Would this work on a shared drive?

[@xtremeExcel]

Not sure. You can give a try and let me know.. :-)

[@paulogoncalves60]

@@xtremeExcel It works :)

[@vaibhavdiware]

How Can i add browse Code instead of Paste destination Path In VBA code & Object for Browse code in this Same Code.

[@syedmoniruzzamanpabel3264]

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 ???

[@cody_svk]

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"

[@Fguindi]

@@cody_svk you saved my life there!!! Thank you

[@Jaceyneo8]

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?

[@xtremeExcel]

Hello Neo, Please send your file(with code) to me. Regards, Kamal

[@Jaceyneo8]

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

[@naresh90sharma]

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.

[@xtremeExcel]

point noted. will try to create and upload a tutorial for this soon.

[@James.Brooke]

Great...thanks..here u have a new subscriber👏🏻

[@xtremeExcel]

Thanks for connecting James.. :-)

[@DalpatMistry]

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)

[@angelomartins5032]

Very Good!

[@KobaRiley]

Super helpful... thanks!!!

[@ba177ba18]

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?

[@xtremeExcel]

Seems like you have not included the reference library. Please share your file with me.

[@dikidym]

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

[@kjesu2715]

Thank you bro...

[@rekeemsmith5371]

Awesome video! Very helpful.

[@madelinedimatulac7333]

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"

[@hamada201079]

how do you do the second line of your Excel, where you are able to insert the desired path? Please Help

[@srikanth01guntur]

Would you be able to share the complete code or the template file for ready to use kind

[@anmac6910]

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\ ??

[@masydell]

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

[@xtremeExcel]

I have not used sharepoint much. Can you explain how can I replicate on my system?

[@johnabram4159]

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

[@xtremeExcel]

yes John. you're right..

[@dineshnair7535]

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

[@xtremeExcel]

Hello Dinesh, please share your file.

[@saiganesh4626]

Wow!! You are awesome!!

[@ДобринАрсов]

Very helpful video !!!

[@B1MPro]

THANK YOU VERY MUCH.... :)

[@xtremeExcel]

Stay connected! :-)

[@indiashare4840]

Can we get audio file duration using this

[@kushmakkar8735]

While doing same with taking path from shared folder from network...it too slow...how to resolve this..please help

[@jonasberg3475]

Hi. I get "variable not defined" when running "xlOp"?

[@xtremeExcel]

Hello Jonas, please share your file(and code) with me..

[@ankitkapuria8278]

It's "xlUp" not xlOp

[@abhidarshi]

Thank you so much.

[@casandramccoy-langley5873]

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.

[@xtremeExcel]

Hello Casandra - could you please share an example with me?

[@LuciaHewitt]

Thanks for the video. However I could not get it to read from my external drive. :(

[@xtremeExcel]

is it shared drive or external hard drive?

[@martianboy9886]

Can you create a macro that can transfer all your VBA knowledge from your brain to my brain?? .... Thanks in advance

[@xtremeExcel]

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

[@Yan48612]

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

[@abdulsafwan711]

Is that possible to share this file to my mail

[@savithabevuru7967]

Hi can i get this code ?thank you

[@xtremeExcel]

you'll find here.. github.com/kamalgirdher/xtremeExcel Fork this repo and give a star if you like. :-)

[@parmindermankoo5797]

Downloadable file available?

[@radheprajapati2036]

amazing

[@karthikeyanpushparaj8709]

Hi, can please shown the full codes in module 1.

[@singwithsrsdeepak1544]

Hi How we defined the path by user

[@ritishsharma4969]

how to get these folders in the same order as they are in the main rather than alphabetical order?

[@csbhupindersinghahluwalia9515]

I need only xlsx file name, is that possible

[@naresh90sharma]

Showing error sub or function not defined

[@xtremeExcel]

please share your file at my email id shared in the video/

[@vatsalushah]

Share a file which can be embedded in Excel to run described function

[@hans_albert]

how i can get the code

[@nikhilahuja9302]

can we get files dimension from this ?

[@xtremeExcel]

If you are asking for image files, then YES..

[@nikhilahuja9302]

@@xtremeExcel : So can you tell me the Syntax of that ? I wanted to extract file name and dimensions from a folder.

[@enesnature5486]

Thank you

[@suganthi1418]

This is very useful video. Can you share me the code? Thanks

[@xtremeExcel]

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

[@DarshanPatel-yq9hi]

How to use clearcontents and clear only contents from column A starting from cell A3 until last row

[@xtremeExcel]

Range("A3:A"& cells(rows.count,1).end(xlUp).row)).clearContents

[@TheShinySomething]

I'm attempting to retrieve the duration of audio and video files. Have any ideas?

[@TheShinySomething]

technet.microsoft.com/library/ee176615.aspx Only resource I've found, but I have yet to figure out how to integrate it

[@nicolasfedrigo881]

really very useful, but how do I if I just want pdf files?

[@xtremeExcel]

@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??

[@nicolasfedrigo881]

@@xtremeExcel Yes, thanks

[@parmindermankoo5797]

Macro enabled Excel spreadsheet to create folder names list with size in mb

[@RaspreetSinghJaggi]

Thanks a ton..

[@xtremeExcel]

Thank you Raspreet :-) Stay Connected.

[@bestmobileslaptops9557]

Can u give me this code and excel sheet?

[@xtremeExcel]

See if you could find it here github.com/kamalgirdher/xtremeExcel

[@bestmobileslaptops9557]

@@xtremeExcel ji can i get a macro for copy the tables and update in all other excel sheet?

[@hermawanvenom]

How to get last modified user ?

[@xtremeExcel]

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

[@avsupport5880]

Hi sir Please Add one Row File Duration / File Length

[@xtremeExcel]

for media files?

[@avsupport5880]

@@xtremeExcel Yes

[@kurtdhadamus6706]

This is wat I can use, can you sent de code please

[@xtremeExcel]

are you facing any challenge in following the code as explained in the video?