Window Message for not value found

M

maperalia

I have a program that open an excel file from select cell by asking the work
order number (see below). What statement do I need to get a window message
when I type the wrong work order number?.

Thanks in advance.
Maperalia


Sub Open_xls_File()

WO = Application.InputBox("Enter Work Order Number")
directory = "C:\Test\Colors\" & WO & "\"
filetext = Selection.Value
Workbooks.Open directory & filetext

End Sub
 
R

Ron de Bruin

Try this

If Dir(directory & filetext & ".xls") <> "" Then
Workbooks.Open directory & filetext & ".xls"
Else
MsgBox "Wrong"
End If


Maybe without the .xls part ?
 
M

maperalia

Ron;
Thanks for your quick response.
I statement is running very well, however, I donot wher I have to located
into my program because I got the window message all the time.
Could you please tell me where can I located it into my program?

Thanks.\
Maparalia
 
R

Ron de Bruin

If you have filename.xls in the worksheet cell then use example 2.
If you only have the filename without the extension use example1


Sub Open_xls_File1()

WO = Application.InputBox("Enter Work Order Number")
directory = "C:\Test\Colors\" & WO & "\"
filetext = Selection.Value

If Dir(directory & filetext & ".xls") <> "" Then
Workbooks.Open directory & filetext & ".xls"
Else
MsgBox "Wrong"
End If

End Sub


Sub Open_xls_File2()

WO = Application.InputBox("Enter Work Order Number")
directory = "C:\Test\Colors\" & WO & "\"
filetext = Selection.Value

If Dir(directory & filetext) <> "" Then
Workbooks.Open directory & filetext
Else
MsgBox "Wrong"
End If

End Sub
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top