Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Formula too long - new file path is shorter than old file path - Excel 2003 | Excel Worksheet Functions | |||
Path not found error | Excel Programming | |||
Path Not Found | Excel Programming | |||
If no FIND value is found, then a Msgbox to say so and exit sub | Excel Programming | |||
Which Path not found? | Excel Programming |