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.
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?
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?
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.
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 ???
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.
@ 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.
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.
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
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?
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?
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
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 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)
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
@@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.
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?
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 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?
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?
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.
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
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\ ??
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..
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.
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
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.
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)?
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
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.
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
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.
@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..
👍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.😀
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?
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?
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.
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
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?
Hi! Thanks for the video! Is there's any way to get the author and last person thay modify each file?
Hi can I perform the same function for multiple paths??
yeah.. this can be done.. Little modification in code would be required..
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)
This is what I want .... thank you very much sir......hats off to u
Thank you Pranav. Subscribe and share to see more.
Thanks for this is it possible to adapt this code to pull a list of subfolders only starting with 2020 for example?
@ 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..
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.
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
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?
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.
How Can i add browse Code instead of Paste destination Path In VBA code & Object for Browse code in this Same Code.
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
Hi. I get "variable not defined" when running "xlOp"?
Hello Jonas, please share your file(and code) with me..
It's "xlUp" not xlOp
how do you do the second line of your Excel, where you are able to insert the desired path?
Please Help
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?
Hi Kamal, Thanks for this video. But kindly walk us through how we can give file directory where we need to check ?
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"
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?
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)
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
Very good explanation, How can I get the image Dimensions (Length and Width)? Thanks
Would you be able to share the complete code or the template file for ready to use kind
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.
I got permission denied if i want to read the root drive letter, e.g. d:\ etc. How do resolve it ? thx
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
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?
While doing same with taking path from shared folder from network...it too slow...how to resolve this..please help
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 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?
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
Like your explanation - very well & simple
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")
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?
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
Can we get audio file duration using this
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\ ??
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..
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.
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.
how to get these folders in the same order as they are in the main rather than alphabetical order?
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.
Hi How we defined the path by user
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)?
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.
Great video! Would this work on a shared drive?
Not sure. You can give a try and let me know.. :-)
@@xtremeExcel It works :)
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. :-)
I need only xlsx file name, is that possible
Thanks for the video. However I could not get it to read from my external drive. :(
is it shared drive or external hard drive?
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?
Very helpful indeed. Works perfectly. Many thanks!
Is that possible to share this file to my mail
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
Wow! This works! THANK YOU! Your explanation to each line was superb. Liked and Subscribed!
Thank you! Stay Connected. :-)
Thx for sharing as I was just looking into to do the same in VBA.
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?
Downloadable file available?
Super nicely explained
thank you Vishakha.. stay connected for more such videos..!
how i can get the code
Showing error sub or function not defined
please share your file at my email id shared in the video/
Share a file which can be embedded in Excel to run described function
Hi, can please shown the full codes in module 1.
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.
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
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
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, thank you for sharing.
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
Thanks for sharing such a wonderful macro. It helps me a lot.
Great to hear!
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
Thank you so much I tried many codes but I couldn't able to enter the subfolders thanks a lot😃
Stay connected 😃
Thanks a lot this is very good session
Great...thanks..here u have a new subscriber👏🏻
Thanks for connecting James.. :-)
Thats Wonderful tutorial Sir.. Thank you
Thanks Vincent! Stay connected!
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.
Macro enabled Excel spreadsheet to create folder names list with size in mb
Thank you so much. Works great with my file.
Great to hear! :-)
Awesome video! Very helpful.
Super helpful... thanks!!!
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
Very Good!
Wow!! You are awesome!!
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
Very helpful video !!!
Thank you so match, Sir.
so nice of you.
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?
amazing
Thank you bro...
THANK YOU VERY MUCH.... :)
Stay connected! :-)
can you send me this file please
Thank you so much.
Thank you