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.

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.

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.