PDA

View Full Version : How to get the file name in the folder?



clif
11-02-2011, 06:32 AM
My question is how to get the filename in particular cell in excel without trigger the brower and click confirm button ?


Sub GetFileNames()
Sheets("Purchase").Select
Range("L3").Select
Dim xRow As Long
Dim xDirect$, xFname$, InitialFoldr$
'
InitialFoldr$ = "C:\abc\bcf\abc\"
'
With Application.FileDialog(msoFileDialogFolderPicker)
.InitialFileName = Application.DefaultFilePath & "\"
.Title = "Please select a folder to list Files from"
.InitialFileName = InitialFoldr$
.Show
If .SelectedItems.Count <> 0 Then
xDirect$ = .SelectedItems(1) & "\"
xFname$ = Dir(xDirect$, 7)
Do While xFname$ <> ""
ActiveCell.Offset(xRow) = xFname$
xRow = xRow + 1
xFname$ = Dir
Loop
End If
End With
Sheets("Purchase").Select
Range("L3").Select
Selection.ClearContents
'
End Sub

mohanvijay
11-02-2011, 07:29 AM
Try this




Dim In_Path As String
Dim i As Long
Dim O_FYS As Object, O_Fol As Object, O_File As Object

In_Path = "C:\abc\bcf\abc\"
i = 0

Set O_FYS = CreateObject("Scripting.FileSystemObject")
Set O_Fol = O_FYS.getfolder(In_Path)

For Each O_File In O_Fol.Files

i = i + 1
Cells(i, 1).Value = O_File.Name

Next

Set O_File = Nothing
Set O_Fol = Nothing
Set O_FYS = Nothing