VBS Script To List Folders And Subfolders Files And Send To Excel

 

This VBS script will allow you to hard code a directory folder name and when executed will send all of the files in the root directory folder and all the sub folders to an excel spreadsheet.

 

VBS Script:

 

objStartFolder = "C:\MyFiles"

 

Set objExcel = CreateObject("Excel.Application")

objExcel.Visible = True

objExcel.Workbooks.Add

intRow = 2

 

objExcel.Cells(1, 1).Value = "Folder"

objExcel.Cells(1, 2).Value = "File Name"

 

Set objFSO = CreateObject("Scripting.FileSystemObject")

Set objFolder = objFSO.GetFolder(objStartFolder)

 

Set colFiles = objFolder.Files

For Each objFile in colFiles

objExcel.Cells(intRow, 1).Value = objfolder.Name

objExcel.Cells(intRow, 2).Value = objFile.Name

intRow = intRow + 1

Next

 

ShowSubfolders objFSO.GetFolder(objStartFolder)

 

Sub ShowSubFolders(Folder)

For Each Subfolder in Folder.SubFolders

objExcel.Cells(intRow, 1).Value = Subfolder.Path

 

Set objFolder = objFSO.GetFolder(Subfolder.Path)

Set colFiles = objFolder.Files

For Each objFile in colFiles

objExcel.Cells(intRow, 2).Value = objFile.Name

intRow = intRow + 1

Next

 

ShowSubFolders Subfolder

Next

End Sub

 

objExcel.Range("A1:B1").Select

objExcel.Selection.Interior.ColorIndex = 19

objExcel.Selection.Font.ColorIndex = 11

objExcel.Selection.Font.Bold = True

objExcel.Cells.EntireColumn.AutoFit

 

MsgBox "Done"

 

 

 

Published Sunday, October 26, 2008 2:34 PM by dhite
Filed under:

Comments

No Comments