1、Dir函数判断文件是否存在

a = Dir("c:\123.xlsx")

If a = "" Then

MsgBox "不存在"

Else

MsgBox "存在"

End If

如果文件存在，a返回文件名称123.xlsx，否则返回空白。

根据Dir函数，介绍一些比较有用的自定义函数。

2、判断文件是否存在，fname参数需写完整路径（包含文件名称）。

Private Function FileExists(fname) As Boolean

' 如果文件存在返回TRUE

Dim x As String

x = Dir(fname)

If x <> "" Then FileExists = True _

Else FileExists = False

End Function

3、从路径中返回文件名称

Private Function FileNameOnly(pname) As String

' 返回文件名称

Dim temp As Variant

Length = Len(pname)

temp = Split(pname, Application.PathSeparator)

FileNameOnly = temp(UBound(temp))

End Function

4、判断路径是否存在，返回True

Private Function PathExists(pname) As Boolean

' 返回路径，如果路径存在

If Dir(pname, vbDirectory) = "" Then

PathExists = False

Else

PathExists = (GetAttr(pname) And vbDirectory) = vbDirectory

End If

End Function

5、判断工作簿是否打开

Private Function WorkbookIsOpen(wbname) As Boolean

' 如果打开返回True

Dim x As Workbook

On Error Resume Next

Set x = Workbooks(wbname)

If Err = 0 Then WorkbookIsOpen = True _

Else WorkbookIsOpen = False

End Function