if file does not exist then exit VB

[asylum]

Hi,

I have a peice of code which opens up to ten work books and manipulates data.

What i need is a piece of code to put at the start which will check for the existance of each file BEFORE the rest of the code executes, if it does not exist it need to error message and exit the code at that point.

the files will all be called read 1.xls, read 2.xls etc and the 1,2, part is already stored as a variable called readno.

any good ideas? tahnks Andy

 

[Richard Schollar]

Hi Andy

You can use Dir to test for the existence of a file:

If Len(Dir("C:\Somefolder\SomeFile.xls"))>0 Then
  'file exists - proceed accordingly
Else
  'file doesn't exist - take appropriate action
End If

So in your example it might be something like:

If Len(Dir("C:\Folder\" & readno & ".xls"))>0 Then
  Workbooks.Open "C:\Folder\" & readno & ".xls"
Else
  Exit Sub
End If

 

[asylum]

Thanks, I have this now, but it errors as 'next without for' ??


Sub check()

For loopno = 1 To readno
If Len(Dir(strPath & "\read " & loopno & ".xls")) > 0 Then
Next loopno
Else
Exit Sub
End If

End Sub

 

[Richard Schollar]

Next in the wrong place:

Sub check()

For loopno = 1 To readno
If Len(Dir(strPath & "\read " & loopno & ".xls")) > 0 Then

Else
Exit Sub
End If
Next loopno
End Sub