Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Creating a list of found files
Set fs = Application.FileSearch
With fs .LookIn = "C:\My Documents" .FileName = "*.doc" If .Execute(SortBy:=msoSortbyFileName, _ SortOrder:=msoSortOrderAscending) 0 Then MsgBox "There were " & .FoundFiles.Count & _ " file(s) found." For i = 1 To .FoundFiles.Count ActiveSheet.Cells(i,"A").Value = FoundFiles(i) Next i Else MsgBox "There were no files found." End If End With Should work. your .Range is the problem. the scope of the With statement when you issue that is a filesearch object which doesn't have a range property. If you remove the dot, it would probably work, but then you would write each name to A1, so only the last file name would remain. -- Regards, Tom Ogilvy "Doug" wrote in message ... I am trying to create a list of files found with the filesearch object. I can find the files and list them but I want to write each file name to a cell in the active workbook. Here's the code from MS to show the files found: Set fs = Application.FileSearch With fs .LookIn = "C:\My Documents" .FileName = "*.doc" If .Execute(SortBy:=msoSortbyFileName, _ SortOrder:=msoSortOrderAscending) 0 Then MsgBox "There were " & .FoundFiles.Count & _ " file(s) found." For i = 1 To .FoundFiles.Count MsgBox .FoundFiles(i) Next i Else MsgBox "There were no files found." End If End With Now I want to take that list and one at a time drop the found filenames into column A starting at row 1. When I insert .Range("A1") = .FoundFiles(i) it won't run. I've tried several different options but nothing seems to work. Any suggestions or help would be greatly appreciated. Doug |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Creating a list of found files
I have these lines at the end of my file search:
Range("B:B").Clear Set rngFiles = Range("B15") rngFiles.Offset(0, 0) = "Found " & .FoundFiles.Count & " containing " & .TextOrProperty For lngIndex = 1 To .FoundFiles.Count ActiveSheet.Hyperlinks.Add Anchor:=rngFiles.Offset(lngIndex, 0), Address:=.FoundFiles.Item(lngIndex) Next This writes the names of the documents into Col. B beginning in B15 as hyperlinks. (I have it set up earlier to give me the whole name including file path.) HTH Ed "Tom Ogilvy" wrote in message ... Set fs = Application.FileSearch With fs .LookIn = "C:\My Documents" .FileName = "*.doc" If .Execute(SortBy:=msoSortbyFileName, _ SortOrder:=msoSortOrderAscending) 0 Then MsgBox "There were " & .FoundFiles.Count & _ " file(s) found." For i = 1 To .FoundFiles.Count ActiveSheet.Cells(i,"A").Value = FoundFiles(i) Next i Else MsgBox "There were no files found." End If End With Should work. your .Range is the problem. the scope of the With statement when you issue that is a filesearch object which doesn't have a range property. If you remove the dot, it would probably work, but then you would write each name to A1, so only the last file name would remain. -- Regards, Tom Ogilvy "Doug" wrote in message ... I am trying to create a list of files found with the filesearch object. I can find the files and list them but I want to write each file name to a cell in the active workbook. Here's the code from MS to show the files found: Set fs = Application.FileSearch With fs .LookIn = "C:\My Documents" .FileName = "*.doc" If .Execute(SortBy:=msoSortbyFileName, _ SortOrder:=msoSortOrderAscending) 0 Then MsgBox "There were " & .FoundFiles.Count & _ " file(s) found." For i = 1 To .FoundFiles.Count MsgBox .FoundFiles(i) Next i Else MsgBox "There were no files found." End If End With Now I want to take that list and one at a time drop the found filenames into column A starting at row 1. When I insert .Range("A1") = .FoundFiles(i) it won't run. I've tried several different options but nothing seems to work. Any suggestions or help would be greatly appreciated. Doug |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
try to open excel files from explorer, 'file not found' | Excel Discussion (Misc queries) | |||
Excell Dropdown List. Display alternate text than found in list. | Excel Discussion (Misc queries) | |||
creating a reconciling list of items not matched between two files | Excel Worksheet Functions | |||
Creating a List of the files in a folder in excel | Excel Discussion (Misc queries) | |||
Linking files "File Not Found" | Excel Discussion (Misc queries) |