Code to search if file exists

[Banger978]

Hello, I have this code below that searches our server for existing PDF files and reports back in a column Yes or no. It works fine, but not for all subfolders. Can this be modified instead of searching just X:\PDF Drawings\55-TILL PDF
Can it be this folder X:\PDF Drawings\ and all 15 subfolders?

Sub CheckIfFileExists()

   Dim LRow As Integer
   Dim LPath As String
   Dim LExtension As String
   Dim LContinue As Boolean

   'Initialize variables
   LContinue = True
   LRow = 2
   LPath = "X:\PDF Drawings\55-TILL PDF"
   LExtension = ".pdf"

   'Loop through all column A values until a blank cell is found
   While LContinue

      'Found a blank cell, do not continue
      If Len(Range("A" & CStr(LRow)).Value) = 0 Then
         LContinue = False

      'Check if file exists for part number
      Else
         'Place "No" in column E if the file does NOT exist
         If Len(Dir(LPath & Range("A" & CStr(LRow)).Value & LExtension)) = 0 Then
            Range("D" & CStr(LRow)).Value = "No"
            'Place "Yes" in column D if the file does exist
         Else
            Range("D" & CStr(LRow)).Value = "Yes"
            Range("D1") = "Assy Drawing Finished Yes/No"
            End If
      End If

      LRow = LRow + 1

   Wend

End Sub

 

[jmcilhinney]

Firstly, I have added an xcode tag to format your code appropriately. As you can see, it's far more readable. Please ensure that code is formatted in future.

That looks very much like the sort of VB.NET code that a VB6 developer would write. There's a number of ways that that could be written better in VB.NET. One that relates directly to your question is that, if you want to know whether a specific file exists then you should be calling the File.Exists method. If you want to know whether a file with a particular name exists in a folder or one of its subfolders, I would suggest this sort of thing:

Dim rowNumber = 2
Dim folderPath = "X:\PDF Drawings\55-TILL PDF"
Dim extension = ".pdf"

Do
    Dim fileName As String = Range("A" & rowNumber).Value

    If String.IsNullOrEmpty(fileName) Then
        Exit Do
    End If

    If Directory.GetFiles(folderPath,
                          fileName & extension,
                          SearchOption.AllDirectories).Any() Then
        'At least one file with the specified name was found in the specified folder or a subfolder.
    Else
        'No matching file was found.
    End If

    rowNumber += 1
Loop

 

[Banger978]

Hello, thanks for the fast reply.
I tried the code, but it didn't work. I might have forgotten to mention that it is in excel.
I have attached the file for you to see. My original code works for the radio button called "Is Assembly Print Finished yes/No"
but only for the folder specified, ie "X:\PDF Drawings\55-TILL PDF"
Can it be modified to search all subfolders under"X:\PDF Drawings\ instead?

Please see attachments for you to test if you are so inclined

 

[jmcilhinney]

might have forgotten to mention that it is in excel.

That might explain why it looked like VB6. Is this really VB.NET code or is it actually VBA?

 

[Banger978]

I guess it is VBA, can I get help with that here? I am a beginner, and somehow get some thing to work in Excel and Solidworks with no prior exp to Visual Basic.
Would you be able to suggest where I can get help?

 

[JohnH]

Search web for "VBA forum", this place as name suggest - VB.Net Forums - is only for VB.Net development.

 

[JuggaloBrotha]

http://www.vbforums.com/ has a good vba section.