Results 1 to 11 of 11
  1. #1
    4 Star Lounger
    Join Date
    May 2002
    Location
    US
    Posts
    439
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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
    Attached Files Attached Files

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    Try Excel 2007.

  3. #3
    4 Star Lounger
    Join Date
    May 2002
    Location
    US
    Posts
    439
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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 !

  4. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    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]

  5. #5
    4 Star Lounger
    Join Date
    May 2002
    Location
    US
    Posts
    439
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Steve,

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

    Appreciate the help and tips,


    BRad

  6. #6
    4 Star Lounger
    Join Date
    May 2002
    Location
    US
    Posts
    439
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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

  7. #7
    4 Star Lounger
    Join Date
    May 2002
    Location
    US
    Posts
    439
    Thanks
    0
    Thanked 0 Times in 0 Posts
    OK. I could not determine why it does not complete the listing on the final worksheet that gets created.

    Steve,

    Any Ideas?


    Thanks,
    Brad

  8. #8
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    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

  9. #9
    4 Star Lounger
    Join Date
    May 2002
    Location
    US
    Posts
    439
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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

  10. #10
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    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

  11. #11
    4 Star Lounger
    Join Date
    May 2002
    Location
    US
    Posts
    439
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Ok... I will go back at it and try those things you describe.



    Will reply with the outcome.


    Thanks,
    Brad

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •