[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 845
  • Last Modified:

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
0
Corbinap
Asked:
Corbinap
  • 3
  • 2
  • 2
  • +2
1 Solution
 
nico5038Commented:
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)
0
 
heer2351Commented:
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
0
 
rockiroadsCommented:
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
   

0
Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

 
rockiroadsCommented:
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
0
 
CorbinapAuthor Commented:
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

0
 
rockiroadsCommented:
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



0
 
nico5038Commented:
Then just try my sample code :-)

Nic;o)
0
 
Rey Obrero (Capricorn1)Commented:

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
0
 
Rey Obrero (Capricorn1)Commented:
THESE CODES WILL IMPORT {ABC0000555_2_2_ALL.xls}  FORM THE FILES BELOW AND WILL CREATE
a TABLE NAME  ABC0000555_2_2_ALL

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

 OR ANY FILE WITH HIGHER SUFFIX


Private Sub cmdGetExcelFile_Click()
Dim strFileName As String, sTableName As String, strPath As String
Dim i As Integer
Dim fs As Object
Set fs = Application.FileSearch
With fs
    .LookIn = "C:\Documents and Settings\gaw2\My Documents\"
    .fileName = "*.xls"
    If .Execute(SortBy:=msoSortbyFileName, _
    SortOrder:=msoSortOrderDescending) > 0 Then
        For i = 1 To .FoundFiles.Count
        Next i

strPath = .FoundFiles(1)
strFileName = Dir(strPath)
sTableName = Left$([strFileName], InStr(1, [strFileName], ".") - 1)
         MsgBox "There were " & .FoundFiles.Count & _
            " file(s) found. And you want to Import " & strFileName
 
   
   DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, _
            "" & sTableName & "", strPath, True
   
    Else
        MsgBox "There were no files found."
    End If
End With

End Sub
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

  • 3
  • 2
  • 2
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now