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]

What do you mean by "availability"? Do you mean if the file exists?

And by "Part of the File Name", do you mean any file with that characters string anywhere in its name, or that begins with those characters? Regardless of the file extension?

 

[aaromic2000]

Yes. It means "whether the file exists"

Yes it is the character string anywhere in the file name..

Can you help me..?

 

[Ruddles]

Yes... give me a few moments...

 

[aaromic2000]

Sure Ruddles... Will wait till you reply... Thanks...

 

[Ruddles]

Maybe something like this:-

[FONT=Fixedsys]Option Explicit[/FONT]
[FONT=Fixedsys][/FONT] 
[FONT=Fixedsys]Public Sub ConfirmFilesExist()[/FONT]
[FONT=Fixedsys][/FONT] 
[FONT=Fixedsys]  Const Source_Drive As String = "M:"
  
  Dim ws As Worksheet
  Dim iLastRow As Long
  Dim iRow As Long
  Dim iFound As Long
  
  Dim ThisFolder As String
  Dim ThisFileMask As String
  Dim ThisFileName As String
  
  Set ws = ThisWorkbook.Sheets(1)
  iLastRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row
  ws.Range("D2:D" & CStr(iLastRow)).ClearContents
  
  iFound = 0
  For iRow = 2 To iLastRow
    ThisFolder = ws.Cells(iRow, 1)
    ThisFileName = Dir(Source_Drive & "\" & ThisFolder & "\" & "*" & ThisFileMask & "*.*")
    If ThisFileName <> "" Then
      ws.Cells(iRow, 4) = "yes"
      iFound = iFound + 1
    Else
      ws.Cells(iRow, 4) = "no"
    End If
  Next iRow
  
  MsgBox vbCrLf _
       & Space(5) & CStr(iLastRow - 1) & " file name" & IIf(iLastRow = 2, "", "s") & " checked" & Space(10) & vbCrLf & vbCrLf _
       & Space(5) & CStr(iFound) & " file" & IIf(iFound = 1, "", "s") & "  found" & Space(10), _
       vbOKOnly + vbInformation, "Aaromic2000 Fle Checker"
         
End Sub
[/FONT]

Switch to the VBA editor by pressing Alt-F11. Go Insert > Module and paste this code into the code window for the new module. Press F5 to run the code.

Does that work?

You can add a command button to the worksheet to run the code. Let me know if you need guidance on doing that.

 

[aaromic2000]

Hello Ruddles, I'm surely missing something... The result is always "No" in the D column... Please note that the column A is the list of Subfolders. I think the code returns "No" because it's checking only the main folders...

Please assist...

 

[Ruddles]

The files you showed in your worksheet sample above - please can you show me the full path and file names for them?

For example, file *6986 - Europe*.* - which folder is it actually in?

 

[aaromic2000]

M:\\123.45.67.898\Logo\Dates\ is source path

And the File I'm searching will look like "Aaromic 2000 - 6986 - Europe - 148"

 

[Ruddles]

So there's no Folder Name called 6986? Do I just ignore column A of the worksheet?