Listing files within a folder
 

Lets say I have a folder named "Jeff" with 5 files in it:
A,B,C,D,E.

I would like to create VBA code in an excel worksheet that
will simply go to folder "Jeff" and list all files within
the folder. Can anyone assist? Thanks.

Listing files within a folder
 

Here's one way. Change strDir to suit. Adds a new sheet to the
ActiveWorkbook and lists the files in the first Column.

Sub ListFiles()

Dim intI As Integer
Dim rngList As Range
Dim strFile As String
Dim strDir As String
Dim wShtList As Worksheet

strDir = "C:\Backup"

Application.ScreenUpdating = False

With Application.FileSearch
.NewSearch
.LookIn = strDir
.SearchSubFolders = False
.FileType = msoFileTypeAllFiles
If .Execute() 0 Then
Set wShtList = ActiveWorkbook.Sheets.Add
wShtList.Name = "File Listing"
Set rngList = wShtList.Cells(1, 1)
For intI = 1 To .FoundFiles.Count
rngList.Value = GetFileName(.FoundFiles(intI))
Set rngList = rngList(2, 1)
Next intI
End If
End With

Application.ScreenUpdating = True

End Sub

Function GetFileName(strPath As String)
'' Used to retrieve the filename from a full path.

Dim intLoc As Integer
Dim strTemp As String
Dim strSep As String

strSep = Application.PathSeparator
strTemp = strPath
intLoc = 1
While InStr(intLoc, strTemp, strSep) 0
intLoc = InStr(intLoc, strTemp, strSep) + 1
Wend
GetFileName = Right(strTemp, Len(strTemp) - intLoc + 1)

End Function

Tested using Excel 97SR2 on Windows 98SE,

HTH
Paul
--------------------------------------------------------------------------------------------------------------
Be advised to back up your WorkBook before attempting to make changes.
--------------------------------------------------------------------------------------------------------------

Lets say I have a folder named "Jeff" with 5 files in it:
A,B,C,D,E.

I would like to create VBA code in an excel worksheet that
will simply go to folder "Jeff" and list all files within
the folder. Can anyone assist? Thanks.

Listing files within a folder
 

Jeff,

Here is some code that will do that. Just change the "ActiveWorkbook.Path"
line in the InsertFiles routine and make it your path; e.g. "C:\Documents
and Settings\Jeff\My Documents" or whatever. It will plop the names in the
cells of the active workbook.

Sub InsertFiles()
InsertFileList (ActiveWorkbook.Path)
End Sub


Private Sub InsertFileList(sDir)

Dim sArray() As String
Dim nCells As Integer
Dim sFile As String
ReDim sArray(0)

sFile = Dir(sDir & "\", vbNormal)

Do While sFile < ""

If sFile < "." And sFile < ".." Then

If (GetAttr(sDir & "\" & sFile) And vbNormal) = vbNormal Then
sArray(UBound(sArray)) = sFile
ReDim Preserve sArray(UBound(sArray) + 1)
End If
End If

sFile = Dir()

Loop

For nCells = 1 To UBound(sArray) + 1
Cells(nCells, 1) = sArray(nCells - 1)
Next

End Sub



--
Charles
www.officezealot.com


"Jeff" wrote in message
...
Lets say I have a folder named "Jeff" with 5 files in it:
A,B,C,D,E.

I would like to create VBA code in an excel worksheet that
will simply go to folder "Jeff" and list all files within
the folder. Can anyone assist? Thanks.

Listing files within a folder
 

Modify the code below as needed:
Note that lbBackUp is the name of a ListBox I use in an Excel W/B

DoDir g_oFSO.GetFolder(ThisWorkbook.Path & "\")
Sub DoDir(Folder)

On Error Resume Next
' On Error GoTo 0

Dim File, SubFolder

For Each File In Folder.files
' MsgBox File.Path
lbBackUp.AddItem File
Next

For Each SubFolder In Folder.SubFolders
' MsgBox SubFolder
lbBackUp.AddItem SubFolder & "\"
' DoDir g_oFSO.GetFolder(SubFolder)
Next

' If Err.Number < 0 then
' MsgBox Err.Number & Chr(13) _
' & Err.Description
' Err.Clear
' End If

End Sub

Hope that helps

Philippe
"Jeff" wrote in message
...
Lets say I have a folder named "Jeff" with 5 files in it:
A,B,C,D,E.

I would like to create VBA code in an excel worksheet that
will simply go to folder "Jeff" and list all files within
the folder. Can anyone assist? Thanks.