Solved

How to avoid this error message?

Posted on 2000-05-12
8
198 Views
Last Modified: 2013-12-18
Hi,
I have a scripts library doing report generation based on information from a Excel file.

But I want to make sure that the Excel file is in the user's local drive before I proceed to generate the reports.

I get this following error message if the Excel file is not in the local drive:

"C:\mcs_data.xls" could not be found. Check the spelling of the file name. and verify that the file location is correct.
If you are trying to open the file from the list of most recently used files on the File menu, make sure that the file has not been renamed, moved, or deleted.


Anybody knows how to skip this error message and proceed to the rest of coding, if the file is not found?

Pls help me if you knows, it's quite urgent to me....
Thanks a lot.
0
Comment
Question by:kspuea
  • 4
  • 3
8 Comments
 
LVL 5

Expert Comment

by:snocross
Comment Utility
Can you post your code?
0
 

Author Comment

by:kspuea
Comment Utility
Here is my coding:

Dim myExcel As Variant, myExcel1 As Variant, myExcel2 As Variant
Dim filename As String, filename1 As String, filename2 As String, shName As String
Dim aKeys (1 To 2) As String, docTAno As String
Dim item As NotesItem
     
Set db = sess.CurrentDatabase
varMth = Month(Today) - 1
varYear = Trim$(Str$(Year(Today)))
Gosub ReportDate
     
filename = "C:\mcs_data.xls"
Set myExcel = GetObject(filename, "Excel.Sheet")
-->I get an error at the above line if my excel file is not there.

Any ways to avoid the error message?

Thanks for your help, snocross!
0
 
LVL 5

Expert Comment

by:snocross
Comment Utility
Try looking at DIR$.  I haven't done it but it looks like you can use DIR$ to retreive a list of files and check to see if it exists before you execute against the filename.
0
 

Expert Comment

by:andyDale
Comment Utility
the other option would be to use error handling calls....

run through your code once and let the error come up. Make a note of the error number.

In your code, at the top, add:
   On Error Goto err_hand

Now at the very bottom of the code do the following:

the_end:
  Exit Sub

err_hand:
  if err() = YOUR_ERR_NUMBER Then
    ' print a specific error message
    MsgBox("Incorrect file name ....")
    Resume the_end
  else
    'print a generic error message
    MsgBox("Error "+cstr(err())+ " - "+Error(), ,"Error")
    Resume the_end
  end if
End Sub

Instead of just putting out messages you could have a routine that smartly finds the xls file, opens it and then the line:
  Resume Next
starts execution of your code back where you errered out from.
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 

Author Comment

by:kspuea
Comment Utility
andyDale, it doesn't have any error number associated. The error is actually invoked by MS Excel, not by Lotus Notes.

snocross, can you provide a sample coding of your ideas?
0
 
LVL 5

Accepted Solution

by:
snocross earned 60 total points
Comment Utility
I haven't tried this but this might work...

Dim pathName As String, fileName As String
pathName$ = "c:\*.*"
fileName$ = Dir$(pathName$, 0)

Do While fileName$ <> ""
   Print fileName$
   fileName$ = Dir$()

If fileName$ = "C:\mcs_data.xls" then

   PUT YOUR CODE HERE TO OPEN THE FILE

End if

Loop
0
 

Author Comment

by:kspuea
Comment Utility
Thanks a lot, snocross!
It works perfectly to me!
0
 
LVL 5

Expert Comment

by:snocross
Comment Utility
You're welcome, I'm glad it worked!
0

Featured Post

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

You’ve got a lotus Domino web server, and you have been told that “leverage browser caching” is a must do. This means that we have to tell the browser everywhere in the web to use cache. In other words, we set (and send) an expiration date in the HT…
Article by: Rob
Notes 8.5 Archiving Steps and Tips This article covers setting up a Notes archive, and helps understand some of the menu choices making setting up and maintaining a Notes archive file easier.
This video discusses moving either the default database or any database to a new volume.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

771 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now