help on text file import error handler

[saurabh_546]

Dear all
I am importing a text file into the excel
I am working on errorhandler now
there are only three possible runtime errors i can get
1st is user cancels the procedure
2nd is the destination sheet alerady exists
3rd is if text file is not found

I am able to workout first 2 , i need help on 3rd
here is my code

ErrorHandler:
    If folderName = "" Then
        Application.DisplayAlerts = False
        ShNew.Delete
        Application.DisplayAlerts = True
    Exit Sub
 
    Else
 
  If Err.Description = "Cannot rename a sheet to the same name as another sheet, a referenced object library or a workbook referenced by Visual Basic." Then
 
    MsgBox "An error was encountered while attempting to import the data." & vbCrLf & Err.Description _
    & vbCrLf & "Please rename or delete the sheet before importing the data." & vbCrLf, vbCritical, "Error " & Err.Number
        Application.DisplayAlerts = False
        ShNew.Delete
        Application.DisplayAlerts = True
      Else
      If Err.Description = "Cannot rename a sheet to the same name as another sheet, a referenced object library or a workbook referenced by Visual Basic." Then
      MsgBox "The Folder does not exists" & vbCrLf & "Please select the valid folder." & vbCrLf, vbCritical, "Error " & Err.Number
 
 
            End If
        End If
    End If

 

[Andrew Poulsom]

You could use the FileExists function here:

http://spreadsheetpage.com/index.php/tip/some_useful_vba_functions/

 

[saurabh_546]

thanx for help andrew
but i have one more question

i want to give user another chance if he by-mistake gives wrong path
i want to direct him to inputbox where he gives folder name
and its pretty much at the beginning of the code (line no. 36)
i tried to use goto or resume but m getting syntax error


can you tell me how to do that?

regards
Saurabh

 

[Andrew Poulsom]

How are you prompting the user for a file name? If you use the GetOpenFileName method the file must exist:

http://www.dailydoseofexcel.com/archives/2004/06/09/getopenfilename/

 

[saurabh_546]

my file name is fixed
only difference is there are different folders eg. run11, run12 and so on.
program accepts the folder name with inout box

folderName = InputBox("Enter the folder name." & vbCrLf & "(e.g. run12)", "Folder Name")

then program creats a string for import

listPath = "TEXT;" & excelPath & folderName & "\erg_f_d_filter.lst"

so for the second chance user should again start from folder name

 

[Andrew Poulsom]

Here's an example:

Sub Test()
    Const ExcelPath As String = "C:\TEMP\"
    Const FileName As String = "erg_f_d_filter.lst"
    Dim FolderName As String
    Do
        FolderName = InputBox("Enter the folder name." & vbCrLf & "(e.g. run12)", "Folder Name")
'       Cancel pressed
        If FolderName = "" Then Exit Sub
'       Test for existence of file
        If Dir(ExcelPath & FolderName & "\" & FileName) <> "" Then
            Exit Do
        Else
            MsgBox "The file does not exist in that folder"
        End If
    Loop
'   Code to process file
End Sub

 

[saurabh_546]

Thanx Andrew<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
my little program is finished now in coming days i will submit this to my professor..<o></o>
<o></o>
thanx for your valuable suggestions, without which it was not possible to complete..<o></o>
<o></o>
i would like to have this apportunity to thank you and other people who helped me.<o></o>
i donno when in future i will do excel or vba programming again..<o></o>
as my area of study is automotive engg.
<o></o>
Hope to see you soon<o></o>
Kind Regards <o></o>
Saurabh<o></o>