CODEWITHSUNDEEP
excelvbafilesystemobject
user4017711
user4017711

Reputation:

Search for Excel Files in a Column Range using FSO

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

Upvotes: 0

Views: 361

Answers (2)

user4017711
user4017711

Reputation:

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

Upvotes: 0

Alex K.
Alex K.

Reputation: 175776

The FSO has a built in FileExists method:

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

Dim sht As Worksheet, cell As Range
Set sht = Sheets("Sheet1")

Do
    Set cell = sht.Cells(row, col)

    If cell.Value = "END" Then Exit Do

    If FSO.FileExists(path & cell.Value) Then
        MsgBox "done " & cell.Value
    End If

    row = row + 1
Loop

You can remove the FSO code entirely and replace the FileExists call with the built-in Dir$ function:

If Len(Dir$(path & cell.Value)) Then

Upvotes: 1

Related Questions