File list - list filenames in excel

J

JRP

Hi, I have tryed this code listed below and it work, but as you see it only
list files in folder spesifyed in: .LookIn = ...

what I want to do is to find files from a dialogbox.

Any of you who can tell me how?????
--------------------------------------------------------------
Function CreateFileList(FileFilter As String, _
IncludeSubFolder As Boolean) As Variant
' returns the full filename for files matching
' the filter criteria in the current folder
Dim FileList() As String, FileCount As Long
CreateFileList = ""
Erase FileList
If FileFilter = "" Then FileFilter = "*.*" ' all files
With Application.FileSearch
.NewSearch
.LookIn = "C:\My Documents"
.Filename = FileFilter
.SearchSubFolders = IncludeSubFolder
.FileType = msoFileTypeAllFiles
If .Execute(SortBy:=msoSortByFileName, _
SortOrder:=msoSortOrderAscending) = 0 Then Exit Function
ReDim FileList(.FoundFiles.Count)
For FileCount = 1 To .FoundFiles.Count
FileList(FileCount) = .FoundFiles(FileCount)
Next FileCount
.FileType = msoFileTypeExcelWorkbooks ' reset filetypes
End With
CreateFileList = FileList
Erase FileList
End Function

Sub TestCreateFileList()
Dim FileNamesList As Variant, i As Integer
'ChDir "C:\Levende"
' activate the desired startfolder for the filesearch
FileNamesList = CreateFileList("*.*", False)
' performs the filesearch, includes any subfolders
' present the result
Range("A:A").ClearContents
For i = 1 To UBound(FileNamesList)
Cells(i + 4, 3).Formula = FileNamesList(i)
Next i
End Sub
-----------------------------------------------------------------------------
 
T

Tom Ogilvy

Sub BBB()
Dim v As Variant
v = CreateFileList("Excel Files (*.xls),*.xls")
If Not IsEmpty(v) Then
For i = LBound(v) To UBound(v)
Debug.Print v(i)
Next
End If
End Sub

Function CreateFileList(FFilter As String _
) As Variant
Dim flist As Variant
If FFilter = "" Then FFilter = "All (*.*),*.*"
Debug.Print FFilter
flist = Application.GetOpenFilename( _
FileFilter:=FFilter, _
MultiSelect:=True)
If Not IsArray(flist) Then
CreateFileList = emtpy
Else
CreateFileList = flist
End If
End Function
 
J

JRP

Hi,

Thank you for helping me.
I'm absolutly led in the right direction. But:

I got up the file selector box and was able to pic files(excel),
but they did not get listed up in the excel sheet.
Do I have to do something with the code U sendt?

I should have told u in the beginning, that I'm not a programmer, and need
full assistant. Maby I can manage it if U give me many hints.

Regards
Jim
 
J

JRP

YESYESYESYESYESYESYESYESYESYESYESYESY

Finaly I manage to list filenames in excel by selecting them from folders.
Thank you Tom Ogilvy, you led me there.

I changed a bit on the code you suggested, and BAM, the files popt up
right where I wanted them.

Try IT:

Function CreateFileList(FFilter As String _
) As Variant
Dim flist As Variant
If FFilter = "" Then FFilter = "All (*.*),*.*"
Debug.Print FFilter
flist = Application.GetOpenFilename( _
FileFilter:=FFilter, _
MultiSelect:=True)
If Not IsArray(flist) Then
CreateFileList = emtpy
Else
CreateFileList = flist
End If
End Function


Sub BBB()
Dim v As Variant, i As Integer
v = CreateFileList("All Files (*.*),*.*")
Range("A:A").ClearContents
If Not IsEmpty(v) Then
For i = 1 To UBound(v)
Cells(i + 4, 3).Formula = v(i)
Next
End If
End Sub

BEST REGARDS
HAPPY MAN
Jim P
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top