How to know the Availability of a file in the directory?

[aaromic2000]

I have shared drive mapped to directory "M:\\". I have lot of folders and and subfolders in it.

I want to run a macro which would search the folder name (Column A) in the directory and find the availability of the "Part of the File Name"(Column C)) and return Available/Not Available in Column D.

<TABLE style="WIDTH: 384pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=512><COLGROUP><COL style="WIDTH: 65pt; mso-width-source: userset; mso-width-alt: 3181" width=87><COL style="WIDTH: 104pt; mso-width-source: userset; mso-width-alt: 5046" width=138><COL style="WIDTH: 105pt; mso-width-source: userset; mso-width-alt: 5120" width=140><COL style="WIDTH: 110pt; mso-width-source: userset; mso-width-alt: 5376" width=147><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 65pt; HEIGHT: 15pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=20 width=87>Folder Name</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 104pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 width=138>Region</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 105pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 width=140>Part of the File Name</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 110pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 width=147>Availability</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 height=20>6986</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67>Europe</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67>6986 - Europe</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67> </TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 height=20>7142</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67>Asia</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67>7142 - Asia</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67> </TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 height=20>4387</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67>Europe</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67>4387 - Europe</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67> </TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 height=20>9841</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67>Asia</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67>9841 - Asia</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67> </TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 height=20>3681</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67>Africa</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67>3681 - Africa</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67> </TD></TR></TBODY></TABLE>

Can any one help me in this??? It's a urgent requirement...

 

[Ruddles]

When you said "the code does not enter the While loop", did you mean the loop which While FolderList.Count > 0 or the one which starts While strFileName <> ""?

Do you actually have a Z: drive? Does the non-working code run okay if you try it against your M: drive (which worked before) or your C: drive with some dummy files in it?

Throw away the old code and use this version which checks for a non-existent or invalid start folder:-

[FONT=Fixedsys]Option Explicit[/FONT]
[FONT=Fixedsys]Option Compare Text[/FONT]
 
[FONT=Fixedsys]Public Sub ConfirmFilesExist_v3()[/FONT]
 
[FONT=Fixedsys] Const StartFolder As String = "C:\Temp"[/FONT]
[FONT=Fixedsys] Dim StartFolderAdjusted As String[/FONT]
 
[FONT=Fixedsys] Dim ws As Worksheet[/FONT]
[FONT=Fixedsys] Dim FolderList As New Collection[/FONT]
[FONT=Fixedsys] Dim strFileName As String[/FONT]
[FONT=Fixedsys] Dim strFolderName As String[/FONT]
[FONT=Fixedsys] Dim iLastRow As Long[/FONT]
[FONT=Fixedsys] Dim iRow As Long[/FONT]
[FONT=Fixedsys] Dim iFound As Long[/FONT]
[FONT=Fixedsys] Dim strMatchFileName As String[/FONT]
[FONT=Fixedsys] Dim strMatchFolderName As String[/FONT]
[FONT=Fixedsys] Dim dtStart As Date[/FONT]
 
[FONT=Fixedsys] Set ws = ThisWorkbook.Sheets(1)[/FONT]
[FONT=Fixedsys] iLastRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row[/FONT]
[FONT=Fixedsys] ws.Range("D2:E" & CStr(iLastRow)).ClearContents[/FONT]
[FONT=Fixedsys] dtStart = Now()[/FONT]
 
[FONT=Fixedsys] StartFolderAdjusted = StartFolder & "\"[/FONT]
[FONT=Fixedsys] Do While (Right(StartFolderAdjusted, 2) = "\\")[/FONT]
[FONT=Fixedsys]   StartFolderAdjusted = Left(StartFolderAdjusted, Len(StartFolderAdjusted) - 1)[/FONT]
[FONT=Fixedsys] Loop[/FONT]
 
[FONT=Fixedsys] strFolderName = Dir(StartFolderAdjusted, vbDirectory)[/FONT]
[FONT=Fixedsys] If StartFolderAdjusted = "\" Or strFolderName = "" Then[/FONT]
[FONT=Fixedsys]   MsgBox vbCrLf _[/FONT]
[FONT=Fixedsys]      & "Invalid or non-existent start folder!" _[/FONT]
[FONT=Fixedsys]      & Space(10) & vbCrLf & vbCrLf _[/FONT]
[FONT=Fixedsys]      & "Unable to start at " & StartFolderAdjusted, _[/FONT]
[FONT=Fixedsys]      vbOKOnly + vbExclamation, "Aaromic2000 File Checker v3"[/FONT]
[FONT=Fixedsys]   Exit Sub[/FONT]
[FONT=Fixedsys] End If[/FONT]
 
[FONT=Fixedsys] FolderList.Add StartFolderAdjusted[/FONT]
 
[FONT=Fixedsys] While FolderList.Count > 0[/FONT]
[FONT=Fixedsys]   strFolderName = FolderList.Item(1)[/FONT]
[FONT=Fixedsys]   FolderList.Remove 1[/FONT]
[FONT=Fixedsys]   strFileName = Dir(strFolderName, vbDirectory)[/FONT]
[FONT=Fixedsys]   While strFileName <> ""[/FONT]
[FONT=Fixedsys]     If strFileName <> "." And strFileName <> ".." Then[/FONT]
[FONT=Fixedsys]       If GetAttr(strFolderName & strFileName) = vbDirectory Then[/FONT]
[FONT=Fixedsys]         FolderList.Add strFolderName & strFileName & "\"[/FONT]
[FONT=Fixedsys]       Else[/FONT]
[FONT=Fixedsys]         For iRow = 2 To iLastRow[/FONT]
[FONT=Fixedsys]           strMatchFolderName = "\" & ws.Cells(iRow, 1) & "\"[/FONT]
[FONT=Fixedsys]           If IsEmpty(ws.Cells(iRow, 4)) Then[/FONT]
[FONT=Fixedsys]             strMatchFileName = ws.Cells(iRow, 3)[/FONT]
[FONT=Fixedsys]             If InStr(strFileName, strMatchFileName) > 0 Then[/FONT]
[FONT=Fixedsys]               If InStr(strFolderName, strMatchFolderName) > 0 Then[/FONT]
[FONT=Fixedsys]                 ws.Cells(iRow, 5) = strFolderName & strFileName[/FONT]
[FONT=Fixedsys]                 ws.Cells(iRow, 4) = "yes"[/FONT]
[FONT=Fixedsys]               End If[/FONT]
[FONT=Fixedsys]             End If[/FONT]
[FONT=Fixedsys]           End If[/FONT]
[FONT=Fixedsys]         Next iRow[/FONT]
[FONT=Fixedsys]       End If[/FONT]
[FONT=Fixedsys]     End If[/FONT]
[FONT=Fixedsys]     strFileName = Dir[/FONT]
[FONT=Fixedsys]   Wend[/FONT]
[FONT=Fixedsys] Wend[/FONT]
 
[FONT=Fixedsys] iFound = 0[/FONT]
[FONT=Fixedsys] For iRow = 2 To iLastRow[/FONT]
[FONT=Fixedsys]   If IsEmpty(ws.Cells(iRow, 4)) Then[/FONT]
[FONT=Fixedsys]     ws.Cells(iRow, 4) = "no"[/FONT]
[FONT=Fixedsys]   Else[/FONT]
[FONT=Fixedsys]     iFound = iFound + 1[/FONT]
[FONT=Fixedsys]   End If[/FONT]
[FONT=Fixedsys] Next iRow[/FONT]
 
[FONT=Fixedsys] MsgBox vbCrLf _[/FONT]
[FONT=Fixedsys]      & Space(5) & CStr(iLastRow - 1) & " file name" & IIf(iLastRow = 2, "", "s") _[/FONT]
[FONT=Fixedsys]      & " checked" & Space(10) & vbCrLf & vbCrLf _[/FONT]
[FONT=Fixedsys]      & Space(5) & CStr(iFound) & " file" & IIf(iFound = 1, "", "s") _[/FONT]
[FONT=Fixedsys]      & " found" & Space(10) & vbCrLf & vbCrLf _[/FONT]
[FONT=Fixedsys]      & Space(5) & "Run time: " & Format(Now() - dtStart, "hh:nn:ss") & Space(10), _[/FONT]
[FONT=Fixedsys]      vbOKOnly + vbInformation, "Aaromic2000 File Checker v3"[/FONT]
 
[FONT=Fixedsys]End Sub[/FONT]