Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
File list - list filenames in excel
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 ----------------------------------------------------------------------------- |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
File list - list filenames in excel
Try Application.GetOpenFileName
-- Charles Chickering "A good example is twice the value of good advice." "JRP" wrote: 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 ----------------------------------------------------------------------------- |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
File list - list filenames in excel
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 -- Regards, Tom Ogilvy "JRP" wrote in message ... 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 ----------------------------------------------------------------------------- |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
File list - list filenames in excel
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 "Tom Ogilvy" wrote: 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 -- Regards, Tom Ogilvy "JRP" wrote in message ... 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 ----------------------------------------------------------------------------- |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
File list - list filenames in excel
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 "JRP" wrote: 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 "Tom Ogilvy" wrote: 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 -- Regards, Tom Ogilvy "JRP" wrote in message ... 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 ----------------------------------------------------------------------------- |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
List Filenames from Folder | Excel Programming | |||
List all filenames & tab names | Excel Worksheet Functions | |||
Getting a list of filenames | Excel Programming | |||
Getting a list of filenames | Excel Programming | |||
List out FileNames.xls with K4 Blank | Excel Programming |