Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Display Contents of Folder
I'm in need of VBA code that will list in an Excel sheet the filenames of all
documents of a specific folder. Any help on this is much appreciated. Thanks, Chad |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Display Contents of Folder
This will do that, it stores them in an array then writes it to sheet1
Function GetFileNames() Dim MyName, MyPath Dim FNames() 'Find how many file exist ReDim FNames(0) MyPath = "C:\" 'You need to change this to your requirement MyName = Dir(MyPath, vbDirectory) Do While MyName < "" If MyName < "." And MyName < ".." Then On Error Resume Next If (GetAttr(MyPath & MyName) And vbDirectory) < vbDirectory Then ReDim Preserve FNames(UBound(FNames) + 1) FNames(UBound(FNames)) = MyPath & MyName End If End If MyName = Dir Loop For x = 1 To UBound(FNames) Sheet1.Cells(x, 1) = FNames(x) Next x End Function -- Message posted via http://www.officekb.com |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Display Contents of Folder
This will put a list of files in column G. Change MYPATH to the path for
the folder you want to look at, ending with a backslash \ Hope this helps, James Const MYPATH = "c:\MyFiles\" Sub ListFiles() Dim PutRow As Long, fName As String PutRow = 1 Columns("g").Clear fName = Dir(MYPATH & "*.*") Cells(PutRow, "g") = fName PutRow = PutRow + 1 Do fName = Dir Cells(PutRow, "g") = fName PutRow = PutRow + 1 Loop Until fName = "" End Sub "Chad" wrote in message ... I'm in need of VBA code that will list in an Excel sheet the filenames of all documents of a specific folder. Any help on this is much appreciated. Thanks, Chad |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Display Contents of Folder
I use this routine
Sub Searchfolder() Set fs = Application.FileSearch With fs .LookIn = "C:\Documents and Settings\User\My Documents" 'Your path here .Filename = "*" .Searchsubfolders = False If .Execute(SortBy:=msoSortByFileName, _ SortOrder:=msoSortOrderAscending) 0 Then MsgBox "There were " & .FoundFiles.Count & _ " file(s) found." For I = 1 To .FoundFiles.Count 'Truncate to File Name flstring = .FoundFiles(I) namelen = InStrRev(flstring, "\") flstring = Right(flstring, Len(flstring) - namelen) 'Output to sheet ActiveCell = flstring ActiveCell.Offset(1, 0).Select Next I Else MsgBox "There were no files found." End If End With End Sub "Chad" wrote: I'm in need of VBA code that will list in an Excel sheet the filenames of all documents of a specific folder. Any help on this is much appreciated. Thanks, Chad |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Display Contents of Folder
I think this will work perfectly. Thank you so much!
-Chad "Crowbar via OfficeKB.com" wrote: This will do that, it stores them in an array then writes it to sheet1 Function GetFileNames() Dim MyName, MyPath Dim FNames() 'Find how many file exist ReDim FNames(0) MyPath = "C:\" 'You need to change this to your requirement MyName = Dir(MyPath, vbDirectory) Do While MyName < "" If MyName < "." And MyName < ".." Then On Error Resume Next If (GetAttr(MyPath & MyName) And vbDirectory) < vbDirectory Then ReDim Preserve FNames(UBound(FNames) + 1) FNames(UBound(FNames)) = MyPath & MyName End If End If MyName = Dir Loop For x = 1 To UBound(FNames) Sheet1.Cells(x, 1) = FNames(x) Next x End Function -- Message posted via http://www.officekb.com |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Display Contents of Folder
You can use my DirTree add-in that will list the subfolders and files of a
specified folder, with many display options. See www.cpearson.com/Excel/FolderTree.aspx -- Cordially, Chip Pearson Microsoft MVP - Excel, 10 Years Pearson Software Consulting www.cpearson.com (email on the web site) "Chad" wrote in message ... I'm in need of VBA code that will list in an Excel sheet the filenames of all documents of a specific folder. Any help on this is much appreciated. Thanks, Chad |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Display Contents of Folder
Is it possible to access the indvidual file attributes that I can view
in different columns within Windows 'Exploder' (ie: Attributes, Owner, Author, Title, Artist, etc)? And if so, are there any VBA examples out there I could look at? Thanks. -pb |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Display Contents of Folder
I suggest downloading Tushar's Directory Listing add-in from
http://www.tushar-mehta.com/ scroll down to Add-insDirectory Listing. Download the ZIP file and un-zip to your Office\Library folder. Gord Dibben MS Excel MVP On Tue, 16 Oct 2007 13:59:46 -0700, cubbybear3 wrote: Is it possible to access the indvidual file attributes that I can view in different columns within Windows 'Exploder' (ie: Attributes, Owner, Author, Title, Artist, etc)? And if so, are there any VBA examples out there I could look at? Thanks. -pb |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Display Contents of Folder
Is it possible to get a list of the entire folder contents, including other
folders that may exist? This code works perfectly to list all files outside of folders within the desired destination. Thanks! "Zone" wrote: This will put a list of files in column G. Change MYPATH to the path for the folder you want to look at, ending with a backslash \ Hope this helps, James Const MYPATH = "c:\MyFiles\" Sub ListFiles() Dim PutRow As Long, fName As String PutRow = 1 Columns("g").Clear fName = Dir(MYPATH & "*.*") Cells(PutRow, "g") = fName PutRow = PutRow + 1 Do fName = Dir Cells(PutRow, "g") = fName PutRow = PutRow + 1 Loop Until fName = "" End Sub "Chad" wrote in message ... I'm in need of VBA code that will list in an Excel sheet the filenames of all documents of a specific folder. Any help on this is much appreciated. Thanks, Chad |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Display Contents of Folder
Change it like this to get files plus subdirectories.
fName = Dir(MYPATH, vbDirectory) This doesn't show the files within the subdirectories, but you can do add the subdirectories to the path one at a time, then do Dir to get those files. HTH, James "Chad" wrote in message ... Is it possible to get a list of the entire folder contents, including other folders that may exist? This code works perfectly to list all files outside of folders within the desired destination. Thanks! "Zone" wrote: This will put a list of files in column G. Change MYPATH to the path for the folder you want to look at, ending with a backslash \ Hope this helps, James Const MYPATH = "c:\MyFiles\" Sub ListFiles() Dim PutRow As Long, fName As String PutRow = 1 Columns("g").Clear fName = Dir(MYPATH & "*.*") Cells(PutRow, "g") = fName PutRow = PutRow + 1 Do fName = Dir Cells(PutRow, "g") = fName PutRow = PutRow + 1 Loop Until fName = "" End Sub "Chad" wrote in message ... I'm in need of VBA code that will list in an Excel sheet the filenames of all documents of a specific folder. Any help on this is much appreciated. Thanks, Chad |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Folder Contents to Excel | Excel Discussion (Misc queries) | |||
Listing the contents of a folder | Excel Discussion (Misc queries) | |||
To display contents of a folder in an excel sheet using Excel MACRO | Excel Worksheet Functions | |||
Add contents of A1 in all workbooks within a folder | Excel Programming | |||
contents of folder | Excel Programming |