Solved

File Search Criteria

Posted on 2004-03-24
9
797 Views
Last Modified: 2008-02-01
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
Comment
Question by:Corbinap
  • 3
  • 2
  • 2
  • +2
9 Comments
 
LVL 54

Expert Comment

by:nico5038
ID: 10672459
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
 
LVL 23

Expert Comment

by:heer2351
ID: 10672462
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
 
LVL 65

Expert Comment

by:rockiroads
ID: 10675854
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
 
LVL 65

Expert Comment

by:rockiroads
ID: 10675866
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
Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 

Author Comment

by:Corbinap
ID: 10677703
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
 
LVL 65

Expert Comment

by:rockiroads
ID: 10677756
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
 
LVL 54

Expert Comment

by:nico5038
ID: 10678445
Then just try my sample code :-)

Nic;o)
0
 
LVL 119

Expert Comment

by:Rey Obrero
ID: 10681539

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
 
LVL 119

Accepted Solution

by:
Rey Obrero earned 500 total points
ID: 10682305
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

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

It took me quite some time to sort out all the different properties of combo and list boxes available from Visual Basic at run-time. Not that the documentation is lacking: the help pages are quite thorough and well written. The problem was rather wh…
Introduction When developing Access applications, often we need to know whether an object exists.  This article presents a quick and reliable routine to determine if an object exists without that object being opened. If you wanted to inspect/ite…
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

920 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now