We help IT Professionals succeed at work.

VB check if file exists. Macro Excel

mweidner
mweidner asked
on
468 Views
Last Modified: 2013-12-25
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
Comment
Watch Question

GrahamSkanRetired
CERTIFIED EXPERT
Top Expert 2012

Commented:
If Dir$(SourceFile) = "" then
      MsgBox "File not found"
Endif
Retired
CERTIFIED EXPERT
Top Expert 2012
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION
GrahamSkanRetired
CERTIFIED EXPERT
Top Expert 2012

Commented:
I don't know where 'back to top' sprang from in my second comment. It shouldn't be there.
GrahamSkanRetired
CERTIFIED EXPERT
Top Expert 2012

Commented:
Ah it's in the bottom RH corner of each comment. I must have copied it accidentally.

Commented:
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
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.