Return value if file is not present (A2002)

Topic

New Reply

I have some code that sets a file location, then a report gets created to that location using the PDF writer. From there I create a mail message with an attachment, which is that PDF file. How can I tell if the file has not been created? I want to create an error if the file is missing and stop the mail message from being created. When I do the attachment, it’s name is strOutFile, which points to the location of the file.

Thanks,
Mark

Reply | Quote

Replies

A simple way is to use the Dir function.

If strOutFile is the full name of the file, including the path, Dir(strOutFile) will return the file name if the file exists, and an empty string “” if it doesn’t. So you can use something like:

If Dir(strOutFile) = “” Then
MsgBox “The attachment has not been created”
Exit Sub
End If

If you have error handling in your code, you can generate an error:

Sub SomethingOrOther
On Error GoTo ErrHandler

…

If Dir(strOutFile) = “” Then
Err.Raise Number:=vbObjectError, Description:=”Attachment not created”
End If

…

ExitHandler:
‘ Do your cleaning up here
Exit Sub

ErrHandler:
MsgBox Err.Description, vbCritical
Resume ExitHandler
End Sub

Reply | Quote

In the description of the Raise method, I read, “When setting the Number property to your own error code in a class module, you add your error code number to the vbObjectError constant.”
I can see you can you also do without adding your error code number?

Reply | Quote

I added 0

Reply | Quote

I might be missing something here but should one be careful what error code number one chooses? As it seems to me there are numbers beyond 512 which are already taken, e.g., Err.Number = 3265 when you attempt to delete an object that is not in a collection.

Reply | Quote

The value of vbObjectError is -2147221504. Microsoft never uses an error number anywhere close to -2147221504 for built-in errors. So if you use vbOjectError + a small number, you can be certain that it won’t conflict with built-in error numbers. You are free to use Err.Raise with an existing error number, but that could be confusing.

Reply | Quote

So, does vbObjectError + 513 generate error number -2147220991 yet it’s still called error number 513?

Reply | Quote

For VBA, it is error number -2147220991. No more and no less. It is *not* the same as the built-in error 513 (if defined).

But you can interpret it as error 513 in the range of user-defined errors. It’s just to make it a little bit easier for you as programmer – you don’t have to remember, “Oh yeah, I used error number -2147220991 for that”.

Reply | Quote

Using the example above:
If Dir(strOutFile) = “” Then
Err.Raise Number:=vbObjectError, Description:=”Attachment not created”
End If
I suppose you use Err.Raise rather than
MsgBox “Attachment not created”
because of the need to use that error but considering that for VBA it is error number -2147221504 should you then process it using -2147221504 rather than 0? For example
if Err.Number= -2147221504 then …
and not
if Err.Number= 0 then …
And I’m even more confused by on-line help saying “The range 0-512 is reserved for system errors;”. If it is then how come you can use 0?

Reply | Quote

I repeat from my previous reply:

> For VBA, it is error number -2147220991. No more and no less.

So if you use

Err.Raise Number:=vbObjectError, …

and you want to test for this, you should use

If Err.Number = vbOjectError

For vbObjectError is NOT, I repeat NOT equal to 0.

If this is not yet entirely clear, please read post 641,728 again.

Reply | Quote

Thank you Hans, it’s clear now . I was thrown off by on-line help saying, “For example, to generate the error number 513, assign vbObjectError + 513 to the Number property.”
Now I know that error number 513 is a convention used for the programmer while VBA considers it as vbObjectError + 513 while, e.g., the error number 3265 mentioned earlier is 3265 to both the programmer and VBA.

Reply | Quote

A simple way is to use the Dir function.

If strOutFile is the full name of the file, including the path, Dir(strOutFile) will return the file name if the file exists, and an empty string “” if it doesn’t. So you can use something like:

If Dir(strOutFile) = “” Then
MsgBox “The attachment has not been created”
Exit Sub
End If

If you have error handling in your code, you can generate an error:

Sub SomethingOrOther
On Error GoTo ErrHandler

…

If Dir(strOutFile) = “” Then
Err.Raise Number:=vbObjectError, Description:=”Attachment not created”
End If

…

ExitHandler:
‘ Do your cleaning up here
Exit Sub

ErrHandler:
MsgBox Err.Description, vbCritical
Resume ExitHandler
End Sub

Reply | Quote