On Error GoTo

[benb]

I am including my code below. The problem I am having is that it seems if on
execution the code encounters two errors, my second On Error GoTo command
does not work. I am opening two files, and I want the code to prompt the
user for each file's location should it not be able to find them using the
specified path. If it can find one, but not the other (only one error) the
code works fine. But if it can't find either, the first On Error Goto works
while the second does not (I get the standard error message). Why is this?
How can I fix it? (Note: RPTCHANGE is a standard error message for the
program to display in any other circumstances.)

Dim rptFile As String, fileRpt As String

On Error GoTo GETPATH
Workbooks.OpenText Filename:= _
"G:\EMERGING MARKETS\EMST05\Daily P&L\Structured\nycreditdld.txt", _
Origin:=xlWindows, StartRow:=1, DataType:=xlDelimited, TextQualifier:= _
xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, Semicolon:=False, _
Comma:=False, Space:=False, Other:=True, OtherChar:="|", FieldInfo:= _
Array(Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1),
Array(6, 1), Array(7 _
, 1), Array(8, 1), Array(9, 1), Array(10, 1), Array(11, 1), Array(12,
1), Array(13, 1), Array _
(14, 1), Array(15, 1), Array(16, 1), Array(17, 1), Array(18, 1),
Array(19, 1), Array(20, 1), _
Array(21, 1))
On Error GoTo RPTCHANGE
GoTo SKIPPATH

GETPATH:
rptFile = Application.GetOpenFilename(Title:="Where's the Oasys report?")
If rptFile = "False" Then
Exit Sub
End If
Workbooks.OpenText Filename:=rptFile1, _
Origin:=xlWindows, StartRow:=1, DataType:=xlDelimited, TextQualifier:= _
xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, Semicolon:=False, _
Comma:=False, Space:=False, Other:=True, OtherChar:="|", FieldInfo:= _
Array(Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1),
Array(6, 1), Array(7 _
, 1), Array(8, 1), Array(9, 1), Array(10, 1), Array(11, 1), Array(12,
1), Array(13, 1), Array _
(14, 1), Array(15, 1), Array(16, 1), Array(17, 1), Array(18, 1),
Array(19, 1), Array(20, 1), _
Array(21, 1))
On Error GoTo RPTCHANGE

SKIPPATH: [some other code . . .]

On Error GoTo PATHGET
Workbooks.OpenText Filename:="G:\EMERGING MAKETS\EMST05\Daily
P&L\Structured\frontarenadld.txt", Origin:= _
xlWindows, StartRow:=1, DataType:=xlDelimited, TextQualifier:= _
xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, Semicolon:=False, _
Comma:=False, Space:=False, Other:=True, OtherChar:="|", FieldInfo:= _
Array(1, 1)
On Error GoTo RPTCHANGE
GoTo PATHSKIP

PATHGET:
fileRpt = Application.GetOpenFilename(Title:="Where's the Front Arena
report?")
If fileRpt = "False" Then
Exit Sub
End If
Workbooks.OpenText Filename:=fileRpt, Origin:= _
xlWindows, StartRow:=1, DataType:=xlDelimited, TextQualifier:= _
xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, Semicolon:=False, _
Comma:=False, Space:=False, Other:=True, OtherChar:="|", FieldInfo:= _
Array(1, 1)
On Error GoTo RPTCHANGE

PATHSKIP: [more code . . . .]

[JE McGimpsey]

Take a look at VBA Help for On Error...

Once an error handler becomes active, any further errors can't be
handled within that Sub until a Resume (or Exit Sub) instruction is
executed.

Normally I handle errors at the end of a procedure, e.g.:

On Error GoTo GETPATH
Workbooks.OpenText...
On Error GoTo PATHGET
Workbooks.OpenText...
On Error GoTo RPTCHANGE
'More Code
Exit Sub

GETPATH:
rptFile=...
If rptFile = False Then Exit Sub
Workbooks.OpenText...
Resume Next
PATHGET:
fileRpt = ...
If fileRpt = "False" Then Exit Sub
Workbooks.OpenText...
Resume Next

Note that this eliminates all the non-On Error GoTo's, which makes your
code logic much more straightforward.



In article <[email protected]>,
"benb" <[email protected]> wrote:

> I am including my code below. The problem I am having is that it seems if on
> execution the code encounters two errors, my second On Error GoTo command
> does not work. I am opening two files, and I want the code to prompt the
> user for each file's location should it not be able to find them using the
> specified path. If it can find one, but not the other (only one error) the
> code works fine. But if it can't find either, the first On Error Goto works
> while the second does not (I get the standard error message). Why is this?
> How can I fix it? (Note: RPTCHANGE is a standard error message for the
> program to display in any other circumstances.)
>

[Tom Ogilvy]

If you have established an On Error command and an error occurs, all code
executed after that is considered to be Error Handling code until a Resume
statement is encountered. If an error is encountered in Error Handling Code
(in other words you are handling an error), VBA quits in frustration,
apparently considering the coder to be a moron (as it seems to so often do)
<g>.

If you want to jump around as you have here, you will need to get in some
Resume statements. See help on On Error in VBA help.

Easier would be to avoid errors and just test the existence of the file

if dir(fname1) <> "" then
' file exists, open it

end if
if dir(fname2) <> "" then
' file2 exists, open it

End if

--
Regards,
Tom Ogilvy


"benb" <[email protected]> wrote in message
news:[email protected]...
> I am including my code below. The problem I am having is that it seems if
on
> execution the code encounters two errors, my second On Error GoTo command
> does not work. I am opening two files, and I want the code to prompt the
> user for each file's location should it not be able to find them using the
> specified path. If it can find one, but not the other (only one error)
the
> code works fine. But if it can't find either, the first On Error Goto
works
> while the second does not (I get the standard error message). Why is
this?
> How can I fix it? (Note: RPTCHANGE is a standard error message for the
> program to display in any other circumstances.)
>
> Dim rptFile As String, fileRpt As String
>
> On Error GoTo GETPATH
> Workbooks.OpenText Filename:= _
> "G:\EMERGING MARKETS\EMST05\Daily P&L\Structured\nycreditdld.txt", _
> Origin:=xlWindows, StartRow:=1, DataType:=xlDelimited, TextQualifier:=
_
> xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True,
Semicolon:=False, _
> Comma:=False, Space:=False, Other:=True, OtherChar:="|", FieldInfo:= _
> Array(Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1),
> Array(6, 1), Array(7 _
> , 1), Array(8, 1), Array(9, 1), Array(10, 1), Array(11, 1), Array(12,
> 1), Array(13, 1), Array _
> (14, 1), Array(15, 1), Array(16, 1), Array(17, 1), Array(18, 1),
> Array(19, 1), Array(20, 1), _
> Array(21, 1))
> On Error GoTo RPTCHANGE
> GoTo SKIPPATH
>
> GETPATH:
> rptFile = Application.GetOpenFilename(Title:="Where's the Oasys report?")
> If rptFile = "False" Then
> Exit Sub
> End If
> Workbooks.OpenText Filename:=rptFile1, _
> Origin:=xlWindows, StartRow:=1, DataType:=xlDelimited, TextQualifier:=
_
> xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True,
Semicolon:=False, _
> Comma:=False, Space:=False, Other:=True, OtherChar:="|", FieldInfo:= _
> Array(Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1),
> Array(6, 1), Array(7 _
> , 1), Array(8, 1), Array(9, 1), Array(10, 1), Array(11, 1), Array(12,
> 1), Array(13, 1), Array _
> (14, 1), Array(15, 1), Array(16, 1), Array(17, 1), Array(18, 1),
> Array(19, 1), Array(20, 1), _
> Array(21, 1))
> On Error GoTo RPTCHANGE
>
> SKIPPATH: [some other code . . .]
>
> On Error GoTo PATHGET
> Workbooks.OpenText Filename:="G:\EMERGING MAKETS\EMST05\Daily
> P&L\Structured\frontarenadld.txt", Origin:= _
> xlWindows, StartRow:=1, DataType:=xlDelimited, TextQualifier:= _
> xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True,
Semicolon:=False, _
> Comma:=False, Space:=False, Other:=True, OtherChar:="|", FieldInfo:= _
> Array(1, 1)
> On Error GoTo RPTCHANGE
> GoTo PATHSKIP
>
> PATHGET:
> fileRpt = Application.GetOpenFilename(Title:="Where's the Front Arena
> report?")
> If fileRpt = "False" Then
> Exit Sub
> End If
> Workbooks.OpenText Filename:=fileRpt, Origin:= _
> xlWindows, StartRow:=1, DataType:=xlDelimited, TextQualifier:= _
> xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True,
Semicolon:=False, _
> Comma:=False, Space:=False, Other:=True, OtherChar:="|", FieldInfo:= _
> Array(1, 1)
> On Error GoTo RPTCHANGE
>
> PATHSKIP: [more code . . . .]

[benb]

Thank you both for your reply.

"benb" wrote:

> I am including my code below. The problem I am having is that it seems if on
> execution the code encounters two errors, my second On Error GoTo command
> does not work. I am opening two files, and I want the code to prompt the
> user for each file's location should it not be able to find them using the
> specified path. If it can find one, but not the other (only one error) the
> code works fine. But if it can't find either, the first On Error Goto works
> while the second does not (I get the standard error message). Why is this?
> How can I fix it? (Note: RPTCHANGE is a standard error message for the
> program to display in any other circumstances.)
>
> Dim rptFile As String, fileRpt As String
>
> On Error GoTo GETPATH
> Workbooks.OpenText Filename:= _
> "G:\EMERGING MARKETS\EMST05\Daily P&L\Structured\nycreditdld.txt", _
> Origin:=xlWindows, StartRow:=1, DataType:=xlDelimited, TextQualifier:= _
> xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, Semicolon:=False, _
> Comma:=False, Space:=False, Other:=True, OtherChar:="|", FieldInfo:= _
> Array(Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1),
> Array(6, 1), Array(7 _
> , 1), Array(8, 1), Array(9, 1), Array(10, 1), Array(11, 1), Array(12,
> 1), Array(13, 1), Array _
> (14, 1), Array(15, 1), Array(16, 1), Array(17, 1), Array(18, 1),
> Array(19, 1), Array(20, 1), _
> Array(21, 1))
> On Error GoTo RPTCHANGE
> GoTo SKIPPATH
>
> GETPATH:
> rptFile = Application.GetOpenFilename(Title:="Where's the Oasys report?")
> If rptFile = "False" Then
> Exit Sub
> End If
> Workbooks.OpenText Filename:=rptFile1, _
> Origin:=xlWindows, StartRow:=1, DataType:=xlDelimited, TextQualifier:= _
> xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, Semicolon:=False, _
> Comma:=False, Space:=False, Other:=True, OtherChar:="|", FieldInfo:= _
> Array(Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1),
> Array(6, 1), Array(7 _
> , 1), Array(8, 1), Array(9, 1), Array(10, 1), Array(11, 1), Array(12,
> 1), Array(13, 1), Array _
> (14, 1), Array(15, 1), Array(16, 1), Array(17, 1), Array(18, 1),
> Array(19, 1), Array(20, 1), _
> Array(21, 1))
> On Error GoTo RPTCHANGE
>
> SKIPPATH: [some other code . . .]
>
> On Error GoTo PATHGET
> Workbooks.OpenText Filename:="G:\EMERGING MAKETS\EMST05\Daily
> P&L\Structured\frontarenadld.txt", Origin:= _
> xlWindows, StartRow:=1, DataType:=xlDelimited, TextQualifier:= _
> xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, Semicolon:=False, _
> Comma:=False, Space:=False, Other:=True, OtherChar:="|", FieldInfo:= _
> Array(1, 1)
> On Error GoTo RPTCHANGE
> GoTo PATHSKIP
>
> PATHGET:
> fileRpt = Application.GetOpenFilename(Title:="Where's the Front Arena
> report?")
> If fileRpt = "False" Then
> Exit Sub
> End If
> Workbooks.OpenText Filename:=fileRpt, Origin:= _
> xlWindows, StartRow:=1, DataType:=xlDelimited, TextQualifier:= _
> xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, Semicolon:=False, _
> Comma:=False, Space:=False, Other:=True, OtherChar:="|", FieldInfo:= _
> Array(1, 1)
> On Error GoTo RPTCHANGE
>
> PATHSKIP: [more code . . . .]