excel - 检查excel 2010 vba中是否存在目录

Question

我让用户输入一些信息MyBarcode 和MyScan，这些信息将用于创建目录。如果该目录存在，我想显示一条指示该目录的消息，然后返回到用户可以输入数据的步骤。 vba 如下，除了我需要一些帮助的目录检查之外，它似乎可以正常工作。我希望这是一个好的开始。谢谢你:)。

Private Sub CommandButton3_Click()

Dim MyBarCode   As String      ' Enter Barcode
Dim MyScan      As String      ' Enter ScanDate
Dim MyDirectory As String

MyBarCode = Application.InputBox("Please enter the last 5 digits of the barcode", "Bar Code", Type:=2)

If MyBarCode = "False" Then Exit Sub   'user canceled
Do
    MyScan = Application.InputBox("Please enter scan date", "Scan Date", Date - 1, Type:=2)
    If MyScan = "False" Then Exit Sub   'user canceled
    If IsDate(MyScan) Then Exit Do
    MsgBox "Please enter a valid date format. ", vbExclamation, "Invalid Date Entry"
Loop

'Create nexus directory and folder check
MyDirectory = "N:\1_DATA\MicroArray\NexusData\" & "2571683" & MyBarCode & "_" & Format(CDate(MyScan), "m-d-yyyy") & "\"
If MyDirectory("MyDirectory") Then
    MsgBox "Folder exists! Please try again", Goto MyBarcode

Else
If Dir(MyDirectory, vbDirectory) = "" Then MkDir MyDirectory

Answer 1

使用 Dir 和 GetAttr 函数检查目录是否存在，如下所示:

Function DirectoryExists(Directory As String) As Boolean
    DirectoryExists = False
    If Not Dir(Directory, vbDirectory) = "" Then
        If GetAttr(Directory) = vbDirectory Then
            DirectoryExists = True
        End If
    End If
End Function

Sub TestDirectoryExists()
    Dim Directory As String
    Directory = "c:\src"                    ' is a valid directory
    Debug.Print DirectoryExists(Directory)  ' shows True
    Directory = "c:\src1"                   ' is not a valid directory
    Debug.Print DirectoryExists(Directory)  ' shows False
    Directory = "x:\src"                    ' is not a valid drive
    Debug.Print DirectoryExists(Directory)  ' shows False
    Directory = "c:\test.txt"               ' is a file
    Debug.Print DirectoryExists(Directory)  ' shows False
End Sub

当我们使用Dir时使用 vbDirectory 属性，我们除了寻找没有特殊属性的文件之外，还寻找文件夹。为了确认我们确实在寻找文件夹，我们使用 GetAttr并检查路径是否确实是一个文件夹。

请随意在按钮点击事件中使用此函数。