PDA

View Full Version : Solved: Capturing Error Message



jazzyt2u
08-05-2008, 01:22 PM
Hi,

In Excel I have code that asks for a user to enter a folder path where they have stored a word template I've created. The code then combines the folder path with the name of the word template.

If they enter the wrong path they will get a system generated run-time error 5273 saying that document name or path is invalid. Is there a way to by pass this message so that the user doesn't have to see it but know that this message was generated and then show user what they entered incorrectly and have them re-enter their folder path?


MyFolder = InputBox("Please enter or copy and paste the folder path that contains the files for this project. EX: H:\Test2\ Don't forget the last ' \ '")
If Right(MyFolder, 1) <> "\" Then
Do Until Right(MyFolder, 1) = "\"
MyFolder = InputBox("You didn't enter the last '\'. Please re-enter your folder path EX: H:\Test2\")
Loop
End If
'On Error?????
Set oDoc = oWord.Documents.Open(MyFolder & "ReportTemplate.doc")

' Create If than Statement that would detect the system error number



If the If than statement is not good please suggest something else.

Thanks a bunch!!!: pray2:

Bob Phillips
08-05-2008, 01:40 PM
Dim MyFolder As String

Do

MyFolder = InputBox("Please enter or copy and paste the folder path that contains the files for this project." & vbNewLine & _
"EX: H:\Test2\ Don't forget the last ' \ '")
If MyFolder <> "" Then

If Right(MyFolder, 1) <> "\" Then

MyFolder = InputBox("You didn't enter the last '\'. Please re-enter your folder path EX: H:\Test2\")
End If
End If
Loop Until Right(MyFolder, 1) = "\" Or MyFolder = ""

If MyFolder <> "" Then

Set oDoc = oWord.Documents.Open(MyFolder & "ReportTemplate.doc")

' Create If than Statement that would detect the system error number
End If

jazzyt2u
08-05-2008, 02:38 PM
Thank you for getting back to me but this doesn't flag them that they've entered the wrong path only if they forget to enter a path...

Bob Phillips
08-05-2008, 03:22 PM
What constitutes wrong?

jazzyt2u
08-05-2008, 04:25 PM
If they don't enter the correct path. Typo...
I found the answer though:


Set oWord = CreateObject("Word.Application") 'Create an instance of word

MyFolder = InputBox("Please enter or copy and paste the folder path that contains the files for this project." & vbNewLine & _
vbNewLine & "EX: H:\Test2\" & vbNewLine & vbNewLine & "Don't forget the last ' \ '")

If Right(MyFolder, 1) <> "\" Then
Do Until Right(MyFolder, 1) = "\"
MyFolder = InputBox("You didn't enter the last '\'." & vbNewLine & vbNewLine & "Please re-enter your folder path EX: H:\Test2\")
Loop
End If
On Error GoTo ErrorHandler
Set oDoc = oWord.Documents.Open(MyFolder & "ReportTemplate.doc") 'Open word file

Exit Sub
ErrorHandler: ' Error-handling routine.

Select Case Err.Number ' Evaluate error number.
Case 5273 ' "Invalid Document name or Invalid Path" error.
MyFolder = InputBox("The path you entered: " & (MyFolder) & vbNewLine & vbNewLine & _
"Doesn't contain the ReportTemplate." & vbNewLine & vbNewLine & "Please re-enter your folder path")
Case 5174 'Blank Entry
MyFolder = InputBox("You didn't enter a path" & vbNewLine & vbNewLine & _
"Please re-enter your folder path" & vbNewLine & vbNewLine & "EX: H:\Test2\")

If Right(MyFolder, 1) <> "\" Then
Do Until Right(MyFolder, 1) = "\"
MyFolder = InputBox("You didn't enter the last '\'." & vbNewLine & vbNewLine & "Please re-enter your folder path EX: H:\Test2\")
Loop
End If

End Select
Resume ' Resume execution at same line
' that caused the error.
End Sub