可以将文章内容翻译成中文,广告屏蔽插件可能会导致该功能失效(如失效,请关闭广告屏蔽插件后再试):
问题:
I have this code. It is supposed to check if a file exists and open it if it does. It does work if the file exists, and if it doesn't, however, whenever I leave the textbox blank and click the submit button, it fails. What I want, if the textbox is blank is to display the error message just like if the file didn't exist.
Runtime-error "1004"
Dim File As String
File = TextBox1.Value
Dim DirFile As String
DirFile = "C:\Documents and Settings\Administrator\Desktop\" & File
If Dir(DirFile) = "" Then
MsgBox "File does not exist"
Else
Workbooks.Open Filename:=DirFile
End If
回答1:
something like this
best to use a workbook variable to provide further control (if needed) of the opened workbook
updated to test that file name was an actual workbook - which also makes the initial check redundant, other than to message the user than the Textbox is blank
Dim strFile As String
Dim WB As Workbook
strFile = Trim(TextBox1.Value)
Dim DirFile As String
If Len(strFile) = 0 Then Exit Sub
DirFile = "C:\Documents and Settings\Administrator\Desktop\" & strFile
If Len(Dir(DirFile)) = 0 Then
MsgBox "File does not exist"
Else
On Error Resume Next
Set WB = Workbooks.Open(DirFile)
On Error GoTo 0
If WB Is Nothing Then MsgBox DirFile & " is invalid", vbCritical
End If
回答2:
I use this function to check for file existence:
Function IsFile(ByVal fName As String) As Boolean
'Returns TRUE if the provided name points to an existing file.
'Returns FALSE if not existing, or if it's a folder
On Error Resume Next
IsFile = ((GetAttr(fName) And vbDirectory) <> vbDirectory)
End Function
回答3:
For checking existence one can also use (works for both, files and folders):
Not Dir(DirFile, vbDirectory) = vbNullString
The result is True
if a file or a directory exists.
Example:
If Not Dir("C:\Temp\test.xlsx", vbDirectory) = vbNullString Then
MsgBox "exists"
Else
MsgBox "does not exist"
End If
回答4:
Maybe it caused by Filename variable
File = TextBox1.Value
It should be
Filename = TextBox1.Value
回答5:
I'll throw this out there and then duck.
The usual reason to check if a file exists is to avoid an error when attempting to open it. How about using the error handler to deal with that:
Function openFileTest(filePathName As String, ByRef wkBook As Workbook, _
errorHandlingMethod As Long) As Boolean
'Returns True if filePathName is successfully opened,
' False otherwise.
Dim errorNum As Long
'***************************************************************************
' Open the file or determine that it doesn't exist.
On Error Resume Next:
Set wkBook = Workbooks.Open(fileName:=filePathName)
If Err.Number <> 0 Then
errorNum = Err.Number
'Error while attempting to open the file. Maybe it doesn't exist?
If Err.Number = 1004 Then
'***************************************************************************
'File doesn't exist.
'Better clear the error and point to the error handler before moving on.
Err.Clear
On Error GoTo OPENFILETEST_FAIL:
'[Clever code here to cope with non-existant file]
'...
'If the problem could not be resolved, invoke the error handler.
Err.Raise errorNum
Else
'No idea what the error is, but it's not due to a non-existant file
'Invoke the error handler.
Err.Clear
On Error GoTo OPENFILETEST_FAIL:
Err.Raise errorNum
End If
End If
'Either the file was successfully opened or the problem was resolved.
openFileTest = True
Exit Function
OPENFILETEST_FAIL:
errorNum = Err.Number
'Presumabley the problem is not a non-existant file, so it's
'some other error. Not sure what this would be, so...
If errorHandlingMethod < 2 Then
'The easy out is to clear the error, reset to the default error handler,
'and raise the error number again.
'This will immediately cause the code to terminate with VBA's standard
'run time error Message box:
errorNum = Err.Number
Err.Clear
On Error GoTo 0
Err.Raise errorNum
Exit Function
ElseIf errorHandlingMethod = 2 Then
'Easier debugging, generate a more informative message box, then terminate:
MsgBox "" _
& "Error while opening workbook." _
& "PathName: " & filePathName & vbCrLf _
& "Error " & errorNum & ": " & Err.Description & vbCrLf _
, vbExclamation _
, "Failure in function OpenFile(), IO Module"
End
Else
'The calling function is ok with a false result. That is the point
'of returning a boolean, after all.
openFileTest = False
Exit Function
End If
End Function 'openFileTest()
回答6:
Here is my updated code. Checks to see if version exists before saving and saves as the next available version number.
Sub SaveNewVersion()
Dim fileName As String, index As Long, ext As String
arr = Split(ActiveWorkbook.Name, ".")
ext = arr(UBound(arr))
fileName = ActiveWorkbook.FullName
If InStr(ActiveWorkbook.Name, "_v") = 0 Then
fileName = ActiveWorkbook.Path & "\" & Left(ActiveWorkbook.Name, InStr(ActiveWorkbook.Name, ".") - 1) & "_v1." & ext
End If
Do Until Len(Dir(fileName)) = 0
index = CInt(Split(Right(fileName, Len(fileName) - InStr(fileName, "_v") - 1), ".")(0))
index = index + 1
fileName = Left(fileName, InStr(fileName, "_v") - 1) & "_v" & index & "." & ext
'Debug.Print fileName
Loop
ActiveWorkbook.SaveAs (fileName)
End Sub
回答7:
You should set a condition loop to check the TextBox1 value.
If TextBox1.value = "" then
MsgBox "The file not exist"
Exit sub 'exit the macro
End If
Hope it help you.