Exit Sub between Calls to prevent subscript error 2

[gemoon]

Ive written a macro to run from excel. This macro processes data from comma delemited text files. The macro is comprised of 3 subroutines. The first prompts the user to select a file name and opens the file, the second does some data manipulation and the third prompts the user and does a file save as.

Everything works great except when the user cancels the file open step. In that step Ive set up some error handling which exits the subroutine if FileName is false. However the process then goes to "Call CopySampleData" and generates an error because there is no open file. So my question what can I add to exit the macro if no file is selected?

Many thanks,
Ed.


Sub ProcessICAPData()

Call OpenTextFile

Call CopySampleData

Call SaveAsExcelFile

End Sub

The first subroutine is as follows:

Sub OpenTextFile()

' Get the file name
FileName = Application.GetOpenFilename("Text Files (*.txt),*.txt,", , "Select a text file to process&quot

' Exit if dialog box canceled
If FileName = False Then
MsgBox "No file was selected."
Exit Sub
End If

' Open text file
Workbooks.OpenText FileName, Origin:= _
xlWindows, StartRow:=1, DataType:=xlDelimited, TextQualifier:= _
xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, Semicolon:=False _
, Comma:=True, Space:=False, Other:=False, FieldInfo:=Array(1, 1)

End Sub

 

[SkipVought]

Hi,

There are many ways to skin a cat. You could set a global variable with an error code.

You could make each Subroutine a Function instead that returns a completion code that you set.



Skip,

http://www.TheOfficeExperts.com

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[Zathras]

Change them to functions and return True if ok to continue:

Sub ProcessICAPData()
    If OpenTextFile Then
      If CopySampleData Then
        Call SaveAsExcelFile
      End If
    End If
End Sub

Function OpenTextFile() as Boolean

' Get the file name
    FileName = Application.GetOpenFilename("Text Files (*.txt),*.txt,", , "Select a text file to process")

' Exit if dialog box canceled
    If FileName = False Then
        MsgBox "No file was selected."
        Exit Sub
    Else
      OpenTextFile = True
' Open text file
    Workbooks.OpenText FileName, Origin:= _
        xlWindows, StartRow:=1, DataType:=xlDelimited, TextQualifier:= _
        xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, Semicolon:=False _
        , Comma:=True, Space:=False, Other:=False, FieldInfo:=Array(1, 1)
    End If
End Function

etc.

 

[gemoon]

Many thanks!
I used a variation of the function option.

If OpenTextFile Then
Call CopySampleData
Call SaveAsExcelFile
End If

Thanks for the advice!
Ed.