Reading from a folder

A

Alex

Hi,

Is there any macro codes that I can use to read all the file names in a
particular folder or path and print the file names to an excel worksheet?
 
B

Bob Phillips

Option Explicit

Private cnt As Long
Private arfiles
Private level As Long

Sub Folders()
Dim i As Long
Dim sFolder As String
Dim iStart As Long
Dim iEnd As Long
Dim fOutline As Boolean

arfiles = Array()
cnt = -1
level = 1

sFolder = "E:\"
ReDim arfiles(2, 0)
If sFolder <> "" Then
SelectFiles sFolder
Application.DisplayAlerts = False
On Error Resume Next
Worksheets("Files").Delete
On Error GoTo 0
Application.DisplayAlerts = True
Worksheets.Add.Name = "Files"
With ActiveSheet
For i = LBound(arfiles, 2) To UBound(arfiles, 2)
If arfiles(0, i) = "" Then
If fOutline Then
Rows(iStart + 1 & ":" & iEnd).Rows.Group
End If
With .Cells(i + 1, arfiles(2, i))
.Value = arfiles(1, i)
.Font.Bold = True
End With
iStart = i + 1
iEnd = iStart
fOutline = False
Else
.Hyperlinks.Add Anchor:=.Cells(i + 1, arfiles(2, i)), _
Address:=arfiles(0, i), _
TextToDisplay:=arfiles(1, i)
iEnd = iEnd + 1
fOutline = True
End If
Next
.Columns("A:Z").ColumnWidth = 5
End With
End If
'just in case there is another set to group
If fOutline Then
Rows(iStart + 1 & ":" & iEnd).Rows.Group
End If

Columns("A:Z").ColumnWidth = 5
ActiveSheet.Outline.ShowLevels RowLevels:=1
ActiveWindow.DisplayGridlines = False
 
G

Gary''s Student

Change the folder path and filetype to suit:

Sub list_um()
Dim F As String
Dim roww As Long
roww = 0
Dim FileLocSpec As String
FileLocSpec = "C:\Temp\*.*"
F = Dir(FileLocSpec)
Do Until F = ""
roww = roww + 1
Cells(roww, 1).Value = F
F = Dir
Loop
End Sub
 
F

Frank Pytel

Hello Gary;

This worked great. I was able to modify the code so that it would select all
files in a folder.

If you have time, how would I modify this code to get all of the files based
on the path that I would paste into cell A1? Is this possible?

Thanks for your help, Gary (and anyone else that would know of an answer to
this question.

Have a Great Day.

Frank Pytel
 
Top