Ytukyg

I have a question regarding some VBA coding. I have an Excel spreadsheet with rows that each contain an identification number (e.g. ABC0123456ABC). I also have a folder with subfolders that have a name similar to the identification number from the Excel file. However, some of these subfolders contain more files (all pdfs). I would like to know which folders contain files (and which do not).

Thus, I would like to search all folders that contain files with the names that are taken from the excel file.

How would I best do this?

I think you could modify this to your needs;

If i have a spreadsheet like this (data in range B1:B3);

where each row represents a sub-folder and you want to know if you have a file in each of these sub-folders

Then you can use this code. It will write whether there is a file which contains the same name as shown in column B to column C.

 Public Sub FindFiles()



Dim myParentFolderLoc As String

Dim curValue As String



Dim myCell As Range

Dim myRange As Range

Dim outputRange As Range



    ' Change these to your range/Folder Location;

    Set myRange = Sheet1.Range("B1:B3")

    Set outputRange = Sheet1.Range("B1:C3") ' note the inclusion of the column to which i am writing

    myParentFolderLoc = "C:Example Folder"



    ' Loop through your excel cells

    For Each myCell In myRange.Cells



        If isFileInFolder(myParentFolderLoc, myCell.Value) Then

            myCell.Offset(0, 1).Value = "Files Exists"

        Else

            myCell.Offset(0, 1).Value = "No Files Exists"

        End If



    Next myCell



    ' Export to Txt

    ExportRangeToTxt outputRange



End Sub





' Loop through the folder and see if a file contains the string

Private Function isFileInFolder(folderLocation As String, folderName As String) As Boolean



   Dim i As Integer

   i = 0

   file = Dir(folderLocation & folderName & "")



   While (file <> "")

     i = i + 1

     file = Dir

  Wend



    If i > 0 Then

        isFileInFolder = True

    Else

        isFileInFolder = False

    End If





End Function





Private Sub ExportRangeToTxt(myRange As Range)



Dim myFile As String

Dim rng As Range

Dim cellValue As Variant

Dim i As Integer

Dim j As Integer



myFile = Application.DefaultFilePath & "output.txt"

Set rng = myRange



Open myFile For Output As #1



For i = 1 To rng.Rows.Count

    For j = 1 To rng.Columns.Count

        cellValue = rng.Cells(i, j).Value



        If j = rng.Columns.Count Then

            Write #1, cellValue

        Else

            Write #1, cellValue,

        End If

  Next j

Next i



Close #1



MsgBox "Text Export Complete - Check the file at: " & myFile



End Sub

This will export a file to the default file path of the application - the message box will tell you where this is.

Remember to update the ranges as shown for the example to include all your sub-folders. I could do this automatically but i dont want to make any assumptions having not seen your data.