Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
File list macro
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 --- Message posted from http://www.ExcelForum.com/ |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
File list macro
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 " wrote in message ... 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 --- Message posted from http://www.ExcelForum.com/ |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
File list macro
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 -- Message posted from http://www.ExcelForum.com |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
File list macro
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 "ianripping " wrote in message ... 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.com --- Message posted from http://www.ExcelForum.com/ |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Clear the file open file name dropdown list | Excel Discussion (Misc queries) | |||
The 'Recently used file list' does not show up under the 'File' menu. | Excel Worksheet Functions | |||
How to create a macro that compares a list to another list | New Users to Excel | |||
Convert List box from excel file to VBA list box object | Excel Programming | |||
Automate open file, update links, run macro, close and save file | Excel Programming |