• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 447
  • Last Modified:

VB check if file exists. Macro Excel

How do I add if exists to this code?  If the source file is not found there should be a msgbox stating "not found"  or else it should complete.

Sub Filerename2()
Dim SourceFile, DestinationFile
SourceFile = "myfile.txt"   ' Define source file name.
x = InStr(1, SourceFile, ".")
TempStr1 = Left(SourceFile, x - 1)
TempStr2 = Right(SourceFile, Len(SourceFile) - x + 1)
NewName = TempStr1 & Format(Now, "ddmmyyyyhhmm") & TempStr2
SourceFile = "C:\pathoflogfile\" & SourceFile
DestinationFile = "R:\yourpathnamehere\" & NewName
Name SourceFile As DestinationFile
0
mweidner
Asked:
mweidner
  • 4
1 Solution
 
GrahamSkanCommented:
If Dir$(SourceFile) = "" then
      MsgBox "File not found"
Endif
0
 
GrahamSkanCommented:
Note that this applies to your second definition of SourceFile. It needs to define the full path

Sub Filerename2()
Dim SourceFile, DestinationFile
SourceFile = "myfile.txt"   ' Define source file name.
x = InStr(1, SourceFile, ".")
TempStr1 = Left(SourceFile, x - 1)
TempStr2 = Right(SourceFile, Len(SourceFile) - x + 1)
NewName = TempStr1 & Format(Now, "ddmmyyyyhhmm") & TempStr2
SourceFile = "C:\pathoflogfile\" & SourceFile
If Dir$(SourceFile) = "" then
      MsgBox "File not found"
     exit sub
Endif
back to top
DestinationFile = "R:\yourpathnamehere\" & NewName
Name SourceFile As DestinationFile
0
 
GrahamSkanCommented:
I don't know where 'back to top' sprang from in my second comment. It shouldn't be there.
0
 
GrahamSkanCommented:
Ah it's in the bottom RH corner of each comment. I must have copied it accidentally.
0
 
peetmCommented:
A minor point, but if there's *any chance* this file might exist, but be hidden etc, then you'll need to alter the Dir$ call GrahamSkan gave you, e.g.,

if Dir$(sourceFile, 31) = "" then MsgBox "Not Found"

You could alternatively use the FileSystemObject

E.g.,

set o = CreateObject("Scripting.FileSystemObject)

if Not o.FileExists(sourceFile) then

   MsgBox "Doesn't exist"

endif
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now