Directory Listing Tool – Need to create “next worksheet” After line limit exceeded

Topic

New Reply

Greetings all!

I have been out of the forum for a little while, but I am back! I have inheirted a Directory listing Workbook. It does exactly what is needed, EXCEPT
it will not carry over the remaining listing IF the 65536 line limit is exceeded for any give page. I am in great need of overcoming this issue, as I have to get a listing of files that is 265,000+ . The way the dir structure is set, I cannot get all that I need.

Many Thanks,

Brad

Reply | Quote

Replies

Try Excel 2007.

Reply | Quote

Ummmmm Good answer, However that is not an option at my current worksite. Only have Excel 2003.

Anyone solve this and I shall toast one to them !

Reply | Quote

Try this

Note, you have many variables that are not DIMmed. I did not fix this in the code (I recommend that you do! [and Use “Option Explicit” at the start of the module to enforce it] nor play too much with your logic, I only added a way to change the sheets when too many files were listed. The lastrow is not hardcoded but tests the number of rows on the sheet so if someone does use XL2007 more rows will print on the list. I did not test it very extensively, I leave the debugging to you…

Steve

[codebox]Sub ListFiles2()
Dim LastStartPoint As String
Dim directories() As String, CurrentDirectory As String
Dim DirCounter As Integer, DirValue As String
Dim wks As Worksheet
Dim sWksName As String
Dim lRow As Long
Dim iWksCount As Integer
Dim lRowMax As Long

On Error GoTo 0
sWksName = Format(Now, “dd-mmm-yyyy hh-mm-ss AM/PM”)
lRow = 2
iWksCount = 0
lRowMax = ActiveSheet.Rows.Count

ShowHiddenAndSystemFiles = MsgBox(“Show HIDDEN and SYSTEM files?”, vbYesNoCancel, “Hidden & system files”)

‘ SelectedDir is a public variable set within the DisplayDirectoryDialogBox sub.
If ShowHiddenAndSystemFiles = vbCancel Then Exit Sub
StartPoint = SelectedDir

‘ Add a sheet to put the output on.
‘ It is labelled with the date and time so that it won’t clash with other sheet names
UserForm2.LB_Directory.Caption = ” Currently searching directory ” & SelectedDir
ReDim directories(2)

‘ Add a backslah to the directory starting point if it was not entered.

If Right(StartPoint, 1) = “” Then
directories(1) = StartPoint
Else
directories(1) = StartPoint & “”
End If

directories(2) = “”

‘ initialise Directory counter

DirCounter = 1
FileCount = 0
On Error Resume Next

‘ Now loop through the directories() array.
‘ For each entry test whether it’s a file or a directory.
‘ If it’s a file then add it to the filelist() array.
‘ If it’s a directory then add it to the directories() array.
‘ Keep going until there are no more entries in the directories array()!!

Do While directories(DirCounter) “”
CurrentDirectory = directories(DirCounter)

‘ use the DIR() function to get the first entry for the current directory

If ShowHiddenAndSystemFiles = vbYes Then
DirValue = Dir(CurrentDirectory, vbDirectory + vbHidden + vbSystem)
Else
DirValue = Dir(CurrentDirectory, vbDirectory)
End If

Do While DirValue “”

‘ write the file name sto the statusbar to show that something useful is happening

Application.StatusBar = CurrentDirectory & DirValue

If InStr(“..”, DirValue) = 0 Then

‘ Use the GetAttr() function to check whether the entry is a directory.
‘ it’s a directory entry so check to see if it’s “.” or “..”
‘ these are returned by the DIR() function but should be ignored

dirok = GetAttr(CurrentDirectory & DirValue) And vbDirectory
If dirok Then

‘ Add one more line to the Directories() array and
‘ paste the text into the array.

ReDim Preserve directories(UBound(directories) + 1)
directories(UBound(directories) – 1) = CurrentDirectory & DirValue & “”
Else

‘ must be a file so store the file name and it’s attributes
If lRow = 2 Then
Set wks = Sheets.Add(after:=Worksheets(1))
iWksCount = iWksCount + 1
wks.Name = “Files” & iWksCount & ” ” & sWksName
With Range(“A1”)
.FormulaR1C1 = “Directory”
.Offset(0, 1).Value = “File Name”
.Offset(0, 2).Value = “File Type”
.Offset(0, 3).Value = “File Size”
.Offset(0, 4).Value = “File Date & Time”
End With
Range(“A:A”).ColumnWidth = 40
Range(“B:C”).ColumnWidth = 25
Range(“e:E”).NumberFormat = “dd-mmm-yyyy hh:mm:ss AM/PM”
Range(“c:C”).ColumnWidth = 10
Range(“d1”).ColumnWidth = 15
Range(“e1”).ColumnWidth = 25
End If

FileCount = FileCount + 1
wks.Cells(lRow, 1) = CurrentDirectory
wks.Cells(lRow, 2) = DirValue
wks.Cells(lRow, 3) = Right(DirValue, 3)
wks.Cells(lRow, 4) = FileLen(CurrentDirectory & DirValue)
‘Format(Now, “dd-mmm-yyyy hh-mm-ss AM/PM”)
wks.Cells(lRow, 5) = Format(FileDateTime(CurrentDirectory & DirValue), “dd-mmm-yyyy hh:mm:ss AM/PM”)
lRow = lRow + 1
If lRow > lRowMax Then lRow = 2
SumDiskSpace = SumDiskSpace + wks.Cells(lRow, 4)
UserForm2.LB_FileNumber.Caption = ” Number of files found is ” & FileCount
UserForm2.LB_Space.Caption = ” Disk space currently used is ” & Int(SumDiskSpace / 100000) / 10 & ” MB”
Application.StatusBar = “Space so far is ” & SumDiskSpace
If Int(FileCount / 10) * 10 = FileCount Then
UserForm2.Image1.Visible = Not UserForm2.Image1.Visible
UserForm2.Image2.Visible = Not UserForm2.Image1.Visible
End If
DoEvents
End If
End If

‘ get the next value fron the DIR() function

DirValue = Dir()
Loop
DirCounter = DirCounter + 1

Loop

Application.StatusBar = False

End Sub[/codebox]

Reply | Quote

Steve,

Thanks! I will begin digesting this and reply with any success/issues.

Appreciate the help and tips,

BRad

Reply | Quote

Ok, well, this is working to a degree.

The first time I ran the code “AS IS”, I got the tool to report the amount of files I am getting.. IE: the dialog box racks up to the expected result, but the last box in the dialog does not report anything (no big deal). The multiple tabs get created and populated, except when it finishes, the final Tab is blank. In this first test, I had 197,925 files, and I only received 196,605 in a total of 3 tabs. It DID create the last tab, but DID NOT POPULATE.

I will keep working…. and hopefully someone else will too. This one may be too complex for me.

Brad

Reply | Quote

As mentioned I would start with ensuring that all the variables are correct and DIMmed using “Option Explicit”. I have seen many programs waylaid by typos in variable names…

Since it takes long to go through all those files, you might try to check the logic by using a smaller test directory (with maybe only 25 files) which you can see what may be missing, and change the max row to something like 10 (instead of reading the mac rows)

Steve

Reply | Quote

OK. I could not determine why it does not complete the listing on the final worksheet that gets created.

Steve,

Any Ideas?

Thanks,
Brad

Reply | Quote

Steve,

On Directories less than 65536 lines, I do get everything. I did put the Option Explicit, but the Macro then failed.

Like I said earlier, I ran your updated code against the huge directory, and got everything except the data that should have been on the last worksheet. So it seems to chunk through the data, create the next worksheet, populate with the max line count, then when it gets to fewer than 65536 the remaining data does not get written, but the last worksheet gets created. On the last worksheet, even the header line does not get written.

I wish I had written the code, bot I did not, and I really am not sure which variables are not DIMmed.

You did such a good job getting it to create the next worksheet and populate, I am hoping you can solve the problem of not writing the remaining data.

I would not think this remaining issue is a result of original variables not being DIMmed. (could be wrong on this point). I really wish I understood this stuff better, but I simply do not, and I am at a breakpoint where I HAVE to get these indexed so that I can Archive the stuff.

Anymore help is greatly appreciated.

Brad

Reply | Quote

If you add option explicit at the beginning, the code will tell you what variables are not defined.

I did suggest working with smaller datasets (and a smaller max) to try to understand where the code fails and what causes it to not complete.

I tested it on smaller datasets and the logic seems to work. It seems odd to me that it would create a sheet and not do any of the other things in that IF statement.

You might try adding a watchpoint to the line:
Set wks = Sheets.Add(after:=Worksheets(1))

And when the last sheet is created start stepping through the code…

I just noticed you have the line:
On Error Resume Next

In your code which will ignore all errors, so any problems with the code may not be detected. I don’t know what errors the code is expecting to ignore, but this blanket use of it, is probably the cause of the problem. You may want to comment out this line and then create an error handler to trap/ignore any particular errors that need to be done.

As I said, I did not check any logic or validity of any other aspects of the code, only allowing it to create new sheets.
Steve

Reply | Quote

Ok… I will go back at it and try those things you describe.

Will reply with the outcome.

Thanks,
Brad

Reply | Quote