• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

List Files In Folder And Subfolders

VijaySM

New Member
Hi Friends,
I am posting 2 questions here and have asked these in other forums as well but I could not get what I was looking for. So, I am hoping to get my answers here.

1. I want to list files in folder & subfolder in a network path with more than a million files. So, I want the code to insert a new sheet and continue listing in that sheet. I am using excel 2007.

This is a very often asked question "List files in folder & subfolders" and I have read many forums and the answer below and have tried to modify the code from other forums as well. But I am not able to modify the code fully to get my desired result. Below is my modified code, Here a new sheet is inserted but unable to continue in the next sheet.
http://chandoo.org/forum/threads/total-number-of-files-for-a-given-drive.6104

This is a modification from MrExcel forum. I am uploading the file from that post also.

http://www.mrexcel.com/forum/excel-...-way-listing-folders-subfolders-contents.html

Code:
Option Explicit

Sub ListFiles()
    Dim sPath      As String

    With Application.FileDialog(msoFileDialogFolderPicker)
        .Title = "Select directory"
        .InitialFileName = ThisWorkbook.Path & "\"
        .AllowMultiSelect = False
        If .Show = 0 Then Exit Sub
        sPath = .SelectedItems(1) & "\"
    End With

    NoCursing sPath, Range("A1")
End Sub

Sub NoCursing(ByVal sPath As String, rOut As Range)
    ' lists file name, size, and date for the files in and below sPath
    ' in columns A:C of rOut

    ' attribute mask
    Const iAttr    As Long = vbNormal + vbReadOnly + vbSystem + vbDirectory
    Dim jAttr      As Long        ' file attributes
    Dim col        As Collection  ' queued directories
    Dim iFile      As Long        ' file counter
    Dim sFile      As String      ' file name
    Dim sName      As String      ' full file name
    Dim fSec        As Single      ' seconds since midnight

    Dim maxRows As Long
    Dim sheetNumber As Integer
    maxRows = 1048576
    sheetNumber = 1
    iFile = 1

    With rOut.Range("A1:C1").Resize(rOut.Worksheet.Rows.Count - rOut.Row + 1)
        .ClearContents
        .Rows(1).Value = Split("File,Date,Size", ",")
    End With

    Application.ScreenUpdating = False
    fSec = Timer

    If Right(sPath, 1) <> "\" Then sPath = sPath & "\"
    Set col = New Collection
    col.Add sPath

    Do While col.Count
        sPath = col(1)
On Error Resume Next
        sFile = Dir(sPath, iAttr)

        Do While Len(sFile)
            sName = sPath & sFile

            On Error Resume Next
            jAttr = GetAttr(sName)

            If Err.Number Then
                ' You can't get attributes for files with Unicode characters in
                ' the name, or some particular files (e.g., "C:\System Volume Information")
                Debug.Print sName
                Err.Clear

            Else
                On Error GoTo 0
                If jAttr And vbDirectory Then
                    If Right(sName, 1) <> "." Then col.Add sName & "\"

                Else
                    iFile = iFile + 1
                    If (iFile And &H3FF) = 0 Then
                        Application.StatusBar = sMsg(iFile, Timer - fSec, col.Count)
                        DoEvents
                    End If
                   
                    If iFile = maxRows Then
                      sheetNumber = sheetNumber + 1
                      ActiveWorkbook.Sheets.Add After:=Worksheets(Worksheets.Count)
                      ActiveSheet.Name = "Sheet-" & sheetNumber
                    End If

                  iFile = iFile + 1
                   
                    rOut.Range("A1:C1").Offset(iFile).Value = Array(sName, _
                                                                    FileDateTime(sName), _
                                                                    FileLen(sName))
                End If
            End If
            sFile = Dir()
        Loop
        col.Remove 1
    Loop

    iFile = iFile + 1
    rOut.Offset(iFile).Value = sMsg(iFile - 1, Timer - fSec, col.Count)
    rOut.CurrentRegion.Sort Key1:=rOut.Range("A1"), Header:=xlYes
    Columns.AutoFit
    Application.StatusBar = False
    Application.ScreenUpdating = True
End Sub

Function sMsg(nFile As Long, fSec As Single, nCol As Long) As String
    sMsg = "  Files listed: " & Format(nFile, "#,##0") & _
          "  ET: " & Format(fSec / 86400, "h:mm:ss") & _
          "  Files/s: " & Format(nFile / fSec, "0") & _
          "  Directories queued: " & nCol
End Function

I am getting run time error 1004 - Application or object defined error in the code (Marked in bold), the iFile is showing 1048577 which is more than the last row. Here I am unable to make the list continue in the next sheet though a new sheet is inserted.
Please check and suggest changes as I am a newbie and not very good at VBA.

2. This question is also related to the above. I want to get the folder names and sizes as displayed in the explorer. I searched google and found some answers but they give folder size as bytes. My intention is to get sizes as shown in explorer. I can do the calculation but is it possible to use shell - Namespace as we do to get extended properties of files using GetDetailsOf() method for folders. If It is not possible how to make the calculation very near to as shown in explorer. (I can divide by 1024 and make as KB/MB but the values will not be as shown in explorer.

So, Thank You Chandoo and other excel enthusiasts. This is a very good website with lots of information and happy to be a member of this forum. Sorry Friends, If my post is very long as this is my first post here and I hope my question is clear to all.

Thank You
VijaySM
 

Attachments

  • MrE656026 - List Files.xlsm
    23.7 KB · Views: 45
Hi !

Better than using Excel (why ?),
just use OS command to generate a text file !

From command prompt, valid Dir /? and read help …

To generate a text file from Dir result :
Dir C:\sourcefoldername\ /s >C:\foldername\textfilename.txt
 
Thank you Marc for your reply.
I know this can be done in command prompt, powershell or vbscript (I got one solution to export to csv using this). I also know that I can export the result from text or csv using above methods, but since I want only a VBA solution (a better one or very similar to the above), I have posted here and other forums also. I have got the solution using DIR in excel VBA as in the following link.

http://www.ozgrid.com/forum/showthread.php?t=197743

I use excel but not VBA, I like to search for various VBA snippets and see how differently the same work is done. For this particular question we get many solution in web. Basically it has 2 types of solution like,
1. Using FileSystemObject
2. Using DIR.

I also found that DIR is slightly faster compared to FileSystemObject, But for recursive listing FileSystemObject is better. FileSystemObject becomes very slow if we have large number of files.

So, I am trying to modify both types of codes and want to check the errors and how to overcome that. eg- In Dir - If length of the filename is >255 characters it gives error (Some files from web can have long names but very rare).

Also there is no code snippet for more than million rows for listing. There are implementations to split a csv with more than million rows, but not the above.

I also tried "ASAP Utilities", which lists the files but in the end says that reporting for only upto the row limit. Some suggest to use database or other software as with large data excel will become bloated and slow.

But since I want only a VBA solution and also want to know if I have modified at the correct position. I tried to change the condition in different positions like before 1st loop etc. but not able to achieve the desired result. I now think that it is not as simple as it appears to me.

Thank you once again for your reply and Sorry if my post is very long but I have to explain why I am insisting only on excel VBA solution.

Thanks
 
Slowest is Excel for this kind of stuff !

Between the three common VBA ways, slowest is using FSO,
faster is to use Dir VBA function (faster even in a recursive way no difficult
to code) and fastest is via Windows API functions.
Many examples of all ways all over the web !

But to just create a list, my favorite is the fourth way like my previous post,
fast and no needs Excel ! Sometimes I even use that way within a VBA code.

For row limitation, for a csv file or a files list or whatever the data,
the solution is exactly the same ‼
When last row is reached, just change column or worksheet, so no difficulty !
 
Thanks once again Marc for your quick reply.
But I want to make clear that I have tried to change the worksheet as in the above modified code, it inserts a new sheet but since the file count is showing 1048577 it is giving an error. I tried to make it to 0, 1 etc. but unable to start from where it ended in previous sheet. In the immediate window where the list is generated it gets to the next file but to output it I am finding it difficult.
And regarding the "Windows API functions", I do not do coding but just do it as hobby but not very serious coder.

Another thing I asked was about folder size using "Windows API functions" as the codes I found everywhere gives in bytes but not as in explorer. I have got one book "Excel 2010 Power Programming with VBA" by John Walkenbach where he has shown to get file properties using the "GetDetailsOf" but for folder I am unable to do it.
I have got one solution in this forum itself as I have mentioned in my 1st post and have given the link. But I want to know If "GetDetailsOf" can be used and if there are other methods or workarounds to do it.

Thanks
 
Just read the well explained help within MSDN for GetDetailsOf

When you reach the maximum rows number, with burning
a couple of neurones easy logic is to just change column or worksheet
and don't forget to reset the row counter to write new data !
 
Thank you, Marc, but I have tried to reset the counter but it is showing some division by zero error in the Function for getting the time run to list the files. I have tried to include the counter in every position possible but not able to solve the problem. Any help would be much appreciated.

Thanks
 
Hi Friends,

Sorry if I am bothering you much, But I am unable to get through this problem myself. I ran the above for the full D-Drive and am attaching you the image of the error I get at the end of the rows but the cursor will be at cell A1 in a new sheet inserted. Please go through it and give some suggestions regarding this.

Thanks
 

Attachments

  • Error1.jpg
    Error1.jpg
    172.3 KB · Views: 6
  • Untitled2.jpg
    Untitled2.jpg
    49.4 KB · Views: 10

Just reset the row counter when max rows number is reached …

Copy / Paste is not coding …
 
Thanks Marc for your suggestion. I have been trying this for 2 weeks before posting here, but unable to get the desired result. It either gives error or inserts new sheet but displays only files after the row limit in the first sheet.:confused:

Thanks
 
OK : show your try !

As it's not your code neither mine, better should to ask where you grab it !
Maybe someone else here will have time to check
this gas-factory time procedure.
This kind of code is insane above 100,000 files, maybe over 10,000 …
VBA is maybe the slowest coding language and
FileSystemObject is the slowest way !
I remember a test under VBA to list all Excel files on a drive (~800) :
with FSO : 130 seconds. With VBA Dir inner function : less than 20 seconds !
 
Marc,

Thank you for all your suggestions and remarks. I am closing this discussion now. I will be posting about any other problems encountered by me as and when I require any answers or suggestions.

Thanks
 
Back
Top