can i get a list of files in specified folder?

This code loads all names of files from "C:\Workings" into array "FilesList", you will have to add a reference to the Microsoft Scripting Runtime.


Sub AllFilesInFolder()

Dim objFS As New Scripting.FileSystemObject
Dim objFolder As Scripting.folder
Dim objFile As Scripting.file

Dim FilesList() As String
Dim i As Integer

If objFS.FolderExists("C:\Workings") Then
Set objFolder = objFS.GetFolder("C:\Workings")
Else
MsgBox "Folder doesn't exist"
Exit Sub
End If


ReDim FilesList(1 To objFolder.Files.Count)
i = 1

For Each objFile In objFolder.Files
FilesList(i) = objFile.Name
i = i + 1
Next objFile

Set objFile = Nothing
Set objFolder = Nothing
Set objFS = Nothing

End Sub

HTH

eeehmm.... question..:

i tried this, but it seems not to be functioning.

By "adding a reference to the Microsoft Scripting Runtime" I presume you mean defining an array range, right?

Hope this helps,

Will

As you want to use objects other than Excel objects you have to add references: while in VBA editor: Tools>References>Microsoft Scripting Runtime

HTH

Both methods seems to be doing nothing really... What am i doing wrong? I follewed your code exactly Amigos, including the path you are using. Created the folder and copied some .xls files into it, but still no result.

Where are the names to be listed anyway? I created an array range as "fileslist", but no good...

OK,
filenames (in both codes) are stored in array called "FilesList" in mine and "fileList" in WillR's code. To access them you use FilesList(i) where "i" is index number of file in the array. So change part of the code into:

For Each objFile In objFolder.Files
FilesList(i) = objFile.Name
ActiveSheet.Cells(i, 1).Value = FilesList(i)
i = i + 1
Next objFile

and you will display all files in first column starting from first row.
now you can access them from this sheet or you can still access them from "the memory" using FilesList(i), so FilesList(3) will give you third file.


HTH

I don't unserstand why i still can't get this simple thing working Can you please PM me a sample file? ray:

You have whole code below, what happends when you run it?

Sub AllFilesInFolder()

Dim objFS As New Scripting.FileSystemObject
Dim objFolder As Scripting.folder
Dim objFile As Scripting.file

Dim FilesList() As String
Dim i As Integer

If objFS.FolderExists("C:\Workings") Then ' change this part into your folder
Set objFolder = objFS.GetFolder("C:\Workings") ' change this part into your folder
Else
MsgBox "Folder doesn't exist" ' if folder doesn't exist - end sub
Exit Sub
End If


ReDim FilesList(1 To objFolder.Files.Count) ' array for file names resizes
i = 1

For Each objFile In objFolder.Files '- for each file in folder
FilesList(i) = objFile.Name ' - load file name to array
ActiveSheet.Cells(i, 1).Value = FilesList(i) ' - print file name on Sheet1 in first column and row i
i = i + 1
Next objFile

Set objFile = Nothing
Set objFolder = Nothing
Set objFS = Nothing

End Sub

I can email you file but I need your email - you can PM me.

Merlin,

I tested my code before posting it and it works fine.

It lists the found files in the active worksheet, from A1 down.

If no files are found, you get a msgbox telling you

Obviously you need to amend my hard-coded directory path to one that suits you "C:\your path\your folder\"

Will