PDA

View Full Version : picking up file names in one folder



sureshprabhu
03-29-2007, 03:44 AM
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
03-29-2007, 05:09 AM
Have a look at the Dir function in VBA help.

sureshprabhu
03-29-2007, 05:16 AM
Hi!
I am very new to VBA, so it will be more helpful to me if you give me one example.
Thanks

Bob Phillips
03-29-2007, 05:23 AM
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

CBrine
03-29-2007, 10:27 AM
Same things as dir, using fso.


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


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
03-29-2007, 10:28 AM
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
03-29-2007, 11:16 AM
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
03-30-2007, 11:26 AM
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
03-30-2007, 12:54 PM
Nor me, just ruminating out aloud.

SureshSuresh
04-03-2007, 11:55 PM
Hi!
Thanks to Bob and Cal and all who helped me to do this! thankyou very much