InStr multiple filter

[bumbum2812]

Hi Experts,

I'm having below code to take a list of filename to my sheet, i want to choose file extension ".PDF" which is form the right so i use the InStrRev then want to take specific name form the left so i use InStr but it did not return what i want. thanks in advance.

Sub SearchFiles()
    Dim oFSO        As Object
    Dim oFolder     As Object
    Dim oFile       As Object
    Dim n           As Long
    Dim folderPath  As String
    Dim lr          As Long
    Dim wb          As Workbook: Set wb = ThisWorkbook
    Dim ws          As Worksheet
    Set ws = wb.Sheets("Ref")
    lr = ws.Range("C" & Rows.Count).End(xlUp).Row
    folderPath = Application.ActiveWorkbook.Path
    'MsgBox folderPath
    ws.Range("C2:C" & lr).ClearContents
    Set oFSO = CreateObject("Scripting.FileSystemObject")
    Set oFolder = oFSO.getfolder(folderPath)
    For Each oFile In oFSO.getfolder(folderPath).Files
        If InStrRev(oFile, ".pdf") Or InStrRev(oFile, ".PDF") Then n = n + 1
        If InStr(oFile, "SGN_") Or InStr(oFile, "HAN_") Or InStr(oFile, "MAWB") Or InStr(oFile, "MANIFEST") Or InStr(oFile, "mawb") Or InStr(oFile, "Manifest") Then
            n = n + 1
            Sheets(1).Cells(n + 1, 3).Value = Array(oFile.Name)
        End If
    Next
    
End Sub

 

[jasonb75]

it did not return what i want.

That could mean any one of many things, your code is riddled with inconsistency and uncertainty.

InStrRev serves no purpose in the way that you have used it, it is only looking for .pdf anywhere in the filepath rather than at the end of the name like you think.
Note that I said filepath above and not name, by using oFile instead of oFile.Name you are looking at the folder names as well, if any of the strings are found in the folder or subfolder names then all files will be returned, not just the ones you want.
In addition to the above, your If's are set out in a way that all file types are being searched for the strings in the second line, not just pdf types.
n is being increased by 1 for every pdf found that doesn't have a matching name or any non pdf file with a matching name and increased by 2 for every pdf with a matching name. It is then further increased when you add the name to the worksheet.
You have set your sheet name to the variable ws yet in the last part of the code you have used Sheets(1) which may cause data to be written back to the incorrect sheet.
I have no idea why you're trying to use Array(oFile.Name), if you're lucky it will do nothing but most likely it will case an error.

Trying to clean up the carnage in the bottom part of your code, I think that this might be what you want but with no information in your post to tell us what you actually need this is purely guesswork.

    For Each oFile In oFolder.Files
        With oFile
            If Right(LCase(.Name), 3) = ".pdf" Then
                If InStr(.Name, "SGN_") Or InStr(.Name, "HAN_") Or InStr(LCase(.Name), "mawb") Or InStr(LCase(.Name), "Manifest") Then
                    n = n + 1
                        ws.Cells(n, 3).Value = .Name
                End If
            End If
        End With
    Next

 

[Yongle]

@jasonb75
There is a typo in your code

If Right(LCase(.Name), 4) = ".pdf" Then

 

[jasonb75]

Thanks @Yongle I forgot to include the . in the character count.

 

[Yongle]

To test that the file names BEGIN with those characters and allow for both upper and lower case, this method can be used

 If InStr(1, oFile.Name, "SGN_", vbTextCompare) = 1 Then

Loop like this (xName must be declared as a variant)

       Dim xName As Variant
            For Each xName In Array("SGN_", "HAN_", "MAWB", "MANIFEST")
                If InStr(1, oFile.Name, xName, vbTextCompare) = 1 Then
                    n = n + 1
                    ws.Cells(n + 1, 3).Value = oFile.Name
                End If
            Next xName

 

[jasonb75]

I was thinking of using Like with Option Compare Text for a similar method @Yongle but decided against it (although I think your way might be neater).

As always there is a lot open to interpretation but given that checking both upper and lower case for pdf, mawb and manifest was the only thing in the code that resembled any kind of consistency I did assume that exclusion of sgn_ and han_ in lower case was deliberate.

 

[Peter_SSs]

Another approach that you may wish to try, testing each file name for the partial names all at once.

Dim RX As Object
Set RX = CreateObject("VBScript.RegExp")
RX.IgnoreCase = True
RX.Pattern = ".*(SGN_|HAN_|MAWB|Manifest).*\.pdf$"
For Each oFile In oFolder.Files
  If RX.Test(oFile.Name) Then
    n = n + 1
    ws.Cells(n, 3).Value = oFile.Name
  End If
Next oFile