VBA Excel - Code to get an index from my C:Drive

[nieironsio]

Hello

can anyone give me a code for Excel VBA that will give me a complete index list of all files on a drive (e.g. my C)

Thanks in advance

Nie

 

[ck1999]

There is a program called folderinfo that will export a list into excel.


ck1999

 

[nieironsio]

blocked by my work IT stuff

 

[ck1999]

Try this link

url:

http://www.cpearson.com/excel/FolderTree.aspx

or

url:

http://www.exceltip.com/st/List_fil...Runtime_using_VBA_in_Microsoft_Excel/446.html

ck1999

 

[nieironsio]

Hello

Thanks for your help - IT blocked the download on the first and the 2nd link wouldn't load for some reason.

 

[ck1999]

2nd site is

Microsoft Scripting Runtime is included in these products:
Windows98, Windows2000, IE5, and Office2000.
The macro examples below assumes that your VBA project has added a reference to the Microsoft Scripting Runtime library.
You can do this from within the VBE by selecting the menu Tools, References and selecting Microsoft Scripting Runtime.

Sub TestListFilesInFolder()
Workbooks.Add ' create a new workbook for the file list
' add headers
With Range("A1")
.Formula = "Folder contents:"
.Font.Bold = True
.Font.Size = 12
End With
Range("A3").Formula = "File Name:"
Range("B3").Formula = "File Size:"
Range("C3").Formula = "File Type:"
Range("D3").Formula = "Date Created:"
Range("E3").Formula = "Date Last Accessed:"
Range("F3").Formula = "Date Last Modified:"
Range("G3").Formula = "Attributes:"
Range("H3").Formula = "Short File Name:"
Range("A3:H3").Font.Bold = True
ListFilesInFolder "C:\FolderName\", True
' list all files included subfolders
End Sub

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

ck1999

 

[nieironsio]

Thanks very much for your help - apologies for the delay in responding - i managed to create a version adapted from one someone at work sent me.