• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

List files in directory with specific prefix only?

W

Winston618

Member
Hi, I would like to tweak the following code so it would list files only with the prefix of "NCIR*", is that possible? I am having a hard time to fit it the code, thanks!

Code: 
Sub ListFilesInFolder(ByVal xFolderName As String, ByVal xIsSubfolders As Boolean)
Dim xFileSystemObject As Object
Dim xFolder As Object
Dim xSubFolder As Object
Dim xFile As Object
Dim rowIndex As Long
Set xFileSystemObject = CreateObject("Scripting.FileSystemObject")
Set xFolder = xFileSystemObject.GetFolder(xFolderName)
rowIndex = Application.ActiveSheet.Range("A65536").End(xlUp).Row + 1
For Each xFile In xFolder.Files
  Application.ActiveSheet.Cells(rowIndex, 1).Formula = xFile.Name
  rowIndex = rowIndex + 1
Next xFile
If xIsSubfolders Then
  For Each xSubFolder In xFolder.subfolders
    ListFilesInFolder xSubFolder.Path, True
  Next xSubFolder
End If
Set xFile = Nothing
Set xFolder = Nothing
Set xFileSystemObject = Nothing
End Sub
 
Hello
Try this code
Code: 
Sub ListFilesInFolder(ByVal xFolderName As String, ByVal xIsSubfolders As Boolean)
    Dim xFileSystemObject  As Object
    Dim xFolder            As Object
    Dim xSubFolder          As Object
    Dim xFile              As Object
    Dim rowIndex            As Long

    Set xFileSystemObject = CreateObject("Scripting.FileSystemObject")
    Set xFolder = xFileSystemObject.GetFolder(xFolderName)
    rowIndex = Application.ActiveSheet.Range("A" & Rows.Count).End(xlUp).Row + 1

    For Each xFile In xFolder.Files
        If Left(xFile.Name, 4) = "NCIR" Then
            Application.ActiveSheet.Cells(rowIndex, 1).Formula = xFile.Name
            rowIndex = rowIndex + 1
        End If
    Next xFile

    If xIsSubfolders Then
        For Each xSubFolder In xFolder.subfolders
            ListFilesInFolder xSubFolder.Path, True
        Next xSubFolder
    End If

    Set xFile = Nothing
    Set xFolder = Nothing
    Set xFileSystemObject = Nothing
End Sub
 
Thank you so much for your help!
I learned a lot, thanks again!

YasserKhalil said:
Hello
Try this code
Code: 
Sub ListFilesInFolder(ByVal xFolderName As String, ByVal xIsSubfolders As Boolean)
    Dim xFileSystemObject  As Object
    Dim xFolder            As Object
    Dim xSubFolder          As Object
    Dim xFile              As Object
    Dim rowIndex            As Long

    Set xFileSystemObject = CreateObject("Scripting.FileSystemObject")
    Set xFolder = xFileSystemObject.GetFolder(xFolderName)
    rowIndex = Application.ActiveSheet.Range("A" & Rows.Count).End(xlUp).Row + 1

    For Each xFile In xFolder.Files
        If Left(xFile.Name, 4) = "NCIR" Then
            Application.ActiveSheet.Cells(rowIndex, 1).Formula = xFile.Name
            rowIndex = rowIndex + 1
        End If
    Next xFile

    If xIsSubfolders Then
        For Each xSubFolder In xFolder.subfolders
            ListFilesInFolder xSubFolder.Path, True
        Next xSubFolder
    End If

    Set xFile = Nothing
    Set xFolder = Nothing
    Set xFileSystemObject = Nothing
End Sub
Click to expand...
 
Dear Sir,

I try to run this code with following..


Code: 
Sub GetFileFolderList()

Run ListFilesInFolder("C:\Users\sganuja\Desktop\1-DAILY ORDER STATUS + INV INFORMATION", True)

End Sub

there are function or variable required error displayed...

Please help to run this code..

Regards,
Chiorag Raval
 
Try this line instead
Code: 
ListFilesInFolder "C:\Users\sganuja\Desktop\1-DAILY ORDER STATUS + INV INFORMATION", True
 
Dear sir @YasserKhalil

Code: 
Sub GetFileFolderList()

With ActiveSheet

ListFilesInFolder "C:\Users\sganuja\Desktop\1-DAILY ORDER STATUS + INV INFORMATION", True
End With
End Sub

No error ...but also nothing happen (no result) in active sheet...

Please help..

Regards,
Chirag Raval
 
Dear Sir @YasserKhalil

Code: 
 For Each xFile In xFolder.Files
        If Left(xFile.Name, 13) = "09 - DISPATCH" Then
            Application.ActiveSheet.Cells(rowIndex, 1).Formula = xFile.Name
            rowIndex = rowIndex + 1

I modify many times...but no result...
can we modify function as "Mid" instead of "Left"?

Regards,
Chirag Raval
 
I don't know what is the problem with your code exactly .. Have you checked the path and make sure it is correct?
As for me it is working well

Try put a back slash after "INV INFORMATION" like that "INV INFORMATION\"
 
Dear Sir @YasserKhalil

Thanks Now its works..I modify as full file name

Code: 
For Each xFile In xFolder.Files
        If Left(xFile.Name, 75) = "09 - DISPATCH DETAILS FOR SUITING & SHIRTING as on 01.09.2017 to 12.09.2017" Then
            Application.ActiveSheet.Cells(rowIndex, 1).Formula = xFile.Name
            rowIndex = rowIndex + 1
        End If
    Next xFile

can we modify for search for any word that as it is partial (middle) of file name?
like search for word "dispatch" is part of file name not whole file name..?

Regards,
Chirag Raval
 
May be using INSTR instead of LEFT would solve your problem
Code: 
Sub INSTR_VBA_Function()
    Debug.Print InStr("Tech on the Net", "T")      'RESULT: 1
    Debug.Print InStr(1, "Tech on the Net", "T")    'RESULT: 1
    Debug.Print InStr(1, "Tech on the Net", "t")    'RESULT: 9
    Debug.Print InStr(10, "Tech on the Net", "t")  'RESULT: 15
    Debug.Print InStr(1, "Tech on the Net", "the")  'RESULT: 9
    Debug.Print InStr(1, "Tech on the Net", "M")    'RESULT: 0

    Dim lPosition As Integer
    lPosition = InStr(10, "Tech on the Net", "t")  'RESULT: 15
End Sub
 
Dear Sir @YasserKhalil

I also used....

Code: 
For Each xFile In xFolder.Files
        If Mid(xFile.Name, 1, 100) = "*DISPATCH*" Then
            Application.ActiveSheet.Cells(rowIndex, 1).Formula = xFile.Name
            rowIndex = rowIndex + 1
        End If
    Next xFile

with "*" & without "*"...

but no-error but also nothing happened...

as per your...we must provide full name is your "INSTR" code?

hope little help..

Regards,
Chirag Raval
 
Hi !

Obviously for performance do not use FileSystemObject external library
but just internal function Dir as explained in VBA inner help
(so many samples all over the web) …

For example to scan all .xl* files on a data hard drive FSO needed 215s
and less than 20s via Dir ! (both tests without any drive cache)

For PC only and advanced developer : fastest is to use Windows API …
​
 

Start with FindFirstFile on MSDN if you know C language
or at least aware of using system libraries in VBA …
​
 
You must log in or register to reply here.
Back
Top