On Error GoTo

G

Guest

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 . . . .]
 
J

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.
 
T

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 said:
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 . . . .]
Click to expand...
 
G

Guest

Thank you both for your reply.

benb said:
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 . . . .]
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Open Text Method Fails 7
Copy from one worksheet to another 1
saving a file using a cell for file name 2
Copy part of text file 4
On error GoTo Question 4
Open delimited file 3
Same action but different codes recorded. 4
Edit Macro script 2

Top