check for directory, or create new if not found

amerk20 · April 8th, 2005, 10:41 AM

I'm using the transferspreadsheet function in VBA (Access 2003) to create .xls files of several queries. I would like these "output" files to be saved to a directory with the path of "C:\IPDC Files". My dilemma is how do I use VBA to check to see if this directory exists first... and if NOT, then go ahead and create it?

Any help is appreciated!!

Rick

BrianWren · April 8th, 2005, 12:18 PM

Try changing to the directory, and if that generates an error create it.

Code:

' ChDrive Drive As String
' ChDir Path As String

Public Sub DoIt()

    On Error GoTo Er

    Dim PthNotExist As Boolean  ' A Booleanâs initial value = False

    If Left$(CurDir, 2) <> "C:" Then
        ChDrive "C:"
    End If

    ChDir "C:\IPDC Files"       ' If the path does not exist, the error haldler
                                ' will be invoked with error #76.
    If PthNotExist Then         ' This will have been set in the error handler
        MkDir "C:\IPDC Files"
    End If

    PthNotExist = False         ' Re-initialize the value, if it is not going
                                ' to be used to take additional action.
Rs: Exit Sub

Er: If Err.Number = 76 Then     ' Path Not Found
        PthNotExist = True
        Resume Next
    End If

    MsgBox "Error " & Err.Number & ", """ & Err.Description & """"
    Resume Rs

End Sub