Importing Files

[waderw]

I am having trouble understanding why my error trap is not picking up the incorrect file name. The desired file to import is "M6237a" but if i enter "6237a" leaving off the "M" the marcro crashes. Why is it not going into my error trap saying "File does not exist"? Any thoughts? My code is below:


Dim infile,fname as String
fname = InputBox(Msg, Title, Default)
inFile = fname & ext
If StrPtr(fname) = 0 Then Exit Sub
With Application.FileSearch .LookIn = Curr_Dir
.FileName = inFile
.MatchTextExactly = True
If .Execute > 0 Then
Workbooks.Open Curr_Dir & inFile
Range("A6").Select
Else
MsgBox "The file " & inFile & " does not exist." & vbCrLf & "Check the file name and try again.", vbCritical, "Error Importing File"
Exit Sub
End If
End With

[Juan Pablo González]

I would replace your code with the GetOpenFileName method. Take a look at it.

[waderw]

Any ideas as to why this would be happening with the code I am using? I tried making the file variable a variant and it still occured.

Thanks for the function reference above. I guess I will have to use that.

[waderw]

Thanks Pablo! I was a little skeptical about using the function but I like it so much now that I think that I am going to use it from now on.

PS. I have a really nice function that compliments the GetOpenFileName function to extract the file name from the path. See below:

Function ExtractFileName(fileToOpen As Variant) As String
Dim X As Integer
For X = Len(fileToOpen) To 1 Step -1
If Mid$(fileToOpen, X, 1) = "" Then Exit For
Next
ExtractFileName = Right$(fileToOpen, Len(fileToOpen) - X)
End Function

Thanks again!

[Ivan F Moala]

Just as a matter of interest to yourself
the filesearch function holds it search
parameters between calls...you have to reset
this just to clear it and then do a new
search....you can see this when you open a
new workbook and use the Tools > Find, you
will always see the last criteria searched for......so in your code use.

.NewSearch

within the Filesearch

As you will not be using this method to open
a filet here will come a time when you will
need to do a filesearch so keep this in mind.