How to tell user If unable to locate an excel file without showing runtime error

I have a program that opens many excel files. But if the program cannot find the designated file, how to pop up a message telling that file not found instead of runtime errors? Anyone can help?

In a nutshell thats what you need.

Please Login or Register  to view this content.

Sample of my program is like this

Workbooks.Open Filename:=_
"C:\xxxx\xxxx"
ActiveWindow.Close

May I know how to use the nutshell you mentioned inside?

I gave like this

If (Dir(FileName) = "C:\xxx\xxx") Then
FileThere = False
MsgBox "no file found"
Else
FileThere =True
ActiveWindow.Close
Endif

But it having runtime error Type mismatch. What should I do?

When I step the code, runtime error happening on the first line of nutshell

Hi

Sorry I wasn't aware that you were on line.

Can you tell me which directory you want to look in and the file name?

I have just tried this and it works.

Please Login or Register  to view this content.

The first line tells excel which director to look in. The best way to get that is to record a macro and save to that directory.

The second line is looking for file Book1.xlsm in that directory. The best way to get the filename is to record a macro and open the file.

The rest should be self explanatory.

I hope That Helps.

Hi Mehmetcik,

This code works perfectly fine for me. It solved the issue. Thanks a lot for the helping hand

Hi Mehmetcik,

This code works perfectly fine for me. It solved the issue. Thanks a lot for the helping hand

Originally Posted by mehmetcik

I have just tried this and it works.

Please Login or Register  to view this content.