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