Exit Sub if file/path not found

[EMoe]

Hello Programmers!!!

I have a code (posted Below) that finds a file by Path & Name, an
retrieves a number from a specific cell, then places it in the ope
workbook named results.

How do add that if the Path or filename isn't found then do nothing (o
exit the sub). Or maybe for an indication place the Letters "NF" (fo
Not Found) in the cell where the number is suppose to go. I don't wan
any error messages or boxes to pop up; only quit the macro if th
file/path name(s) are incorrect.

CODE

Public Sub GetValue()

Application.ScreenUpdating = False
Dim sFileName As String
Dim sPath As String

sPath = Range("AE4").Value
sFileName = Range("AE6").Value

If Not Right(sFileName, 4) <> ".xls" Then
sFileName = sFileName & ".xls"
End If

Workbooks.Open Filename:=sPath & sFileName

Sheets("Auto Place Sheet").Activate
Range("B371").Copy
Windows("Results.xls").Activate
Range("M29").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone
SkipBlanks _
:=False, Transpose:=False
Windows("Results.xls").ActivatePrevious
Application.CutCopyMode = False
ActiveWorkbook.Close
Application.ScreenUpdating = True

End Sub

Thanks,
EMo

 

[Tom Ogilvy]

if dir(sPath & sFilename) = "" then exit sub

 

[EMoe]

Sorry Tom;

I placed that line of code after...

sFileName = Range("AE6").Value

and the sub was exited. My file name & path is correct.

Where should I place it?

EMoe

 

[Bob Phillips]

Try sPath & "\" & sFilename

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

 

[Tom Ogilvy]

and after that line, you have code that completes sFilename by adding the
extension.

so put it just before the workbook.open command. (where both will use the
same string)

if workbook.open will work, then that line should work

 

[CarloC]

Use a FileSystemObject to test the validity of the Path/Filename...

Dim objFSO as Object

Set objFSO = CreateObject("Scripting.FileSystemObject")

If objFSO.FileExists(sPath & sFileName) then

...open the workbook here and finish off your code.

Else:

...add code here if file does not exist.

End If

End Sub