Link to home
Start Free TrialLog in
Avatar of Corbinap
Corbinap

asked on

File Search Criteria

I need help adding to the code below additional search criteria to look for only the Max file going by number order as below and with verbage after the number. I can't use lastmodified. Thanks in advance.

ABC0000555_1_1_ALL.xls
ABC0000555_1_2_ALL.xls
ABC0000555_1_3_ALL.xls(out of these files (I want this one)


This is the code I have so far:

Function FileSearch()
Dim i As Integer
Dim fs As Object
Set fs = Application.FileSearch
With fs
.LookIn = "C:\Documents and Settings\gaw2\My Documents\"
.fileName = "*.xls"

'.SearchSubFolders = True 'ADD THIS LINE to search the subfolders.
If .Execute(SortBy:=msoSortByFileName, _
SortOrder:=msoSortOrderAscending) > 0 Then
MsgBox "There were " & .FoundFiles.Count & _
" file(s) found."
For i = 1 To .FoundFiles.Count

'Debug.Print .FoundFiles(i)
' MsgBox .FoundFiles(i)
Next i

Else
MsgBox "There were no files found."
End If
End With

End Function

This is some code I think I can use but not sure how to incorporate into the code above or how it works:

brkFile = "": brkTest = "": brkMax = 0
For i = 1 To .FoundFiles.Count
  aTmp = Split(.FoundFiles(i), "_")
  If UBound(aTmp) = 3 Then
    curTest = aTmp(0) & "_" & aTmp(1)
    If curTest <> brkTest And i > 1
        MsgBox brkFile
        ' Your stuff here
        brkTest = curTest: brkMax = 0
    End If
    If CInt(aTmp(2)) > brkMax Then
      brkFile = .FoundFiles(i)
      brkMax = CInt(aTmp(2))
    End If
  End If
Next i
If brkMax > 0 Then
  MsgBox brkFile
  ' Your stuff here
End If
Avatar of nico5038
nico5038
Flag of Netherlands image

When you have always the same string, you can easily use in a loop the complete name like:

Dim strMaxName as string

strMaxName = .foundfiles(1).name

For i = 1 To .FoundFiles.Count
  IF strMaxName < .foundfiles(i).name
     strMaxName = .foundfiles(i).name
  End If
Next i

Nic;o)
Avatar of heer2351
heer2351

Could you give some more details.

>>to look for only the Max file going by number order

is this always the number after the second underscore?

Do you only want one file, multiple files, i.e. what do you expect for the following files:

ABC0000555_1_1_ALL.xls
ABC0000555_1_2_ALL.xls
ABC0000555_1_3_ALL.xls
ABC0000555_2_1_ALL.xls
ABC0000555_2_2_ALL.xls
ABC0000675_1_1_ALL.xls
ABC0000675_1_2_ALL.xls
ABC0000675_1_3_ALL.xls
ABC0000675_1_4_ALL.xls
Avatar of rockiroads
if the format of the files held in the directory is always the same then your on the right track when using the split function

Ive assumed you have defined  aTmp as follows

   dim aTmp() as string

define max number held
   dim iMax as integer

then within the loop

   iMax = 0
   For i = 1 To .FoundFiles.Count

this is right

    aTmp = split (.FoundFiles(i), "_")               'Takes a string and splits it using the _ as the delimiter, values go in string array

    'Check format, only accept if 3 underscores exist
    if ubound(sFiles) = 3 then

         debug.print val(nz(aTmp(2),"0"))              'aTmp(2) is the the max number
                                                                      'NZ is a safety precaution, if aTmp(2) is null then use 0
                                                                      'Val wil convert any text to 0

         if val(nz(aTmp(2),"0")) > imax then imax = val(nz(aTmp(2),"0"))

    end if
next


and iMax will then hold the highest value held in after the second underscore
   

oops, the example returned the highest number
should also set the filename at the point of setting imax

i.e.

if val(nz(aTmp(2),"0")) > iMax then
       iMax = val(nz(aTmp(2),"0"))
       sHighestFile = .FoundFiles(i)
end if
Avatar of Corbinap

ASKER

heer2351 > the files will be like the ones below and will be the same string except 1_1 format is the latest version
so I would need 2_2 to be the one found and imported into the database.


ABC0000555_1_1_ALL.xls
ABC0000555_1_2_ALL.xls
ABC0000555_1_3_ALL.xls
ABC0000555_2_1_ALL.xls
ABC0000555_2_2_ALL.xls

you then need two integer variables

e.g.

iMax1, iMax2

    if ubound(sFiles) = 3 then

         if val(nz(aTmp(1),"0")) > iMax1 then
              iMax1 = val(nz(aTmp(1),"0"))

              if val(nz(aTmp(2),"0")) > iMax2 then
                     iMax2 = val(nz(aTmp(2),"0"))
                     sFoundFile = sFile
              endif
          endif
    endif



Then just try my sample code :-)

Nic;o)

Just change Ascending to Descending and first one will be the file you want which will be
   .FoundFiles(1)

If .Execute(SortBy:=msoSortByFileName, _
SortOrder:=msoSortOrderDescending) > 0 Then   'Change Ascending to Descending
MsgBox "There were " & .FoundFiles.Count & _
" file(s) found."
For i = 1 To .FoundFiles.Count
ASKER CERTIFIED SOLUTION
Avatar of Rey Obrero (Capricorn1)
Rey Obrero (Capricorn1)
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial