picking up file names in one folder

[sureshprabhu]

Hi!
I want to pick all names of files with different extensions in one excel sheet. Those files are in same folder with my Excel file. How can i do this? Please help me!
Thanks in advance
SureshP

[mdmackillop]

Have a look at the Dir function in VBA help.

[sureshprabhu]

Hi!
I am very new to VBA, so it will be more helpful to me if you give me one example.
Thanks

[Bob Phillips]

[vba]

Public Sub Test()
Dim sFile As String
Dim i As Long

i = 1
sFile = Dir("C:\Projects\Temp\*.*") '<=== change to suit
Do While sFile <> ""
Cells(i, "A").Value = sFile
On Error Resume Next
sFile = Dir
On Error GoTo 0
i = i + 1
Loop

End Sub

[/vba]

[CBrine]

Same things as dir, using fso.

[VBA]
Dim FSO As object, f As object, Path As String
Set FSO = CreateObject("Scripting.FileSystemObject")
For Each f In FSO.GetFolder("C:\").Files
ActiveSheet.Range("a" & Application.Rows.Count).End(xlUp).Offset(1, 0) = f.Name
Next f
[/VBA]

Although my code is getting far more files for some reason? Maybe I'm picking up hidden and system files as well? Not sure.

[CBrine]

I did a check, and my code is extracting hidden and systems files, which it seems Bob's code is not. Shouldn't be an issue in a user folder though, either way, but something to keep in mind.

Cal

[Bob Phillips]

If files are hidden, and a directory listing is set to not show system files, it could be argued that they shouldn't be picked up, after all it is either company or user policy.

[CBrine]

Bob,
No aurgement from me, just something I thought should be pointed out to the OP, so they can make an educated decision on which way to go.

Cal

[Bob Phillips]

Nor me, just ruminating out aloud.

[SureshSuresh]

Hi!
Thanks to Bob and Cal and all who helped me to do this! thankyou very much