• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 215
  • Last Modified:

How to avoid this error message?

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
kspuea
Asked:
kspuea
  • 4
  • 3
1 Solution
 
snocrossCommented:
Can you post your code?
0
 
kspueaAuthor Commented:
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
 
snocrossCommented:
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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
andyDaleCommented:
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
 
kspueaAuthor Commented:
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
 
snocrossCommented:
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
 
kspueaAuthor Commented:
Thanks a lot, snocross!
It works perfectly to me!
0
 
snocrossCommented:
You're welcome, I'm glad it worked!
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now