CODEWITHSUNDEEP
excelvba
XWormX
XWormX

Reputation: 103

Reading all folders in VBA

I put the following code in my excel. It is a script that I found on the net.. 

   Sub TestListFolders()
        Application.ScreenUpdating = False
        Workbooks.Add ' create a new workbook for the folder list
        ' add headers
        With Range("A1")
            .Formula = "Folder contents:"
            .Font.Bold = True
            .Font.Size = 12
        End With
        Range("A3").Formula = "Folder Path:"
        Range("B3").Formula = "Folder Name:"
        Range("C3").Formula = "Size:"
        Range("D3").Formula = "Subfolders:"
        Range("E3").Formula = "Files:"
        Range("F3").Formula = "Short Name:"
        Range("G3").Formula = "Short Path:"
        Range("A3:G3").Font.Bold = True
        ListFolders "C:\FolderName\", True
        Application.ScreenUpdating = True
    End Sub

Sub ListFolders(SourceFolderName As String, IncludeSubfolders As Boolean)
' lists information about the folders in SourceFolder
' example: ListFolders "C:\FolderName", True
Dim FSO As Scripting.FileSystemObject
Dim SourceFolder As Scripting.Folder, SubFolder As Scripting.Folder
Dim r As Long
    Set FSO = New Scripting.FileSystemObject
    Set SourceFolder = FSO.GetFolder(SourceFolderName)
    ' display folder properties
    r = Range("A65536").End(xlUp).Row + 1
    Cells(r, 1).Formula = SourceFolder.Path
    Cells(r, 2).Formula = SourceFolder.Name
    Cells(r, 3).Formula = SourceFolder.Size
    Cells(r, 4).Formula = SourceFolder.SubFolders.Count
    Cells(r, 5).Formula = SourceFolder.Files.Count
    Cells(r, 6).Formula = SourceFolder.ShortName
    Cells(r, 7).Formula = SourceFolder.ShortPath
    If IncludeSubfolders Then
        For Each SubFolder In SourceFolder.SubFolders
            ListFolders SubFolder.Path, True
        Next SubFolder
        Set SubFolder = Nothing
    End If
    Columns("A:G").AutoFit
    Set SourceFolder = Nothing
    Set FSO = Nothing
    ActiveWorkbook.Saved = True
End Sub

The script fails because I am missing this object.

New Scripting.FileSystemObject

How do I get the object's library? is there another script that I can use instead, which doesnt rely on that object?

Upvotes: 3

Views: 1437

Answers (2)

Gary McGill
Gary McGill

Reputation: 27516

VBA itself has built-in functions to access the file system (e.g. Dir), but they're pretty unpleasant to use.

To make the code above work, simply add a reference (Tools->References) to the Microsoft Scripting Runtime".

Upvotes: 3

user2140173
user2140173

Reputation:

You are trying to bind an object to a library which you are missing references to.

this

Dim FSO As Scripting.FileSystemObject

will throw a User-defined type not defined Error

udferror

You need to add references to already installed, but not included in the project Microsoft Scripting Runtime library

To do that, Select Tools » References

references

then scroll down, find and tick Microsoft Scripting Runtime Library

added

Now, re-run your procedure and everything should be working as expected.

Additionally, note: you may want to edit this ListFolders "C:\FolderName\", True line and provide the path to your desired path.

Upvotes: 1

Related Questions