Error handling clarification

[corquando]

Hello, gurus.

A quick question having to do with semantics as much as anything else.

I have a routine that occasionally and inexplicably throws a "File not found" error when performing a mail merge. When this occurs, the debug button opens the VBA studio and I hit 'F5' and it takes off again as if the error never happened. It's not on the same template every time, nor is the pattern anything but random, and the files in question do exist because their names are retrieved from existing folders earlier in the program.

Anyway, I'd like to bracket the offending command in "On Error Resume Next" and "On Error GoTo 0" so that in my absence my staff can run the program and not have to contend with this randomness. However, everything I read says that "On Error Resume Next" "ignores the error and resumes execution on the next line of code."

I'd like execution to resume on the offending command itself, since there never seems to be an actual error when handled manually. Am I simply reading the explanation incorrectly, or should I approach the situation differently?

Thanks.

Just in case, the command in question is displayed below. All standard Dims and Sets are present.

            AppWd.ActiveDocument.MailMerge.OpenDataSource Name:= _
                "G:\Huge\Big\Medium\Specific\DocMaker.xlsm" _
                , ConfirmConversions:=False, ReadOnly:=False, LinkToSource:=True, _
                AddToRecentFiles:=False, PasswordDocument:="", PasswordTemplate:="", _
                WritePasswordDocument:="", WritePasswordTemplate:="", Revert:=False, _
                Format:=wdOpenFormatAuto, Connection:= _
                "Provider=Microsoft.Jet.OLEDB.4.0;Password="""";User ID=Admin;Data Source=G:\Huge\Big\Medium\Specific\DocMaker.xlsm;Mode=Read;Extended Properties=""HDR=YES;IMEX=1;"";Jet OLEDB:System database="""";Jet OLEDB:Registry Path="""";Je" _
                , SQLStatement:="SELECT * FROM `DataSet$`", SQLStatement1:="", SubType:= _
                wdMergeSubTypeAccess

 

[sous2817]

Not sure if this is happening, but could there be a delay in opening the file. So when the code executes and the file isn't open, you get the error message. In the time it takes to press "debug" and then "F5" the file has had a chance to open and so it continues on like normal.

Perhaps putting a "Wait for 5 seconds" before executing that line of code will stop the error from happening.

 

[ScottR]

First I agree that you should be addressing the error rather than just trying to bypass it and the delay with file opening sounds plausible.

That said, the following example might help a little with possible error handling strategies by way of illustrating alternates to "Resume Next".

Sub ThrowError()
Dim x As Double, y As Integer
On Error GoTo ThrowErr

x = 0
CodeSection:
y = 9 / Int(x)

MsgBox y

Exit Sub
ThrowErr:
x = x + 0.1
Debug.Print x
Resume CodeSection

End Sub

 

[Norie]

Do you know why the error is occuring?

How intermittent is it?

If it really is because the file can't be found you could check for it's existence using Dir.

 

[corquando]

Wow. This is why MrExcel is one of the best sites ever.

No clue what the cause is, but I hadn't considered a delay issue; in retrospect that may well be what's happening since there are over 150 templates to choose from. The occurrences are random and patternless - sometimes a day between, sometimes a month but always inconvenient. I'll add the wait function just to give it a bit of breathing room. Of course, I won't be able to come back and say "Hey, it worked!" until 2 or 3 months go by and the error doesn't happen, so I'm saying "woo-hoo" now.

And the files do exist, so thanks for the lookout - and I thought of that at first, too - but that's a given. Their names are retrieved from a command earlier in the program, and can't be retrieved without the template already in existence since they must come from the directory involved.

That said, the second suggestion is a worthy addition as well simply because you can't have too many answers. I'll be curious as to what it reveals.

So, merci tres beaucoups to everybody. Rock on.

 

[Norie]

You say the filenames are retrieved earlier in the code?

Have you considered that could be the origin of the error?

Perhaps just a character or space out of place when the filename is being created/retrieved.

Maybe worth a look.

 

[corquando]

Hey.

That is a small but real possibility, but I'd think that the F5 button would simply show me an error box if the error were real.

I'm going to do the delay thing, and then if it throws an error the chance of it being what you describe would be much larger, and I could address it at that point with greater conifidence.

Thanks again!

 

[Norie]

Wouldn't an incorrect filename/path kind of be real, and cause an error?

 

[corquando]

Yep. Which is why when I hit F5 and it resumes running instead of insisting there's an error, I have to believe there wasn't one to begin with. That's what was weird about it all.

 

[Norie]

Well I can't explain that without knowing a bit more about the process.

The only thing I can think of off the top of my head that I've experienced similar to this is when waiting for a webpage to completely load.

eg one minute it's not there, next minute it is.

That can be handled with a simple loop that checks if the page has been loaded before continuing.

Perhaps you could try something similar.

 Do Until 
       Dir(strFileName)<>""
       DoEvents
 Loop

That's taken directly from some web code but it might work, or it could send you into an infinite loop of despair.