file names

Is there a way I can collect multiple file names from within a folder and get
them to display within a excel. If it involves using a macro I haven't been
successful in getting one working yet.

For example if I had music file names and wanted them to display in cells of
the spreadsheet.

Create a module, and paste this code in it, then run PopulateDirectoryList.

Option Explicit

Sub PopulateDirectoryList()
'dimension variables
Dim objFSO As FileSystemObject, objFolder As Folder
Dim objFile As File, strSourceFolder As String, x As Long, i As Long
Dim wbNew As Workbook, wsNew As Worksheet

ToggleStuff False 'turn of screenupdating

Set objFSO = New FileSystemObject 'set a new object in memory
strSourceFolder = BrowseForFolder 'call up the browse for folder routine
If strSourceFolder = "" Then Exit Sub

Workbooks.Add 'create a new workbook

Set wbNew = ActiveWorkbook
Set wsNew = wbNew.Sheets(1) 'set the worksheet
wsNew.Activate
'format a header
With wsNew.Range("A1:F1")
.Value = Array("File", "Size", "Modified Date", "Last Accessed",
"Created Date", "Full Path", "Size")
.Interior.ColorIndex = 7
.Font.Bold = True
.Font.Size = 12
End With

With Application.FileSearch
.LookIn = strSourceFolder 'look in the folder browsed to
.FileType = msoFileTypeAllFiles 'get all files
.SearchSubFolders = True 'search sub directories
.Execute 'run the search

For x = 1 To .FoundFiles.Count 'for each file found, by the count
(or index)
i = x 'make the variable i = x
If x 60000 Then 'if there happens to be more than multipls of
60,000 files, then add a new sheet
i = x - 60000 'set i to the right number for row placement
below
Set wsNew = wbNew.Sheets.Add(after:=Sheets(wsNew.Index))
With wsNew.Range("A1:F1")
.Value = Array("File", "Parent Folder", "Full Path",
"Modified Date", _
"Last Accessed", "Size")
.Interior.ColorIndex = 7
.Font.Bold = True
.Font.Size = 12
End With

End If
On Error GoTo Skip 'in the event of a permissions error

Set objFile = objFSO.GetFile(.FoundFiles(x)) 'set the object to
get it's properties
With wsNew.Cells(1, 1) 'populate the next row with the variable
data
.Offset(i, 0) = objFile.Name
.Offset(i, 1) = Format((objFile.Size / 1024) / 1024, "00")
'in MB
.Offset(i, 2) = objFile.DateLastModified
.Offset(i, 3) = objFile.DateLastAccessed
.Offset(i, 4) = objFile.DateCreated
.Offset(i, 5) = objFile.Path

End With
' Next objFile
Skip:
'this is in case a Permission denied error comes up or an
unforeseen error
'Do nothing, just go to next file
Next x
wsNew.Columns("A:F").AutoFit

End With

'clear the variables
Set objFolder = Nothing
Set objFile = Nothing
Set objFSO = Nothing
Set wsNew = Nothing
Set wbNew = Nothing

ToggleStuff True 'turn events back on
End Sub
Sub ToggleStuff(ByVal x As Boolean)
Application.ScreenUpdating = x
Application.EnableEvents = x
End Sub


Function BrowseForFolder(Optional OpenAt As Variant) As Variant
'''Code from kpuls, www.VBAExpress.com..portion of Knowledge base
submission
''www.codeguru.com

Dim ShellApp As Object
Set ShellApp = CreateObject("Shell.Application"). _
BrowseForFolder(0, "Please choose a folder", 0, OpenAt)

On Error Resume Next
BrowseForFolder = ShellApp.self.Path
On Error GoTo 0

Set ShellApp = Nothing

Select Case Mid(BrowseForFolder, 2, 1)
Case Is = ":"
If Left(BrowseForFolder, 1) = ":" Then GoTo Invalid
Case Is = "\"
If Not Left(BrowseForFolder, 1) = "\" Then GoTo Invalid
Case Else
GoTo Invalid
End Select
Exit Function

Invalid:


ToggleStuff True
End Function





"Mr Widget" wrote:

Is there a way I can collect multiple file names from within a folder and get
them to display within a excel. If it involves using a macro I haven't been
successful in getting one working yet.

For example if I had music file names and wanted them to display in cells of
the spreadsheet.

Several methods to accomplish this.......I like Tushar's best if importing
to Excel.

To add a "Print Directory" feature to Explorer, go to
this KB Article.

http://support.microsoft.com/default...EN-US;q272623&

Or you can download Printfolder 1.2 from.....

http://no-nonsense-software.com/freeware/

I use PF 1.2 and find it to be more than adequate with custom
features.

OR Go to DOS(Command) prompt and directory.
Type DIR MYFILES.TXT

All the above create a *.TXT file which can be opened in Notepad or
Excel.

One more method if you want to by-pass the *.TXT file and pull
directly to Excel is to use Tushar Mehta's Excel Add-in. This allows
filtering and sorting once you have the data in Excel.

http://www.tushar-mehta.com/excel/so...ist/index.html

Download the ZIP file and un-zip to your Office\Library folder.

Note the installation instructions near bottom of page.


Gord Dibben MS Excel MVP

On Tue, 23 Mar 2010 16:20:11 -0700, Mr Widget
wrote:

Is there a way I can collect multiple file names from within a folder and get
them to display within a excel. If it involves using a macro I haven't been
successful in getting one working yet.

For example if I had music file names and wanted them to display in cells of
the spreadsheet.

Hi,

You could also try this. Press Ctrl+Fe New. Give the name as file_list.
In the refers to box, type the path of the directors from where you want the
list of files such as:

=FILES("D:\Documents\Material\sessions\docs\*.*")

Now in G1, enter the following formula

=index(file_list,row()) and copy down

--
Regards,

Ashish Mathur
Microsoft Excel MVP

"Mr Widget" wrote in message
...
Is there a way I can collect multiple file names from within a folder and
get
them to display within a excel. If it involves using a macro I haven't
been
successful in getting one working yet.

For example if I had music file names and wanted them to display in cells
of
the spreadsheet.