Extract files name from a folder

[milluz70]

Hi,
I have a folder (c:\codes...)that contains files .pdf.
I would like to know, if is possible, extract a list of them and copy this list in a excel column.
If yes ... when I'll add a file in (c:\codes) is possible to update automatically the excel coloum?

Thank you in advanced and best regards,

Milluz70

[Bob Phillips]

I have a folder (c:\codes...)that contains files .pdf.
I would like to know, if is possible, extract a list of them and copy this list in a excel column.

[vba]

Sub ProcessFiles()
Dim FSO As Object
Dim i As Long
Dim sFolder As String
Dim Folder As Object
Dim file As Object
Dim Files As Object
Dim cnt As Long

Set FSO = CreateObject("Scripting.FileSystemObject")

sFolder = "C:\MyTest"
If sFolder <> "" Then
Set Folder = FSO.GetFolder(sFolder)

Set Files = Folder.Files
cnt = 1
For Each file In Files
If file.Type = "Adobe Acrobat Document" Then
Cells(cnt, "A").Value = file.Name
cnt = cnt + 1
End If
Next file

End If ' sFolder <> ""

End Sub
[/vba]

I

f yes ... when I'll add a file in (c:\codes) is possible to update automatically the excel coloum?

Not really, unless you continually check the directory and re-build the list.

[milluz70]

Great xld.
It is perfect for my need.

Thank you !!!

milluz70

[marlonlp]

Hi, i use this code for show directory contents.
Sorry, but I?m brazilian and my English is poor...


[vba]
Private Sub Workbook_Open()
Set pa = Application.FileSearch
With pa
.LookIn = "C:\Codes"
.SearchSubFolders = True
.Filename = *.pdf

If .Execute(SortBy:=msoSortByFileName) = 1 Then
MsgBox "Found " & .FoundFiles.Count & " file."
For i = 1 To .FoundFiles.Count
Plan1.Range("B500").End(xlUp).Offset(1, 0).Value = .FoundFiles(i)
Next i

ElseIf .Execute(SortBy:=msoSortByFileName) > 1 Then
MsgBox "Found " & .FoundFiles.Count & " files."
For i = 1 To .FoundFiles.Count
Plan1.Range("B500").End(xlUp).Offset(1, 0).Value = .FoundFiles(i)
Next i

Else
MsgBox "No Found Files."
End If
End With
End Sub
[/vba]

[Bob Phillips]

Marlon,

FYI

If you precede you code with

vba

within square brackets (couldn't type them else it will treat this as VBA :-), and then

[/vba]

afterwards, it is much more readable.

And FileSearch has been dropped from Excel 2007.

[marlonlp]

Ok xld, thanks.