Search for Excel Files in a Column Range using FSO

Question

In the code below what I am trying to achieve is that the code searches for the files that are entered in column range F in the given path which is "D:\Checksheets\". I am still learning the FSO and would greatly appreciate any help.

Sub Test()

Dim FSO As Object
Dim FSO_Folder As Object
Dim FSO_file As Object

Dim path As String


Dim sheetref As String
Dim nextform As String
Dim row As Integer
Dim col As Integer

row = 8
col = 6

sheetref = Sheets("Sheet1").Cells(row, col)

'nextform = sheetref

path = "D:\Checksheets\"

Do Until Sheets("Sheet1").Cells(row, col) = "END"

    Set FSO = CreateObject("Scripting.FileSystemObject")
    Set FSO_Folder = FSO.GetFolder(path)

    For Each FSO_file In FSO_Folder.Files
        If FSO_file.Name = sheetref Then

        MsgBox "done" & path
    Else

    End If
    row = row + 1
    Next

Loop

End Sub

Answer 1

Thanks to Alex I was able to get the code working. In case someone has similar issue, below is the code:

Sub test()
Set FSO = CreateObject("Scripting.FileSystemObject")

Dim sht As Worksheet, cell As Range
Dim row As Integer
Dim col As Integer
Dim path As String

path = "D:\Checksheets\"

row = 1
col = 6
Set sht = Sheets("Sheet1")

Do

    Set cell = sht.Cells(row, col)

    If cell.Value = "END" Then Exit Do

    If cell.Value <> "" Then    ' checks for any empty cells

    FSO.FileExists (path)

        MsgBox "file exists"

    Else

    End If



    row = row + 1
Loop

End Sub