VBA - 找不到文件时的msgbox
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/12010762/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
VBA - msgbox when file not found
提问by user1442459
I want a msgbox to show "File not found" when the file TestData.xlsx is not found. Thanks
当找不到文件 TestData.xlsx 时，我想要一个 msgbox 显示“找不到文件”。谢谢
Sub check()
Dim i As Long
'~~> From Row 5 to row 10
'~~> Chnage as applicable
For i = 5 To 10
Sheets("Sheet1").Range("F" & i).Formula = _
"=VLookup((CONCATENATE(C1,"" "",C" & i & _
")),'C:\Documents[TestData.xlsx]Sheet1'!$A:$G,7, FALSE)"
Sheets("Sheet1").Range("F" & i).Value = Sheets("Sheet1").Range("F" & i).Value
Next i
End Sub
回答by Daniel
Do a check for the file before your for loop:
在 for 循环之前检查文件：
If Dir$("C:\Documents\TestData.xlsx") = "" Then
MsgBox "File not found"
Exit Sub
End If
回答by Andy Raddatz
Add a reference to "Microsoft Scripting Runtime"
添加对“Microsoft Scripting Runtime”的引用
and then:
进而：
Dim fso As New FileSystemObject
If Not fso.FileExists("C:\Documents\TestData.xlsx") Then MsgBox "File Not Found."
回答by enderland
This will work.
这将起作用。
Sub test()
sPath = "C:\Documents\TestData.xlsx"
'Test if directory or file exists
If File_Exists(sPath) Then
MsgBox sPath & " exists!"
Else
MsgBox sPath & " does not exist."
End If
End Sub
Private Function File_Exists(ByVal sPathName As String,
Optional Directory As Boolean) As Boolean
'Returns True if the passed sPathName exist
'Otherwise returns False
On Error Resume Next
If sPathName <> "" Then
If IsMissing(Directory) Or Directory = False Then
File_Exists = (Dir$(sPathName) <> "")
Else
File_Exists = (Dir$(sPathName, vbDirectory) <> "")
End If
End If
End Function
This is from the second google result of "vba test if file exists" http://vbadud.blogspot.com/2007/04/vba-function-to-check-file-existence.html
这是来自“vba 测试文件是否存在”的第二个谷歌结果http://vbadud.blogspot.com/2007/04/vba-function-to-check-file-existence.html
