File list macro

[ianripping]

I got this code from a site but want it to look at C:\Temp, cant get it
to work. Any idea?

Sub ListFilesInFolder(SourceFolderName As String, IncludeSubfolders As
Boolean)
' lists information about the files in SourceFolder
' example: ListFilesInFolder "C:\FolderName", True
Dim FSO As Scripting.FileSystemObject
Dim SourceFolder As Scripting.Folder, SubFolder As Scripting.Folder
Dim FileItem As Scripting.File
Dim r As Long
Set FSO = New Scripting.FileSystemObject
Set SourceFolder = FSO.GetFolder(SourceFolderName)
r = Range("A65536").End(xlUp).Row + 1
For Each FileItem In SourceFolder.Files
' display file properties
Cells(r, 1).Formula = FileItem.Path & FileItem.Name
Cells(r, 2).Formula = FileItem.Size
Cells(r, 3).Formula = FileItem.Type
Cells(r, 4).Formula = FileItem.DateCreated
Cells(r, 5).Formula = FileItem.DateLastAccessed
Cells(r, 6).Formula = FileItem.DateLastModified
Cells(r, 7).Formula = FileItem.Attributes
Cells(r, 8).Formula = FileItem.ShortPath & FileItem.ShortName
' use file methods (not proper in this example)
' FileItem.Copy "C:\FolderName\Filename.txt", True
' FileItem.Move "C:\FolderName\Filename.txt"
' FileItem.Delete True
r = r + 1 ' next row number
Next FileItem
If IncludeSubfolders Then
For Each SubFolder In SourceFolder.SubFolders
ListFilesInFolder SubFolder.Path, True
Next SubFolder
End If
Columns("A:H").AutoFit
Set FileItem = Nothing
Set SourceFolder = Nothing
Set FSO = Nothing
ActiveWorkbook.Saved = True
End Sub

 

[Trevor Shuttleworth]

Ian

you might need to use GetSpecialFolder for the temp directory

Set SourceFolder = FSO.GetSpecialFolder(2)

See the Help:

GetSpecialFolder Method
Description

Returns the special folder specified.

Syntax

object.GetSpecialFolder(folderspec)

The GetSpecialFolder method syntax has these parts:

Part Description
object Required. Always the name of a FileSystemObject.
folderspec Required. The name of the special folder to be returned.
Can be any of the constants shown in the Settings section.



Settings

The folderspec argument can have any of the following values:

Constant Value Description
WindowsFolder 0 The Windows folder contains files installed by the
Windows operating system.
SystemFolder 1 The System folder contains libraries, fonts, and device
drivers.
TemporaryFolder 2 The Temp folder is used to store temporary files.
Its path is found in the TMP environment variable.

Regards

Trevor

 

[ianripping]

this works easier: -

Sub ListFiles()
F = Dir("C:\*.XLS")
Do While Len(F) > 0
ActiveCell.Formula = F
ActiveCell.Offset(1, 0).Select
F = Dir()
Loop
End Sub

got it from microsoft.co

 

[Trevor Shuttleworth]

maybe you should have posted that solution back sooner to stop people from
wasting their time finding an answer to a problem you no longer have.

Seems like that's the answer to a different question but if it does what you
want, fair enough. The original solution is much more sophisticated than
this and provides much more information