PDA

View Full Version : Solved: File Exists Issue



callen2468
06-07-2007, 08:40 AM
I am having trouble writing a function to check the existence of a file. I searched several forums and a few books and so far have not found a good example of what I am trying to accomplish.
I have a procedure and some functions I use to check the existence of folders and create them if they do not exist. The procedure also creates a new workbook and saves it in one of the new folders. I am having some difficulty directing my FileExists() function to look in the correct folder for the existence of the file. Perhaps someone could take a look at this and point me in the right direction. I am self taught and have been working with VBA only a few years. I have several books and I pull as many examples from the online forums as I can. You have all been a great help and I appreciate it.
My project is too large to post here so I dummied up a workbook and some procedures and functions typical to what I am trying to accomplish.
Thank you in advance.
Chris

lucas
06-07-2007, 09:07 AM
There have been many functions posted in the forum dealing with file or directory exists....search using the term exists....here is one that I found:
http://www.vbaexpress.com/forum/showthread.php?t=11572&highlight=exists

Here is a method I have on file:
Option Explicit
Function wbOrDirExists(PathName As String) As Boolean
'Macro Purpose: Function returns TRUE if the specified file
' or directory exists, false if not.
'File usage : Provide full file path and extension
'Path usage : Provide full file path only (accepts with/without trailing "\")
Dim sTemp As String
Application.Volatile
'Ignore errors to allow for error evaluation
On Error Resume Next
sTemp = GetAttr(PathName)

'Check if error exists and set response appropriately
Select Case Err.Number
Case Is = 0
wbOrDirExists = True
Case Else
wbOrDirExists = False
End Select

'Resume error checking
On Error GoTo 0
End Function
Sub TestIt()
'Macro Purpose: To test the wbOrDirExists function
Dim sPath As String
'Change your directory here
sPath = "C:\Test.xls"
'Test if directory or file exists
If wbOrDirExists(sPath) Then
MsgBox sPath & " exists!"
Else
MsgBox sPath & " does not exist."
End If
End Sub


To use it in a worksheet:

formula for B2 is =wbordirexists(A2)
formula for B3 is =wbordirexists(A3)

callen2468
06-07-2007, 09:35 AM
Thank you for the quick reply. I see my problem was not specifying the full path in the procedure. All works as it should now. I guess I better go back to search school:).
I will mark the thread solved.
Chris