PDA

View Full Version : Solved: Get FileNames From Folder Not Using PathName



omnibuster
04-05-2009, 04:21 AM
:banghead:
How to get FileNames to List.
Cant use: =MID(A3;9;11) because sometimes File exist My Documets...or something else place.


Sub GetFileName()
Dim i As Long
Dim rCtr As Long
Dim sStr As String
Columns("A:A").ClearContents
sStr = ThisWorkbook.Path '"C:\XXXX" OR "C:\Documents and Settings\user\My Documents\XXXXX" OR "C:\Whatever\Whatever\Whatever\Whatever"
rCtr = 0
With Application.FileSearch
.NewSearch
.LookIn = sStr
.SearchSubFolders = True
.Filename = "*.xls"
.FileType = msoFileTypeExcelWorkbooks
If .Execute() > 0 Then
ReDim FileList(1 To .FoundFiles.Count)
For i = 1 To .FoundFiles.Count
If LCase(.FoundFiles(i)) Like LCase("*\" & .Filename) Then
'Get .FoundFiles(i).Name

rCtr = rCtr + 1
ActiveSheet.Cells(rCtr, 1).Value = .FoundFiles(i) '??? Need only Names Like XXXXX.xls Not Path C:\...\XXXX.xls
End If
Next i
Else
MsgBox "There were no files found."
End If
End With
End Sub

mdmackillop
04-05-2009, 05:04 AM
Try

ActiveSheet.Cells(rCtr, 1).Value = Split(.FoundFiles(i), "/")(UBound(Split(.FoundFiles(i), "/")))

omnibuster
04-05-2009, 05:22 AM
Big Thanks mdmackillop.

Works fine.

Zack Barresse
04-05-2009, 09:13 AM
omnibuster, fyi the FileSearch method ended with Office 2003, as it was taken out of future versions. If you're planning on moving ahead to other versions your code here will not work.

omnibuster
04-05-2009, 01:43 PM
Thanks Zack.
I find better solution (I hope).
Dim FSO As Object, fld As Object, Fil As Object
Dim SubFolderName As String
Dim i As Integer
Set FSO = CreateObject("Scripting.FileSystemObject")
Me.ListBox1.Clear 'clear previous entries
SubFolderName = ThisWorkbook.Path
Set fld = FSO.GetFolder(SubFolderName)
For Each Fil In fld.Files
i = i + 1
Me.ListBox1.AddItem Fil.Name

Next Fil

Kenneth Hobs
04-05-2009, 02:08 PM
No, that will not search subfolders without modification.

FileSearch is fine for 2003. We can show you other ways such as the fso method and others if you need it.

omnibuster
04-06-2009, 10:57 PM
Thanks Ken.
Obviously I need more help.

"For Each Fil In fld.Files"- I was thinking Fil= File.name but not. In this case
Fil= Path (C\Folder\FileName)

What Im druing to do:
FSO Look in Folder, Get Filenames & put in the ListBox1.
In Folder I have some files and 2 file (Main.xls &Example.xls) who dont need getting in ListBox.
So. How to do this:


For Each Fil In fld.Files
i = i + 1
If File.Name <> "Main.xls" Or "Sample.xls" then
Me.ListBox1.AddItem Fil.Name

Using: Get NonVisibleFileNames. Dont work.



This code get error:13,
type mismatch in: "If objFile.Name <> ("Main.xls") Or ("Sample.xls") Then"

Public Sub UserForm_Activate()
Dim objFso As FileSystemObject, objFolder As Folder, objFile As File, strSourceFolder As String, strDestFolder As String
Dim Counter As Integer, strNewFileName As String, strName As String, strExt As String, strobjFile As String
Dim lCount As Long, wbResults As Workbook, wbCodeBook As Workbook, y As Variant
Dim rCtr As Long, Filename As String, FoundFile As String
Dim sStr As String, a As Long
'On Error Resume Next
Sheets("Sheet1").Select
Set wbCodeBook = ThisWorkbook

strSourceFolder = ThisWorkbook.Path
strDestFolder = ThisWorkbook.Path
Set objFso = New FileSystemObject
Set objFolder = objFso.GetFolder(strSourceFolder)
For Each objFile In objFolder.Files

If objFile.Name <> ("Main.xls") Or ("Sample.xls") Then
Me.ListBox1.AddItem objFile.Name
End If

Next objFile
End Sub

mdmackillop
04-07-2009, 12:50 AM
If objFile.Name <> ("Main.xls") Or objFile.Name <> ("Sample.xls") Then
Me.ListBox1.AddItem objFile.Name
End If

omnibuster
04-07-2009, 04:23 AM
Thanks mdmackillop.
Litle modif...d & works fine. :)

For Each objFile In objFolder.Files
If objFile.Name <> ("Main.xls") And objFile.Name <> ("Sample.xls") Then
Me.ListBox1.AddItem objFile.Name
End If
Next objFile

If objFile.Name <> ("AABAA.xls") Or objFile.Name <> ("QQQ.xls") Then
Dont work?