Exit Sub if file/path not found
 

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

--
EMo
-----------------------------------------------------------------------
EMoe's Profile: http://www.excelforum.com/member.php...fo&userid=2318
View this thread: http://www.excelforum.com/showthread.php?threadid=57230

Exit Sub if file/path not found
 

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

--
Regards,
Tom Ogilvy


"EMoe" wrote:


Hello Programmers!!!

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

How do add that if the Path or filename isn't found then do nothing (or
exit the sub). Or maybe for an indication place the Letters "NF" (for
Not Found) in the cell where the number is suppose to go. I don't want
any error messages or boxes to pop up; only quit the macro if the
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,
EMoe


--
EMoe
------------------------------------------------------------------------
EMoe's Profile: http://www.excelforum.com/member.php...o&userid=23183
View this thread: http://www.excelforum.com/showthread...hreadid=572305

Exit Sub if file/path not found
 

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


--
EMoe
------------------------------------------------------------------------
EMoe's Profile: http://www.excelforum.com/member.php...o&userid=23183
View this thread: http://www.excelforum.com/showthread...hreadid=572305

Exit Sub if file/path not found
 

Try sPath & "\" & sFilename

--
HTH

Bob Phillips

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

"EMoe" wrote in message
...

Hello Programmers!!!

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

How do add that if the Path or filename isn't found then do nothing (or
exit the sub). Or maybe for an indication place the Letters "NF" (for
Not Found) in the cell where the number is suppose to go. I don't want
any error messages or boxes to pop up; only quit the macro if the
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,
EMoe


--
EMoe
------------------------------------------------------------------------
EMoe's Profile:
http://www.excelforum.com/member.php...o&userid=23183
View this thread: http://www.excelforum.com/showthread...hreadid=572305

Exit Sub if file/path not found
 

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

--
Regards,
Tom Ogilvy


"EMoe" wrote:


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


--
EMoe
------------------------------------------------------------------------
EMoe's Profile: http://www.excelforum.com/member.php...o&userid=23183
View this thread: http://www.excelforum.com/showthread...hreadid=572305

Exit Sub if file/path not found
 

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
--
3c


"EMoe" wrote:


Hello Programmers!!!

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

How do add that if the Path or filename isn't found then do nothing (or
exit the sub). Or maybe for an indication place the Letters "NF" (for
Not Found) in the cell where the number is suppose to go. I don't want
any error messages or boxes to pop up; only quit the macro if the
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,
EMoe


--
EMoe
------------------------------------------------------------------------
EMoe's Profile: http://www.excelforum.com/member.php...o&userid=23183
View this thread: http://www.excelforum.com/showthread...hreadid=572305