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:=msoSortBy FileName, _
SortOrder:=msoSortOrderAsc ending) > 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
ABC0000555_1_1_ALL.xls
ABC0000555_1_2_ALL.xls
ABC0000555_1_3_ALL.xls(out
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:=msoSortBy
SortOrder:=msoSortOrderAsc
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
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
>>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
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
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
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
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
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
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)
Nic;o)
Just change Ascending to Descending and first one will be the file you want which will be
.FoundFiles(1)
If .Execute(SortBy:=msoSortBy
SortOrder:=msoSortOrderDes
MsgBox "There were " & .FoundFiles.Count & _
" file(s) found."
For i = 1 To .FoundFiles.Count
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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)