使用FSO搜索列范围中的Excel文件

Question

在下面的代码中，我试图实现的是，代码搜索在给定路径中的列范围F中输入的文件，即“D：\Checksheets”。我仍然在学习FSO，并将非常感谢任何帮助。

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

多亏了亚历克斯，我才能让代码正常工作。如果有人有类似的问题，下面是代码：

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

Answer 2

FSO有一个内置的FileExists方法：

...
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

您可以完全删除FSO代码，并用内置的FileExists函数替换Dir$调用：

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