error handling filepath

**kchm_2000** · 05-17-2011, 10:57 AM

Hi,

I am trying to create an error handling when I input a file path to copy from a sheet from another file and then paste into the current sheet. However I can't seem to get the if error get me to retry and ask me to reinput the file path again. The main file to extract is AY09Exam file whilst the Question_1data file is the retrieval file. thanks for your help in advance.


Dim FP As String

On Error GoTo myerrorline
    FP = InputBox("Enter File Path")
    Workbooks.Open Filename:=("FP")
Exit Sub

Worksheets("exp_data").Columns.Copy Destination:=Workbooks("Question2").Worksheets("DATA").Columns

Workbooks("Question1_data").Close (SaveChanges = False)

myerrorline:
        retry = MsgBox("Error, do you want to retry?", vbYesNo)
        If retry = vbYes Then Resume
        If retry = vbNo Then Exit Sub
End Sub

**davegugg** · 05-17-2011, 11:29 AM

I'd suggest handling the chance that the file is not a real file without explicitly using errors. The Dir() method can be used to check if a file exists. I'd also suggest using Excel's built in File Open Dialog box rather than an input box. I've included that in the code below, but commented it out; uncomment to use it.

Public Sub kchm_2000()

    Dim strFile As String
    
    Do Until Dir(strFile) <> ""
        strFile = InputBox("Enter File Path")
        'strFile = Application.GetOpenFilename("Excel files (*.xls*), *.xls*")
    Loop
    
    Workbooks.Open Filename:=(strFile)
    Worksheets("exp_data").Columns.Copy Destination:=Workbooks("Question2").Worksheets("DATA").Columns
    Workbooks("Question1_data").Close (SaveChanges = False)

End Sub

**snb** · 05-17-2011, 11:29 AM

sub snb()
  do 
    fl=InputBox("Enter File Path")
  loop until fl="" or dir(fl)<>""
  if fl="" then exit sub
  
  With Workbooks.Open fl
     .sheets("exp_data").Columns.Copy ,Workbooks("Question2").sheets("DATA")
     .close 0
  end with

End Sub

Thread: error handling filepath

Thread Tools

Display

error handling filepath

Re: error handling filepath

Re: error handling filepath

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions