Solved

File Search Criteria

Posted on 2004-03-24
9
823 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Back Up Your Microsoft Windows Server®

Back up 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.

 
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
 

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 120

Expert Comment

by:Rey Obrero (Capricorn1)
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 120

Accepted Solution

by:
Rey Obrero (Capricorn1) 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

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

732 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