使用没有“DIR”的VBA或Excel测试文件是否存在 [英] Test if file exists using VBA or Excel without "DIR"

问题描述

我正在进入文件夹目录，查看文件夹以便在每次找到特定文件时获取特定数据。

I am stepping into a directory of folders and looking into the folders to get particular data each time I find a specific file.

如果我打开文件，我正在找不到它的文件夹，我得到一个调试错误。所以很清楚，我需要测试该目录以查看该文件是否存在，如果不存在，则不尝试打开它（这很好，并不是所有的文件夹都有主题文件）。

If I go to open the file I am looking for in a folder that it is not in, I get a debug error. So clearly I need to test the directory to see if the file is there and not try to open it if it is not (that is fine, not all the folders have the subject file).

DIR功能是测试文件存在的最佳选择，但是由于我使用DIR功能来遍历文件夹，我的测试表明我无法使用DIR测试特定文件我的代码的中间，因为VBA在我穿过文件夹的地方感到困惑。

The DIR function is the hands on favorite to test a file's presence, but because I am using the DIR function to step through the folders, my testing has shown that I cannot use the DIR to test for a specific file in the middle of my code because VBA gets confused where I was in the stepping through the folders.

如何在没有使用DIR的情况下测试一个已知文件名的存在？我尝试了我发现的函数：

How can I test for the existence of a known filename without using DIR? I tried the function I found:

函数FileExists（fullFileName As String）As Boolean
FileExists = VBA.Len（VBA.Dir（fullFileName））> 0
结束功能

Function FileExists(fullFileName As String) As Boolean FileExists = VBA.Len(VBA.Dir(fullFileName)) > 0 End Function

不幸的是，我还没有想出如何将其写入我正在构建的SUB中。我发现的样本（和其他类似的），但通常不清楚有兴趣的新手需要什么。在这种情况下，不知道LEN和DIR会发生什么，为什么会在DIM语句（因为As Boolean）中需要fullfilename。我也发现，如果我尝试把这个函数放在我的子程序中，VBA就不高兴。

Unfortunately, I have not figured out how to actually write this into the SUB that I am building. The sample I found (and others similar), but the usually do not make clear what is needed for an aspiring novice. In this case don't know what would go in LEN and DIR and why the fullfilename would be needed in what looks like a DIM statement (because of the As Boolean). I also find that if I try to put this function within my Subroutine, VBA is just not happy.

我可以尝试什么工作？我只想要一个关于函数的IF语句，告诉我文件是否存在。

What can I try that will work? I just want an IF statement around a function that will tell me if the file exists or not.

推荐答案

说我们正在寻找在文件夹 C：\TestFolder 或该文件夹的子文件夹中包含幸福的文件。我们想知道：

Say we are looking for a file containing "happiness" within folder C:\TestFolder or a sub-folder of that folder. We want to know:

• 文件的名称


• 其位置

我们也希望通知您是否找不到幸福。考虑：

We also want to be informed if "happiness" cannot be found. Consider:

Dim FileIsThere As Boolean

Sub MAIN()

   Dim FileSystem As Object
   Dim TopFolder As String

   TopFolder = "C:\TestFolder"
   FileIsThere = False
   Set FileSystem = CreateObject("Scripting.FileSystemObject")

   Process FileSystem.GetFolder(TopFolder)

   If Not FileIsThere Then
        MsgBox "happiness cannot be obtained, settle for the absence of misery"
   End If
 End Sub

 Sub Process(Folder)
    Dim SubFolder
    For Each SubFolder In Folder.SubFolders
      Process SubFolder
    Next

    Dim File
    For Each File In Folder.Files
        If InStr(1, File.Path, "happiness") > 0 Then
            tpath = Left(File.Path, Len(File.Path) - Len(File.Name))
            MsgBox tpath & "  contains  " & File.Name
            FileIsThere = True
        End If
    Next
End Sub

这篇关于使用没有“DIR”的VBA或Excel测试文件是否存在的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！