Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Folder Contents to Excel
How can I copy (en mass) a folders contents, to show only the names.ext of
the files. I'll be using these names to create links in excel. I've tried the insert hyperlink, but it is too slow for all the work here and there are toooo many to manually type in. ie: \2006 \2007\ abc.pdf def.pdf ghi.pdf xyz.pdf lmn.xls My goal is to create an index of all 1500 or so files with working links to each file. Additionally, it would have to be updated weekly with new files. thanks for your help. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Folder Contents to Excel
A simple way is to use the DOS-style dir function, with syntax along
the lines of: dir /b /s c:\list.txt Which will generate a list of all files in the current directory into a file on C drive root called list.txt. Another way is to use some VBA (adopted from John Walkenbach): Dim rowCount As Long Public Sub ListFiles(ByVal myDir As String) Dim Dirs() As String Dim NumDirs As Long Dim FileName As String Dim PathAndName As String Dim i As Long If Right(myDir, 1) < "\" Then myDir = myDir & "\" If rowCount = 0 Then rowCount = 1 Cells(rowCount, 1).Value = myDir rowCount = rowCount + 1 FileName = Dir(myDir & "*.*", vbDirectory) Do While Len(FileName) < 0 If Left(FileName, 1) < "." Then 'Current dir PathAndName = myDir & FileName If (GetAttr(PathAndName) And vbDirectory) = vbDirectory Then ReDim Preserve Dirs(0 To NumDirs) As String Dirs(NumDirs) = PathAndName NumDirs = NumDirs + 1 Else Cells(rowCount, 2) = FileName rowCount = rowCount + 1 End If End If FileName = Dir() Loop For i = 0 To NumDirs - 1 ListFiles Dirs(i) Next i End Sub On Jan 22, 11:43*pm, TC Daniel wrote: How can I copy (en mass) a folders contents, to show only the names.ext of the files. I'll be using these names to create links in excel. I've tried the insert hyperlink, but it is too slow for all the work here and there are toooo many to manually type in. ie: \2006 \2007\ * *abc.pdf * *def.pdf * *ghi.pdf * *xyz.pdf * *lmn.xls My goal is to create an index of all 1500 or so files with working links to each file. Additionally, it would have to be updated weekly with new files. thanks for your help. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Folder Contents to Excel
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/ 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, 22 Jan 2008 20:43:00 -0800, TC Daniel wrote: How can I copy (en mass) a folders contents, to show only the names.ext of the files. I'll be using these names to create links in excel. I've tried the insert hyperlink, but it is too slow for all the work here and there are toooo many to manually type in. ie: \2006 \2007\ abc.pdf def.pdf ghi.pdf xyz.pdf lmn.xls My goal is to create an index of all 1500 or so files with working links to each file. Additionally, it would have to be updated weekly with new files. thanks for your help. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Listing Folder Contents into a Column | Excel Worksheet Functions | |||
Listing the contents of a folder | Excel Discussion (Misc queries) | |||
How can I insert the contents of a folder into Excel | Excel Discussion (Misc queries) | |||
To display contents of a folder in an excel sheet using Excel MACRO | Excel Worksheet Functions | |||
Exporting folder contents to excel or text format | Excel Discussion (Misc queries) |