Sub DoesFileExist()
Dim sPath As String
Dim sMyFile As String

sPath = "C:\Documents and Settings\My Docs\"
sMyFile = "ABC123999.xls"

If Dir(sPath & MyFile) = "" Then
MsgBox "File NOT FOUND"
Else
MsgBox "File EXISTS"
End If

End Sub


"HeatherO" wrote:

Thanks actually you helped alot. I just have one other question if I am
opening those files the ABC123999 or the DEF123999 I was just wondering if
there is anyway I can check before I open them to make sure they are there so
that I don't get an error when I try to open a workbook that is not there? I
am supposed to go under the assumption that they exist but I am trying to
capture the human error which is bound to be there.
Thanks in advance for all your help.


"HeatherO" wrote:

I am currently trying to create a macro that has a prompt for people to enter
values. Based on these values the macro will then open 2 workbooks with
those values plus 2 different prefixes (ie. ABC123999 and DEF123999). In
another workbook I am looking up based on the one column in this workbook if
it has ABC or DEF I am trying to do a vlookup to the appropriate workbook
and then return the value of the 7th column in either the ABC or DEF workbook
to the N column of the original workbook.
I know I can do this in Excel but when I try to create the code in VBA I
am just not getting it. This is what I have so far:
Workbooks("Test").Worksheets("Sheet1").Activate
With ActiveSheet
.Range("N2:N55").Value =

If .Range("L2:L55") = "ABC" Then
VLOOKUP(B2,workbooks("ABC123999").worksheets("SMAR T")
$A$2:$H$H82,7,FALSE),0)
Any help would be gratefully appreciated.