Announcement

Collapse
No announcement yet.

[Solved] VBA: FileSystemObject

Collapse
This topic is closed.
X
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • [Solved] VBA: FileSystemObject



    Hi I have the following code and wisht o execute it so it runs for the folder that the excel file is in.

    Public Sub FileInfo()
    Dim fso As FileSystemObject
    Dim myFile As file
    Dim r As Integer

    'Get a new fileSystemObject
    Set fso = New FileSystemObject

    r = 2 'initialize row counter beginning with row 2

    'For Each myFile In fso.GetParentFolderName(fso.GetAbsolutePathName("asw28-5")).Files
    For Each myFile In fso.GetSpecialFolder(0).Files

    'File name goes in column 1
    ThisWorkbook.Worksheets("Files").Cells(r, 1).Value = myFile.Name

    'File type goes in column 2
    ThisWorkbook.Worksheets("Files").Cells(r, 2).Value = myFile.Type

    'File size goes in column 3
    ThisWorkbook.Worksheets("Files").Cells(r, 3).Value = myFile.Size

    ThisWorkbook.Worksheets("Files").Cells(r, 4).Value = myFile.DateLastModified

    r = r + 1 'increment row counter

    Next

    Set fso = Nothing 'done with this

    End Sub


    any help would be appreciated

  • #2


    torpedo13,

    The GetSpecialFolder method only has three valid constants:
    0 = Windows
    1 = System
    2 = Temporary

    To seach a different specific folder use the GetFolder method. It accepts a path to the folder as a string.

    Code:
    Public Sub FileInfo()
    Dim fso As FileSystemObject
    Dim myFile As file
    Dim r As Integer
    Dim mypath As String
    
    mypath = ActiveWorkbook.Path
    'Get a new fileSystemObject
    Set fso = New FileSystemObject
    
    r = 2 'initialize row counter beginning with row 2
    
    'For Each myFile In fso.GetParentFolderName(fso.GetAbsolutePathName("asw28-5")).Files
    For Each myFile In fso.GetFolder(mypath).Files
    
    'File name goes in column 1
    ThisWorkbook.Worksheets("Files").Cells(r, 1).Value = myFile.Name
    
    'File type goes in column 2
    ThisWorkbook.Worksheets("Files").Cells(r, 2).Value = myFile.Type
    
    'File size goes in column 3
    ThisWorkbook.Worksheets("Files").Cells(r, 3).Value = myFile.Size
    
    ThisWorkbook.Worksheets("Files").Cells(r, 4).Value = myFile.DateLastModified
    
    r = r + 1 'increment row counter
    
    Next
    
    Set fso = Nothing 'done with this
    
    End Sub
    Hope that is what you were after
    Weasel
    Kind Regards
    Pesky Weasel
    "I have a plan so cunning, you could put a tail on it and call it a Weasel." EB
    Eagles may soar, but Weasels don't get sucked into jet engines.
    Templates and Calculators
    The Way of the Weasel
    Download Ivan & Colo's HTML Maker Here
    101 Excel Hacks - Great New Book
    Huge Savings on Excel Training

    Comment

    Working...
    X