Solved

File Search Criteria

Posted on 2004-03-24
9
786 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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 

Author Comment

by:Corbinap
Comment Utility
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
Comment Utility
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
Comment Utility
Then just try my sample code :-)

Nic;o)
0
 
LVL 119

Expert Comment

by:Rey Obrero
Comment Utility

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
Comment Utility
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

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.

Join & Write a Comment

When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
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…
Familiarize people with the process of utilizing SQL Server functions 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 Microsoft Ac…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.

762 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

11 Experts available now in Live!

Get 1:1 Help Now