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
閱讀更多 套路Excel 的文章
