Fr33dan
New Member
- Joined
- Jul 18, 2007
- Messages
- 19
I have a marco that usees Application.FileSearch to look for files in multiple locations and checks a csv file to see if see if certain other files match the conditions (the other files always have the same name but always different data). I reworked a couple of if statements to allow for 0 results from the file search but then got an End With without With error:
Code:
Sub Get_URLs()
Dim AgeFiles() As String
Dim AgeNames() As String
Application.ScreenUpdating = False
Put_Dir$ = Range("Put_Path").Address(External:=True)
ModelCode = Range("Model_Code").Value
Sheets("Data_URLs").Cells.ClearContents
Application.ScreenUpdating = False
'--- Need to filter out the non aging files ---
With Application.FileSearch
lookpath = ""
progressBar introText:="Preparing Search", percentFinished:=10, totalLength:=30
If (Range("Cluster_1").Value) Then
lookpath = lookpath & Range("Cluster_Path").Value & "Cluster1\;"
End If
If (Range("Cluster_2").Value) Then
lookpath = lookpath & Range("Cluster_Path").Value & "Cluster2\;"
End If
If (Range("Other").Value) Then
lookpath = lookpath & Range("Data_Path").Value & ";"
End If
If (Range("Model").Value) Then
lookpath = lookpath & Range("Model_Path").Value & Range("O6").Value
End If
progressBar introText:="Preparing Search", percentFinished:=20, totalLength:=30
If lookpath <> "" Then
.NewSearch
.LookIn = lookpath
.SearchSubFolders = True
'----------------------------- look -------------------
'.FileName = "*." & ModelCode 'W95 stuff
.FileName = "." & ModelCode 'XP stuff
' .FileName = "*." & ModelCode 'W95 stuff
.MatchTextExactly = False
.FileType = msoFileTypeAllFiles
progressBar introText:="Running Search", percentFinished:=30, totalLength:=30
If .Execute(SortBy:=msoSortByFileName, _
SortOrder:=msoSortOrderAscending) > 0 Then
Range("No_Aging_Files").Value = .FoundFiles.Count
Offset = 1
progressBar introText:="Fetching Results", percentFinished:=50, totalLength:=30
For i = 1 To Range("No_Aging_Files").Value
If Left$(Right$(.FoundFiles(i), 12), 8) = "COPY_OUT" Or Left$(Right$(.FoundFiles(i), 9), 5) = "REAGE" Then
Offset = Offset + 1
Range("No_Aging_Files").Value = Range("No_Aging_Files").Value - 1
Else
Range(Put_Dir$).Offset(i - Offset).Value = .FoundFiles(i)
Range(Put_Dir$).Offset(i - Offset, 2).Value = False
End If
Next i
Offset = i - Offset
progressBar introText:="Fetching Results", percentFinished:=60, totalLength:=30
End If
Count = 0
If (Range("Unarchived_Files").Value) Then
Application.ScreenUpdating = False
Workbooks.Open (Range("Archive_Data_Path").Value & "PULLINFO.CSV")
i = 1
While Not IsEmpty(Cells(i, "D"))
If (Right$(Cells(i, "D"), 3) = ModelCode) Then
ReDim Preserve AgeFiles(Count + 1)
ReDim Preserve AgeNames(Count + 1)
AgeFiles(Count) = Cells(i, "A")
AgeNames(Count) = Cells(i, "D")
Count = Count + 1
End If
i = i + 1
Wend
ActiveWorkbook.Close
For i = 1 To Count
Range("No_Aging_Files").Value = Range("No_Aging_Files").Value + 1
Range(Put_Dir$).Offset(Offset).Value = Range("Archive_Data_Path").Value & AgeFiles(i - 1)
Range(Put_Dir$).Offset(Offset, 1).Value = AgeNames(i - 1)
Range(Put_Dir$).Offset(Offset, 2).Value = False
Offset = Offset + 1
Next i
End If
If .Count <> 0 And Count <> 0 Then
Range("Current_Aging_File").Value = 1
progressBar introText:="Loading File", percentFinished:=80, totalLength:=30
Call Load_and_Graph
Else
MsgBox "There were no files found."
Range("No_Aging_Files").Value = .FoundFiles.Count
Range("Current_Aging_File").Value = 0
End If
End With
progressBar introText:="Finished", percentFinished:=100, totalLength:=30
Application.StatusBar = False
End Sub