Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
On Error GoTo
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 . . . .] |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
On Error GoTo
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 , "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.) |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
On Error GoTo
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" wrote in message ... 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 . . . .] |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
On Error GoTo
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 . . . .] |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
On Error Goto doesn't goto | Excel Programming | |||
On Error Goto doesn't goto | Excel Programming | |||
On Error GoTo | Excel Programming | |||
On error goto 0? | Excel Programming | |||
On error goto 0 | Excel Programming |