Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
"Listing Contents of Sub-Folders"
Here's a snippit of code (attached to a userform) that I use to list files
contained in a folder. This code only picks up files contained in the folder selected and not in any sub-folders of that folder. What change(s) do I need to make so that the code identifies files contained in sub-floders also? If OptionButton1 Then EXT = "*.MP3" ElseIf OptionButton2 Then EXT = "*.CDG" ElseIf OptionButton3 Then EXT = "*.ZIP" ElseIf OptionButton4 Then EXT = "*.*" End If With Application.FileDialog(msoFileDialogFolderPicker) .Show ThePath = .SelectedItems(1) & "\" End With fname = Dir(ThePath & EXT) TIA...Don |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
"Listing Contents of Sub-Folders"
On Oct 28, 8:01 pm, Don wrote:
Here's a snippit of code (attached to a userform) that I use to list files contained in a folder. This code only picks up files contained in the folder selected and not in any sub-folders of that folder. What change(s) do I need to make so that the code identifies files contained in sub-floders also? If OptionButton1 Then EXT = "*.MP3" ElseIf OptionButton2 Then EXT = "*.CDG" ElseIf OptionButton3 Then EXT = "*.ZIP" ElseIf OptionButton4 Then EXT = "*.*" End If With Application.FileDialog(msoFileDialogFolderPicker) .Show ThePath = .SelectedItems(1) & "\" End With fname = Dir(ThePath & EXT) TIA...Don Hello Don, All system file viewers show only the contents of the directory (last folder in the path). None will identify specified file types in the sub-folders. If you had only 2 or 3 sub-folders, you could create another form with a ListBox for each sub-folder. If you are dealing with an unknown number of sub-files then creating ListBoxes for each sub-folder becomes problematic. Sincerely, Leith Ross |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
"Listing Contents of Sub-Folders"
Filesearch has a subfolders property, but be aware this has been removed in
Excel 2007. -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Don" wrote in message ... Here's a snippit of code (attached to a userform) that I use to list files contained in a folder. This code only picks up files contained in the folder selected and not in any sub-folders of that folder. What change(s) do I need to make so that the code identifies files contained in sub-floders also? If OptionButton1 Then EXT = "*.MP3" ElseIf OptionButton2 Then EXT = "*.CDG" ElseIf OptionButton3 Then EXT = "*.ZIP" ElseIf OptionButton4 Then EXT = "*.*" End If With Application.FileDialog(msoFileDialogFolderPicker) .Show ThePath = .SelectedItems(1) & "\" End With fname = Dir(ThePath & EXT) TIA...Don |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
"Listing Contents of Sub-Folders"
"Leith Ross" wrote: On Oct 28, 8:01 pm, Don wrote: Here's a snippit of code (attached to a userform) that I use to list files contained in a folder. This code only picks up files contained in the folder selected and not in any sub-folders of that folder. What change(s) do I need to make so that the code identifies files contained in sub-floders also? If OptionButton1 Then EXT = "*.MP3" ElseIf OptionButton2 Then EXT = "*.CDG" ElseIf OptionButton3 Then EXT = "*.ZIP" ElseIf OptionButton4 Then EXT = "*.*" End If With Application.FileDialog(msoFileDialogFolderPicker) .Show ThePath = .SelectedItems(1) & "\" End With fname = Dir(ThePath & EXT) TIA...Don Hello Don, All system file viewers show only the contents of the directory (last folder in the path). None will identify specified file types in the sub-folders. If you had only 2 or 3 sub-folders, you could create another form with a ListBox for each sub-folder. If you are dealing with an unknown number of sub-files then creating ListBoxes for each sub-folder becomes problematic. Sincerely, Leith Ross Thanks Leith for the quick reply, Was hoping there was a way but if not, so be it. I do have quite a number of sub-folders (several thousand within one main folder), each with only two or three files in them. Within each sub-folder, each file is titled exactly the same with only the extension being different. My goal is to be able to secure a list of all titles. Guess I'll have to rearrange the contents of my folders. Thanks again, Don |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
"Listing Contents of Sub-Folders"
Bob...I am using 2002 but don't seem to be able to get a handle on this. I
response to Leith explains my goal. Thanks for the reply, Don "Bob Phillips" wrote: Filesearch has a subfolders property, but be aware this has been removed in Excel 2007. -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Don" wrote in message ... Here's a snippit of code (attached to a userform) that I use to list files contained in a folder. This code only picks up files contained in the folder selected and not in any sub-folders of that folder. What change(s) do I need to make so that the code identifies files contained in sub-floders also? If OptionButton1 Then EXT = "*.MP3" ElseIf OptionButton2 Then EXT = "*.CDG" ElseIf OptionButton3 Then EXT = "*.ZIP" ElseIf OptionButton4 Then EXT = "*.*" End If With Application.FileDialog(msoFileDialogFolderPicker) .Show ThePath = .SelectedItems(1) & "\" End With fname = Dir(ThePath & EXT) TIA...Don |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
"Listing Contents of Sub-Folders"
Below I copied the example code from VBA help for subfolders. the code is
written for fileSearch which may not work on Excel 2007 but will work for excel 2003. there arre similar methods that can be used without fileSearch. You can use DIR instead of the FileSeaqrch methods. I have written similar code, but it is not as well documented as the code below. I put my code that gets every file and puts the filename and size on a worksheet after the code I found in the VBA help. VBA help code Sub SearchEveryFolder() 'Declare variables that reference a 'SearchScope and a ScopeFolder object. Dim ss As SearchScope Dim sf As ScopeFolder 'Declare a variable to act as a generic counter. Dim lngCount As Long 'Use a With...End With block to reference the 'FileSearch object. With Application.FileSearch 'Clear all the parameters of the previous searches. 'This method doesn't clear the LookIn property or 'the SearchFolders collection. .NewSearch 'Specify the type of file for which to search. 'Use the FileType property to specify the first type 'and then add additional types to the FileTypes collection. .FileType = msoFileTypeWebPages .FileTypes.Add msoFileTypeExcelWorkbooks 'Clear the SearchFolder collection by 'looping through each ScopeFolder object 'and removing it. For lngCount = 1 To .SearchFolders.Count .SearchFolders.Remove lngCount Next lngCount 'Loop through the SearchScopes collection to find 'the scope in which you want to search. In this 'case the scope is the local machine. For Each ss In .SearchScopes Select Case ss.Type Case msoSearchInMyComputer 'Loop through each ScopeFolder in 'the ScopeFolders collection of the 'SearchScope object. For Each sf In ss.ScopeFolder.ScopeFolders 'Call a function that loops through all 'of the subfolders of the root ScopeFolder. 'This function adds any folders named "1033" to the 'SearchFolders collection. Call OutputPaths(sf.ScopeFolders, "1033") Next sf Case Else End Select Next ss 'Test to see if any ScopeFolders collections were added to 'the SearchFolders collection. If .SearchFolders.Count 0 Then 'Set the LookIn property to the path of 'the first ScopeFolder object in the SearchFolders 'collection. This is here so that any previous 'setting of the LookIn property doesn't affect 'the search. .LookIn = .SearchFolders.Item(1).Path 'Execute the search and test to see if any files 'were found. If .Execute < 0 Then 'Display the number of files found. MsgBox "Files found: " & .FoundFiles.Count 'Loop through the list of found files and 'display the path of each one in a message box. For lngCount = 1 To .FoundFiles.Count If MsgBox(.FoundFiles.Item(lngCount), vbOKCancel, _ "Found files") = vbCancel Then 'Break out of the loop lngCount = .FoundFiles.Count End If Next lngCount End If End If End With End Sub 'This subroutine loops through all of the ScopeFolders collections 'in a given ScopeFolders collection. It adds any folder 'that has the same name as the value of strFolder 'to the SearchFolders collection. Sub OutputPaths(ByVal sfs As ScopeFolders, _ ByRef strFolder As String) 'Declare a variable as a ScopeFolder object Dim sf As ScopeFolder 'Loop through each ScopeFolder object in the 'ScopeFolders collection. For Each sf In sfs 'Test to see if the folder name of the ScopeFolder 'matches the value of strFolder. Use LCase to ensure 'that case does not affect the match. If LCase(sf.Name) = LCase(strFolder) Then 'Add the ScopeFolder to the SearchFolders collection. sf.AddToSearchFolders End If 'Include a DoEvents call because there is the potential for this 'loop to last a long time. The DoEvents call allows this process to 'continue handling events. DoEvents 'Test to see if the ScopeFolders collection in the 'current ScopeFolder is empty. If it isn't empty, then 'that means that the current ScopeFolder object contains subfolders. If sf.ScopeFolders.Count 0 Then 'This subroutine recursively calls itself so that 'it can add the subfolders of the current ScopeFolder object 'to the SearchFolders collection. Call OutputPaths(sf.ScopeFolders, strFolder) End If Next sf End Sub ---------------------------------------------------------------------- My code which lists every file found on a worksheet Dim RowNumber Sub GetFolderSize() strFolder = "C:" RowNumber = 1 Set fso = CreateObject _ ("Scripting.FileSystemObject") Set folder = _ fso.GetFolder(strFolder) Sheets(1).Cells(RowNumber, 1) = strFolder + "\" Sheets(1).Cells(RowNumber, 2) = folder.Size RowNumber = RowNumber + RowNumber Call GetSubFolderSize(strFolder + "\") End Sub Sub GetSubFolderSize(strFolder) Set fso = CreateObject _ ("Scripting.FileSystemObject") Set folder = _ fso.GetFolder(strFolder) If folder.subfolders.Count 0 Then For Each sf In folder.subfolders On Error GoTo 100 Call GetSubFolderSize(strFolder + sf.Name + "\") 100 Next sf End If 'folder size in bytes On Error GoTo 200 If Not folder.isrootfolder Then FolderSize = folder.Size Sheets(1).Cells(RowNumber, 2) = FolderSize Sheets(1).Cells(RowNumber, 1) = strFolder RowNumber = RowNumber + 1 End If 200 On Error GoTo 0 End Sub ----------------------------------------------------------------------- "Don" wrote: Bob...I am using 2002 but don't seem to be able to get a handle on this. I response to Leith explains my goal. Thanks for the reply, Don "Bob Phillips" wrote: Filesearch has a subfolders property, but be aware this has been removed in Excel 2007. -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Don" wrote in message ... Here's a snippit of code (attached to a userform) that I use to list files contained in a folder. This code only picks up files contained in the folder selected and not in any sub-folders of that folder. What change(s) do I need to make so that the code identifies files contained in sub-floders also? If OptionButton1 Then EXT = "*.MP3" ElseIf OptionButton2 Then EXT = "*.CDG" ElseIf OptionButton3 Then EXT = "*.ZIP" ElseIf OptionButton4 Then EXT = "*.*" End If With Application.FileDialog(msoFileDialogFolderPicker) .Show ThePath = .SelectedItems(1) & "\" End With fname = Dir(ThePath & EXT) TIA...Don |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
"Listing Contents of Sub-Folders"
Here is code written some time ago by Bill Manville which does a recursive
search. The path and file name are returned in the array aFiles(). Call the sub with the full path to the top level directory, with or without the ending backslash, e.g. C:MyDocuments\ExcelFiles or C:MyDocuments\ExcelFiles\ Option Base 1 Dim aFiles() As String, iFile As Integer Sub ListFilesInDirectory(Directory As String) Dim aDirs() As String, iDir As Integer, stFile As String ' use Dir function to find files and directories in Directory ' look for directories and build a separate array of them ' note that Dir returns files as well as directories 'when vbDirectory specified If Right$(Directory, 1) < Application.PathSeparator Then Directory = Directory & Application.PathSeparator End If iDir = 0 stFile = Directory & Dir(Directory & "*.*", vbDirectory) Do While stFile < Directory If Right(stFile, 2) = "\." Or Right(stFile, 3) = "\.." Then ' do nothing - GetAttr doesn't like these directories ElseIf (GetAttr(stFile) And vbDirectory) = vbDirectory Then ' add to local array of directories iDir = iDir + 1 ReDim Preserve aDirs(iDir) aDirs(iDir) = stFile Else ' add to global array of files iFile = iFile + 1 ReDim Preserve aFiles(iFile) aFiles(iFile) = stFile End If stFile = Directory & Dir() Loop ' now, for any directories in aDirs call self recursively If iDir 0 Then For iDir = 1 To UBound(aDirs) ListFilesInDirectory aDirs(iDir) & Application.PathSeparator Next iDir End If End Sub On Mon, 29 Oct 2007 04:04:02 -0700, Joel wrote: Below I copied the example code from VBA help for subfolders. the code is written for fileSearch which may not work on Excel 2007 but will work for excel 2003. there arre similar methods that can be used without fileSearch. You can use DIR instead of the FileSeaqrch methods. I have written similar code, but it is not as well documented as the code below. I put my code that gets every file and puts the filename and size on a worksheet after the code I found in the VBA help. VBA help code Sub SearchEveryFolder() 'Declare variables that reference a 'SearchScope and a ScopeFolder object. Dim ss As SearchScope Dim sf As ScopeFolder 'Declare a variable to act as a generic counter. Dim lngCount As Long 'Use a With...End With block to reference the 'FileSearch object. With Application.FileSearch 'Clear all the parameters of the previous searches. 'This method doesn't clear the LookIn property or 'the SearchFolders collection. .NewSearch 'Specify the type of file for which to search. 'Use the FileType property to specify the first type 'and then add additional types to the FileTypes collection. .FileType = msoFileTypeWebPages .FileTypes.Add msoFileTypeExcelWorkbooks 'Clear the SearchFolder collection by 'looping through each ScopeFolder object 'and removing it. For lngCount = 1 To .SearchFolders.Count .SearchFolders.Remove lngCount Next lngCount 'Loop through the SearchScopes collection to find 'the scope in which you want to search. In this 'case the scope is the local machine. For Each ss In .SearchScopes Select Case ss.Type Case msoSearchInMyComputer 'Loop through each ScopeFolder in 'the ScopeFolders collection of the 'SearchScope object. For Each sf In ss.ScopeFolder.ScopeFolders 'Call a function that loops through all 'of the subfolders of the root ScopeFolder. 'This function adds any folders named "1033" to the 'SearchFolders collection. Call OutputPaths(sf.ScopeFolders, "1033") Next sf Case Else End Select Next ss 'Test to see if any ScopeFolders collections were added to 'the SearchFolders collection. If .SearchFolders.Count 0 Then 'Set the LookIn property to the path of 'the first ScopeFolder object in the SearchFolders 'collection. This is here so that any previous 'setting of the LookIn property doesn't affect 'the search. .LookIn = .SearchFolders.Item(1).Path 'Execute the search and test to see if any files 'were found. If .Execute < 0 Then 'Display the number of files found. MsgBox "Files found: " & .FoundFiles.Count 'Loop through the list of found files and 'display the path of each one in a message box. For lngCount = 1 To .FoundFiles.Count If MsgBox(.FoundFiles.Item(lngCount), vbOKCancel, _ "Found files") = vbCancel Then 'Break out of the loop lngCount = .FoundFiles.Count End If Next lngCount End If End If End With End Sub 'This subroutine loops through all of the ScopeFolders collections 'in a given ScopeFolders collection. It adds any folder 'that has the same name as the value of strFolder 'to the SearchFolders collection. Sub OutputPaths(ByVal sfs As ScopeFolders, _ ByRef strFolder As String) 'Declare a variable as a ScopeFolder object Dim sf As ScopeFolder 'Loop through each ScopeFolder object in the 'ScopeFolders collection. For Each sf In sfs 'Test to see if the folder name of the ScopeFolder 'matches the value of strFolder. Use LCase to ensure 'that case does not affect the match. If LCase(sf.Name) = LCase(strFolder) Then 'Add the ScopeFolder to the SearchFolders collection. sf.AddToSearchFolders End If 'Include a DoEvents call because there is the potential for this 'loop to last a long time. The DoEvents call allows this process to 'continue handling events. DoEvents 'Test to see if the ScopeFolders collection in the 'current ScopeFolder is empty. If it isn't empty, then 'that means that the current ScopeFolder object contains subfolders. If sf.ScopeFolders.Count 0 Then 'This subroutine recursively calls itself so that 'it can add the subfolders of the current ScopeFolder object 'to the SearchFolders collection. Call OutputPaths(sf.ScopeFolders, strFolder) End If Next sf End Sub ---------------------------------------------------------------------- My code which lists every file found on a worksheet Dim RowNumber Sub GetFolderSize() strFolder = "C:" RowNumber = 1 Set fso = CreateObject _ ("Scripting.FileSystemObject") Set folder = _ fso.GetFolder(strFolder) Sheets(1).Cells(RowNumber, 1) = strFolder + "\" Sheets(1).Cells(RowNumber, 2) = folder.Size RowNumber = RowNumber + RowNumber Call GetSubFolderSize(strFolder + "\") End Sub Sub GetSubFolderSize(strFolder) Set fso = CreateObject _ ("Scripting.FileSystemObject") Set folder = _ fso.GetFolder(strFolder) If folder.subfolders.Count 0 Then For Each sf In folder.subfolders On Error GoTo 100 Call GetSubFolderSize(strFolder + sf.Name + "\") 100 Next sf End If 'folder size in bytes On Error GoTo 200 If Not folder.isrootfolder Then FolderSize = folder.Size Sheets(1).Cells(RowNumber, 2) = FolderSize Sheets(1).Cells(RowNumber, 1) = strFolder RowNumber = RowNumber + 1 End If 200 On Error GoTo 0 End Sub ----------------------------------------------------------------------- "Don" wrote: Bob...I am using 2002 but don't seem to be able to get a handle on this. I response to Leith explains my goal. Thanks for the reply, Don "Bob Phillips" wrote: Filesearch has a subfolders property, but be aware this has been removed in Excel 2007. -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Don" wrote in message ... Here's a snippit of code (attached to a userform) that I use to list files contained in a folder. This code only picks up files contained in the folder selected and not in any sub-folders of that folder. What change(s) do I need to make so that the code identifies files contained in sub-floders also? If OptionButton1 Then EXT = "*.MP3" ElseIf OptionButton2 Then EXT = "*.CDG" ElseIf OptionButton3 Then EXT = "*.ZIP" ElseIf OptionButton4 Then EXT = "*.*" End If With Application.FileDialog(msoFileDialogFolderPicker) .Show ThePath = .SelectedItems(1) & "\" End With fname = Dir(ThePath & EXT) TIA...Don |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
"Listing Contents of Sub-Folders"
ty Myrna,
I'll try and make it work, but not sure my Xcel knowledge is adequate at this time...but it's fun trying...thanks again, Don "Myrna Larson" wrote: Here is code written some time ago by Bill Manville which does a recursive search. The path and file name are returned in the array aFiles(). Call the sub with the full path to the top level directory, with or without the ending backslash, e.g. C:MyDocuments\ExcelFiles or C:MyDocuments\ExcelFiles\ Option Base 1 Dim aFiles() As String, iFile As Integer Sub ListFilesInDirectory(Directory As String) Dim aDirs() As String, iDir As Integer, stFile As String ' use Dir function to find files and directories in Directory ' look for directories and build a separate array of them ' note that Dir returns files as well as directories 'when vbDirectory specified If Right$(Directory, 1) < Application.PathSeparator Then Directory = Directory & Application.PathSeparator End If iDir = 0 stFile = Directory & Dir(Directory & "*.*", vbDirectory) Do While stFile < Directory If Right(stFile, 2) = "\." Or Right(stFile, 3) = "\.." Then ' do nothing - GetAttr doesn't like these directories ElseIf (GetAttr(stFile) And vbDirectory) = vbDirectory Then ' add to local array of directories iDir = iDir + 1 ReDim Preserve aDirs(iDir) aDirs(iDir) = stFile Else ' add to global array of files iFile = iFile + 1 ReDim Preserve aFiles(iFile) aFiles(iFile) = stFile End If stFile = Directory & Dir() Loop ' now, for any directories in aDirs call self recursively If iDir 0 Then For iDir = 1 To UBound(aDirs) ListFilesInDirectory aDirs(iDir) & Application.PathSeparator Next iDir End If End Sub On Mon, 29 Oct 2007 04:04:02 -0700, Joel wrote: Below I copied the example code from VBA help for subfolders. the code is written for fileSearch which may not work on Excel 2007 but will work for excel 2003. there arre similar methods that can be used without fileSearch. You can use DIR instead of the FileSeaqrch methods. I have written similar code, but it is not as well documented as the code below. I put my code that gets every file and puts the filename and size on a worksheet after the code I found in the VBA help. VBA help code Sub SearchEveryFolder() 'Declare variables that reference a 'SearchScope and a ScopeFolder object. Dim ss As SearchScope Dim sf As ScopeFolder 'Declare a variable to act as a generic counter. Dim lngCount As Long 'Use a With...End With block to reference the 'FileSearch object. With Application.FileSearch 'Clear all the parameters of the previous searches. 'This method doesn't clear the LookIn property or 'the SearchFolders collection. .NewSearch 'Specify the type of file for which to search. 'Use the FileType property to specify the first type 'and then add additional types to the FileTypes collection. .FileType = msoFileTypeWebPages .FileTypes.Add msoFileTypeExcelWorkbooks 'Clear the SearchFolder collection by 'looping through each ScopeFolder object 'and removing it. For lngCount = 1 To .SearchFolders.Count .SearchFolders.Remove lngCount Next lngCount 'Loop through the SearchScopes collection to find 'the scope in which you want to search. In this 'case the scope is the local machine. For Each ss In .SearchScopes Select Case ss.Type Case msoSearchInMyComputer 'Loop through each ScopeFolder in 'the ScopeFolders collection of the 'SearchScope object. For Each sf In ss.ScopeFolder.ScopeFolders 'Call a function that loops through all 'of the subfolders of the root ScopeFolder. 'This function adds any folders named "1033" to the 'SearchFolders collection. Call OutputPaths(sf.ScopeFolders, "1033") Next sf Case Else End Select Next ss 'Test to see if any ScopeFolders collections were added to 'the SearchFolders collection. If .SearchFolders.Count 0 Then 'Set the LookIn property to the path of 'the first ScopeFolder object in the SearchFolders 'collection. This is here so that any previous 'setting of the LookIn property doesn't affect 'the search. .LookIn = .SearchFolders.Item(1).Path 'Execute the search and test to see if any files 'were found. If .Execute < 0 Then 'Display the number of files found. MsgBox "Files found: " & .FoundFiles.Count 'Loop through the list of found files and 'display the path of each one in a message box. For lngCount = 1 To .FoundFiles.Count If MsgBox(.FoundFiles.Item(lngCount), vbOKCancel, _ "Found files") = vbCancel Then 'Break out of the loop lngCount = .FoundFiles.Count End If Next lngCount End If End If End With End Sub 'This subroutine loops through all of the ScopeFolders collections 'in a given ScopeFolders collection. It adds any folder 'that has the same name as the value of strFolder 'to the SearchFolders collection. Sub OutputPaths(ByVal sfs As ScopeFolders, _ ByRef strFolder As String) 'Declare a variable as a ScopeFolder object Dim sf As ScopeFolder 'Loop through each ScopeFolder object in the 'ScopeFolders collection. For Each sf In sfs 'Test to see if the folder name of the ScopeFolder 'matches the value of strFolder. Use LCase to ensure 'that case does not affect the match. If LCase(sf.Name) = LCase(strFolder) Then 'Add the ScopeFolder to the SearchFolders collection. sf.AddToSearchFolders End If 'Include a DoEvents call because there is the potential for this 'loop to last a long time. The DoEvents call allows this process to 'continue handling events. DoEvents 'Test to see if the ScopeFolders collection in the 'current ScopeFolder is empty. If it isn't empty, then 'that means that the current ScopeFolder object contains subfolders. If sf.ScopeFolders.Count 0 Then 'This subroutine recursively calls itself so that 'it can add the subfolders of the current ScopeFolder object 'to the SearchFolders collection. Call OutputPaths(sf.ScopeFolders, strFolder) End If Next sf End Sub ---------------------------------------------------------------------- My code which lists every file found on a worksheet Dim RowNumber Sub GetFolderSize() strFolder = "C:" RowNumber = 1 Set fso = CreateObject _ ("Scripting.FileSystemObject") Set folder = _ fso.GetFolder(strFolder) Sheets(1).Cells(RowNumber, 1) = strFolder + "\" Sheets(1).Cells(RowNumber, 2) = folder.Size RowNumber = RowNumber + RowNumber Call GetSubFolderSize(strFolder + "\") End Sub Sub GetSubFolderSize(strFolder) Set fso = CreateObject _ ("Scripting.FileSystemObject") Set folder = _ fso.GetFolder(strFolder) If folder.subfolders.Count 0 Then For Each sf In folder.subfolders On Error GoTo 100 Call GetSubFolderSize(strFolder + sf.Name + "\") 100 Next sf End If 'folder size in bytes On Error GoTo 200 If Not folder.isrootfolder Then FolderSize = folder.Size Sheets(1).Cells(RowNumber, 2) = FolderSize Sheets(1).Cells(RowNumber, 1) = strFolder RowNumber = RowNumber + 1 End If 200 On Error GoTo 0 End Sub ----------------------------------------------------------------------- "Don" wrote: Bob...I am using 2002 but don't seem to be able to get a handle on this. I response to Leith explains my goal. Thanks for the reply, Don "Bob Phillips" wrote: Filesearch has a subfolders property, but be aware this has been removed in Excel 2007. -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Don" wrote in message ... Here's a snippit of code (attached to a userform) that I use to list files contained in a folder. This code only picks up files contained in the folder selected and not in any sub-folders of that folder. What change(s) do I need to make so that the code identifies files contained in sub-floders also? If OptionButton1 Then EXT = "*.MP3" ElseIf OptionButton2 Then EXT = "*.CDG" ElseIf OptionButton3 Then EXT = "*.ZIP" ElseIf OptionButton4 Then EXT = "*.*" End If With Application.FileDialog(msoFileDialogFolderPicker) .Show ThePath = .SelectedItems(1) & "\" |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
"Listing Contents of Sub-Folders"
Hi All,
I'm attempting to learn VBA. So, I copied this procedure into a sheet module. Using debug.print, I saw the procedure worked. However, I don't understand how to get the information into a spread sheet or to print out. Thanks for your time and insight. Dan |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
"Listing Contents of Sub-Folders"
If you are talking about the code I posted, this example writes the list to
the worksheet at the activecell. Sub FileListToWorkSheet() Dim DirName As String DirName = "C:\Docs\Excel" ListFilesInDirectory DirName ActiveCell.Resize(UBound(aFiles), 1).Value = Application.Transpose(aFiles) End Sub On Wed, 31 Oct 2007 00:16:30 -0000, dan dungan wrote: Hi All, I'm attempting to learn VBA. So, I copied this procedure into a sheet module. Using debug.print, I saw the procedure worked. However, I don't understand how to get the information into a spread sheet or to print out. Thanks for your time and insight. Dan |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
"Listing Contents of Sub-Folders"
PS: Recursion isn't the ideal place to start learning a programming language
<g. On Tue, 30 Oct 2007 15:31:02 -0700, Don wrote: ty Myrna, I'll try and make it work, but not sure my Xcel knowledge is adequate at this time...but it's fun trying...thanks again, Don "Myrna Larson" wrote: Here is code written some time ago by Bill Manville which does a recursive search. The path and file name are returned in the array aFiles(). Call the sub with the full path to the top level directory, with or without the ending backslash, e.g. C:MyDocuments\ExcelFiles or C:MyDocuments\ExcelFiles\ Option Base 1 Dim aFiles() As String, iFile As Integer Sub ListFilesInDirectory(Directory As String) Dim aDirs() As String, iDir As Integer, stFile As String ' use Dir function to find files and directories in Directory ' look for directories and build a separate array of them ' note that Dir returns files as well as directories 'when vbDirectory specified If Right$(Directory, 1) < Application.PathSeparator Then Directory = Directory & Application.PathSeparator End If iDir = 0 stFile = Directory & Dir(Directory & "*.*", vbDirectory) Do While stFile < Directory If Right(stFile, 2) = "\." Or Right(stFile, 3) = "\.." Then ' do nothing - GetAttr doesn't like these directories ElseIf (GetAttr(stFile) And vbDirectory) = vbDirectory Then ' add to local array of directories iDir = iDir + 1 ReDim Preserve aDirs(iDir) aDirs(iDir) = stFile Else ' add to global array of files iFile = iFile + 1 ReDim Preserve aFiles(iFile) aFiles(iFile) = stFile End If stFile = Directory & Dir() Loop ' now, for any directories in aDirs call self recursively If iDir 0 Then For iDir = 1 To UBound(aDirs) ListFilesInDirectory aDirs(iDir) & Application.PathSeparator Next iDir End If End Sub On Mon, 29 Oct 2007 04:04:02 -0700, Joel wrote: Below I copied the example code from VBA help for subfolders. the code is written for fileSearch which may not work on Excel 2007 but will work for excel 2003. there arre similar methods that can be used without fileSearch. You can use DIR instead of the FileSeaqrch methods. I have written similar code, but it is not as well documented as the code below. I put my code that gets every file and puts the filename and size on a worksheet after the code I found in the VBA help. VBA help code Sub SearchEveryFolder() 'Declare variables that reference a 'SearchScope and a ScopeFolder object. Dim ss As SearchScope Dim sf As ScopeFolder 'Declare a variable to act as a generic counter. Dim lngCount As Long 'Use a With...End With block to reference the 'FileSearch object. With Application.FileSearch 'Clear all the parameters of the previous searches. 'This method doesn't clear the LookIn property or 'the SearchFolders collection. .NewSearch 'Specify the type of file for which to search. 'Use the FileType property to specify the first type 'and then add additional types to the FileTypes collection. .FileType = msoFileTypeWebPages .FileTypes.Add msoFileTypeExcelWorkbooks 'Clear the SearchFolder collection by 'looping through each ScopeFolder object 'and removing it. For lngCount = 1 To .SearchFolders.Count .SearchFolders.Remove lngCount Next lngCount 'Loop through the SearchScopes collection to find 'the scope in which you want to search. In this 'case the scope is the local machine. For Each ss In .SearchScopes Select Case ss.Type Case msoSearchInMyComputer 'Loop through each ScopeFolder in 'the ScopeFolders collection of the 'SearchScope object. For Each sf In ss.ScopeFolder.ScopeFolders 'Call a function that loops through all 'of the subfolders of the root ScopeFolder. 'This function adds any folders named "1033" to the 'SearchFolders collection. Call OutputPaths(sf.ScopeFolders, "1033") Next sf Case Else End Select Next ss 'Test to see if any ScopeFolders collections were added to 'the SearchFolders collection. If .SearchFolders.Count 0 Then 'Set the LookIn property to the path of 'the first ScopeFolder object in the SearchFolders 'collection. This is here so that any previous 'setting of the LookIn property doesn't affect 'the search. .LookIn = .SearchFolders.Item(1).Path 'Execute the search and test to see if any files 'were found. If .Execute < 0 Then 'Display the number of files found. MsgBox "Files found: " & .FoundFiles.Count 'Loop through the list of found files and 'display the path of each one in a message box. For lngCount = 1 To .FoundFiles.Count If MsgBox(.FoundFiles.Item(lngCount), vbOKCancel, _ "Found files") = vbCancel Then 'Break out of the loop lngCount = .FoundFiles.Count End If Next lngCount End If End If End With End Sub 'This subroutine loops through all of the ScopeFolders collections 'in a given ScopeFolders collection. It adds any folder 'that has the same name as the value of strFolder 'to the SearchFolders collection. Sub OutputPaths(ByVal sfs As ScopeFolders, _ ByRef strFolder As String) 'Declare a variable as a ScopeFolder object Dim sf As ScopeFolder 'Loop through each ScopeFolder object in the 'ScopeFolders collection. For Each sf In sfs 'Test to see if the folder name of the ScopeFolder 'matches the value of strFolder. Use LCase to ensure 'that case does not affect the match. If LCase(sf.Name) = LCase(strFolder) Then 'Add the ScopeFolder to the SearchFolders collection. sf.AddToSearchFolders End If 'Include a DoEvents call because there is the potential for this 'loop to last a long time. The DoEvents call allows this process to 'continue handling events. DoEvents 'Test to see if the ScopeFolders collection in the 'current ScopeFolder is empty. If it isn't empty, then 'that means that the current ScopeFolder object contains subfolders. If sf.ScopeFolders.Count 0 Then 'This subroutine recursively calls itself so that 'it can add the subfolders of the current ScopeFolder object 'to the SearchFolders collection. Call OutputPaths(sf.ScopeFolders, strFolder) End If Next sf End Sub ---------------------------------------------------------------------- My code which lists every file found on a worksheet Dim RowNumber Sub GetFolderSize() strFolder = "C:" RowNumber = 1 Set fso = CreateObject _ ("Scripting.FileSystemObject") Set folder = _ fso.GetFolder(strFolder) Sheets(1).Cells(RowNumber, 1) = strFolder + "\" Sheets(1).Cells(RowNumber, 2) = folder.Size RowNumber = RowNumber + RowNumber Call GetSubFolderSize(strFolder + "\") End Sub Sub GetSubFolderSize(strFolder) Set fso = CreateObject _ ("Scripting.FileSystemObject") Set folder = _ fso.GetFolder(strFolder) If folder.subfolders.Count 0 Then For Each sf In folder.subfolders On Error GoTo 100 Call GetSubFolderSize(strFolder + sf.Name + "\") 100 Next sf End If 'folder size in bytes On Error GoTo 200 If Not folder.isrootfolder Then FolderSize = folder.Size Sheets(1).Cells(RowNumber, 2) = FolderSize Sheets(1).Cells(RowNumber, 1) = strFolder RowNumber = RowNumber + 1 End If 200 On Error GoTo 0 End Sub ----------------------------------------------------------------------- "Don" wrote: Bob...I am using 2002 but don't seem to be able to get a handle on this. I response to Leith explains my goal. Thanks for the reply, Don "Bob Phillips" wrote: Filesearch has a subfolders property, but be aware this has been removed in Excel 2007. -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Don" wrote in message ... Here's a snippit of code (attached to a userform) that I use to list files contained in a folder. This code only picks up files contained in the folder selected and not in any sub-folders of that folder. What change(s) do I need to make so that the code identifies files contained in sub-floders also? If OptionButton1 Then EXT = "*.MP3" ElseIf OptionButton2 Then EXT = "*.CDG" ElseIf OptionButton3 Then EXT = "*.ZIP" ElseIf OptionButton4 Then EXT = "*.*" End If With Application.FileDialog(msoFileDialogFolderPicker) .Show ThePath = .SelectedItems(1) & "\" |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
"Listing Contents of Sub-Folders"
Hi Myrna,
Well, one has to begin somewhere! I do have a couple questions, though. I hope it's appropriate to ask them here. If not, please let me know the best place for these questions. I had never heard of Option Base 1, and I didn't know there are several option statements. I understand from this group that using Option Explicit is a good practice. 1. Can a procedure have more than one Option statement? 2. Does Option Base 1 force explicit declaration? Concerning the line, If Right$(Directory, 1) < Application.PathSeparator Then 1. What is the purpose of the $? Thanks, Dan On Oct 30, 5:50 pm, Myrna Larson wrote: PS: Recursion isn't the ideal place to start learning a programming language <g. |
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
"Listing Contents of Sub-Folders"
"dan dungan" wrote in message oups.com... I had never heard of Option Base 1, and I didn't know there are several option statements. I understand from this group that using Option Explicit is a good practice. 1. Can a procedure have more than one Option statement? Yes, as long it is different options 2. Does Option Base 1 force explicit declaration? No, it sets the number base for arrays and the like to 1 (against 0) Concerning the line, If Right$(Directory, 1) < Application.PathSeparator Then 1. What is the purpose of the $? Just explicitly saying use the string version of the function. |
#14
Posted to microsoft.public.excel.programming
|
|||
|
|||
"Listing Contents of Sub-Folders"
Thanks Bob.
|
#15
Posted to microsoft.public.excel.programming
|
|||
|
|||
"Listing Contents of Sub-Folders"
Comments in-line
I had never heard of Option Base 1, and I didn't know there are several option statements. I understand from this group that using Option Explicit is a good practice. 1. Can a procedure have more than one Option statement? Put your cursor on the word Option, then press F1 to get help. Yes, there are several Option statements: Option Explicit, Option Base 1, Option Compare Text/Binary, etc 2. Does Option Base 1 force explicit declaration? No, that's Option Explicit Concerning the line, If Right$(Directory, 1) < Application.PathSeparator Then 1. What is the purpose of the $? The statement creates a temporary variable. Using the $ at the end means that Right function should create a STRING variable rather than a variant. It's better to avoid variants when you can, as it takes extra time to process them -- the basic engine has to look up the variable, determine its type, then possibly convert it to what is needed, in this case a string. |
#16
Posted to microsoft.public.excel.programming
|
|||
|
|||
"Listing Contents of Sub-Folders"
1. Can a procedure have more than one Option statement?
There are four different Option statements: Option Explicit Option Compare Text Option Base 0 Option Private Module You can have 1 or 0 of each of these, but no duplicates. 2. Does Option Base 1 force explicit declaration? Option Base 1 sets the default lower bound of arrays. With Option Base 1, the following array declarations are the same. Dim Arr(5) Dim Arr(1 To 5) ' five elements in the array With Option Base 0, the following array declarations are the same. Dim Arr(5) Dim Arr(0 To 5) ' six elements in the array Personally, I think it is bad programming practice to omit the lower bound in an array declaration. If you move code from one module to another, the logic can become faulty if the modules have different Option Base statements. Option Explicit means that variables must be declared with a Dim statement. Option Compare Text specifies that text comparisons ignore upper/lower case. E.g., "ABC" is equal to "abc". Option Compare Binary enforces case, so "ABC" is NOT the same as "abc". Option Private Module means that the procedures in the module cannot be called by other projects that may reference the project containing the module. I think this is misleading nomenclature. It would be much more intuitive if the statement were "Option Private Project" because the procedures are private to the project but may be called by any module in the project. 1. What is the purpose of the $? This forces the result as a String instead of a Variant. It is more efficient, meaning it is faster. -- Cordially, Chip Pearson Microsoft MVP - Excel, 10 Years Pearson Software Consulting www.cpearson.com (email on the web site) "dan dungan" wrote in message oups.com... Hi Myrna, Well, one has to begin somewhere! I do have a couple questions, though. I hope it's appropriate to ask them here. If not, please let me know the best place for these questions. I had never heard of Option Base 1, and I didn't know there are several option statements. I understand from this group that using Option Explicit is a good practice. 1. Can a procedure have more than one Option statement? 2. Does Option Base 1 force explicit declaration? Concerning the line, If Right$(Directory, 1) < Application.PathSeparator Then 1. What is the purpose of the $? Thanks, Dan On Oct 30, 5:50 pm, Myrna Larson wrote: PS: Recursion isn't the ideal place to start learning a programming language <g. |
#17
Posted to microsoft.public.excel.programming
|
|||
|
|||
"Listing Contents of Sub-Folders"
Thanks for your responses, Myrna. Concerning finding an ideal place to
start learning a programming language--it's even worse than you suspect. I don't know how to program in any language! I have taken qbasic introduction to programming and vba for Access. I'm working on a project I'm being paid to complete. Here's my learning plan: 1. When I need to make an improvement, if I don't know what to do, I'll search my John Walkenbach books or the newsgroups. 2. When I can't figure out what to do; like now, I start reading other people's questions. I try to help them if I can. If not I try to understand the answers their question generated. I'm not able to figure out how to ask my question, so, I've been trying to find other well-formed questions as models. 3. That's how I ended up looking at recursion. Anyway, thanks again for your response. Dan |
#18
Posted to microsoft.public.excel.programming
|
|||
|
|||
"Listing Contents of Sub-Folders"
Hi Chip,
Option Base 1 sets the default lower bound of arrays. With Option Base 1, the following array declarations are the same. Dim Arr(5) Dim Arr(1 To 5) ' five elements in the array With Option Base 0, the following array declarations are the same. Dim Arr(5) Dim Arr(0 To 5) ' six elements in the array Personally, I think it is bad programming practice to omit the lower bound in an array declaration. In either of your examples, it seems to me that you have not omitted the lower bound in the array declaration, have you? So, I don't see the usefulness of the Option Base 1 or Option Base 0. Thanks for your comments and help. Dan |
#19
Posted to microsoft.public.excel.programming
|
|||
|
|||
"Listing Contents of Sub-Folders"
Have you looked at the Array statement? It's affected by the Option Base
setting. e.g. Dim v as variant v = Array(1,2,3,4,5) the array bounds are 0 to 4 with no Option Base statement, or with Option Base 0 (the default). With Option Base 1 at the top of the module, the array bounds are 1 to 5. Dim Arr(5) specifies the upper bound and omits the lower bound. On Thu, 01 Nov 2007 00:22:43 -0000, dan dungan wrote: Hi Chip, Option Base 1 sets the default lower bound of arrays. With Option Base 1, the following array declarations are the same. Dim Arr(5) Dim Arr(1 To 5) ' five elements in the array With Option Base 0, the following array declarations are the same. Dim Arr(5) Dim Arr(0 To 5) ' six elements in the array Personally, I think it is bad programming practice to omit the lower bound in an array declaration. In either of your examples, it seems to me that you have not omitted the lower bound in the array declaration, have you? So, I don't see the usefulness of the Option Base 1 or Option Base 0. Thanks for your comments and help. Dan |
#20
Posted to microsoft.public.excel.programming
|
|||
|
|||
"Listing Contents of Sub-Folders"
In either of your examples, it seems to me that you have not omitted
the lower bound in the array declaration, have you? Each pair of array declarations was supposed to illustrate the difference between omitting and including the lower bound of the array declaration. The first Dim statement in each pair omitted the lower bound and the second Dim statement in each pair showed how the array would need to be declared to match a Option Base statement. For example, the in pair of declarations Dim Arr(5) Dim Arr(1 To 5) ' five elements in the array the first illustrates not using a lower bound and the second shows how VBA actually allocates the array if Option Base 1 is in effect. The second pair of declarations, Dim Arr(5) Dim Arr(0 To 5) ' six elements in the array illustrates again an array without a specified lower bound and how VBA will allocated the array if Option Base is 0. The point I was trying to make was that the declaration Dim Arr(5) will allocate two differently sized arrays depending on the Option Base statement,. Dim Arr(5) declares an array without a specified lower bound. If you have Option Base 1, this array declaration is the same as if you used Dim Arr(1 To 5). This array has five elements. If you have Option Base 0 or no Option Base statement at all, that same array declaration, Dim Arr(5), is the same as the declaration Dim Arr(0 To 5). This array has six elements. A potential problem arises when you copy a procedure from one module to another. If the code is copied from a module using Option Base 1 to a module using Option Base 0 or no Option Base statement at all, that array declaration, which originally had 5 element now declares an array with 6 elements. The presence of the extra element may cause the logic of the procedure to behave in an unintended way. As I wrote before, I think it is very bad programming practice to omit the lower bound. In my opinion, all arrays that have bounds specified in the Dim statement should have both the lower and upper bound specified. I would never use Dim Arr(5). Instead, I would always use Dim Arr(0 to 5) or Dim Arr(1 To 5). I never use Option Base at all. It is worth noting that not all arrays adhere the the Option Base statement. For example, the array created by the Split function always has a lower bound of 0, regardless of the Option Base statement. So, I don't see the usefulness of the Option Base 1 or Option Base 0. Not only is it not useful in any meaningful way, it is an invitation to bugs if you are a proponent of code reuse. -- Cordially, Chip Pearson Microsoft MVP - Excel, 10 Years Pearson Software Consulting www.cpearson.com (email on the web site) "dan dungan" wrote in message oups.com... Hi Chip, Option Base 1 sets the default lower bound of arrays. With Option Base 1, the following array declarations are the same. Dim Arr(5) Dim Arr(1 To 5) ' five elements in the array With Option Base 0, the following array declarations are the same. Dim Arr(5) Dim Arr(0 To 5) ' six elements in the array Personally, I think it is bad programming practice to omit the lower bound in an array declaration. In either of your examples, it seems to me that you have not omitted the lower bound in the array declaration, have you? So, I don't see the usefulness of the Option Base 1 or Option Base 0. Thanks for your comments and help. Dan |
#21
Posted to microsoft.public.excel.programming
|
|||
|
|||
"Listing Contents of Sub-Folders"
Thanks, Bob.
|
#22
Posted to microsoft.public.excel.programming
|
|||
|
|||
"Listing Contents of Sub-Folders"
Thanks Chip,
I think I see the light. Dan |
#23
Posted to microsoft.public.excel.programming
|
|||
|
|||
"Listing Contents of Sub-Folders"
Hi Myrna,
Thanks for your clarification. Dan |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I turn off "Initializing Folders" every time I open a file. | Excel Discussion (Misc queries) | |||
Convert cell "contents" into a "comment" | Excel Discussion (Misc queries) | |||
Excel - Golf - how to display "-2" as "2 Under" or "4"as "+4" or "4 Over" in a calculation cell | Excel Discussion (Misc queries) | |||
how to increase size of "name box" and "contents of cell " displa. | New Users to Excel | |||
"Search for files and folders" option doesnot work for 100% | Excel Discussion (Misc queries) |