Try to create a Dir if it does not exist

Access Office 365
Try to create a Dir if it does not exist

Calling a sub called
mkdirtest

strUsername = "zzzzzz"

But this is not creating a directory under that path

Sub mkdirtest()
Dim strFolderPath As String

strFolderPath = Environ("USERPROFILE") & strUsername & "\Access_Export\"

CheckDir (strFolderPath)

End Sub




Function CheckDir(Path As String)
Dim strUsername As String

strUsername = Environ("username")


    If Dir(Path, vbDirectory) = "" Then
        MkDir (Path)
      '  MsgBox "Making Directory!"
        ' copy the file from the desktop
        FileCopy "C:\Users\" & strUsername & "\desktop\Export_Import.xlsx", "C:\Users\" & strUsername & "\Access_Export\Export_Import.xlsx"
    'End If
    Else
    ';    MsgBox "Dir Exists!"
    End If

End Function

Open in new window

Thanks
fordraiders

VBA* Access

Last Comment

Bill Prew

8/22/2022 - Mon

ASKER CERTIFIED SOLUTION

Bill Prew

3/5/2020

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.

View this solution by signing up for a free trial.

Members can start a 7-Day free trial and enjoy unlimited access to the platform.

See Pricing Options

Start Free Trial

GET A PERSONALIZED SOLUTION

Ask your own question & get feedback from real experts

Find out why thousands trust the EE community with their toughest problems.

Fabrice Lambert

3/5/2020

Side note:
Don't use the Dir function, it is buggy.
Instead, use the FileSystem library wich is more reliable and have stronger meaning.

Sample code:

Dim fso As Object    '// Scripting.FileSystemObject
Set fso = CreateObject("Scripting.FileSystemObject")
    '// check if a directory exist
If Not (fso.FolderExists("c:\myfolder")) Then
        '// create a directory
    fso.CreateFolder ("c:\myFolder")
End If

Open in new window

Bill Prew

3/6/2020

And I tend to use a small recursive routine to create folders, something like this example. It takes care of creating any missing parent folders in the path along with the lowest node new folder. Since it is recursive I like to create the filesystem object outside of it and then just pass in the object for its usage.

Sub Main()
    ' Create filesystem object
    Dim FSO As Object
    Set FSO = CreateObject("Scripting.FileSystemObject")
    
    ' Create new folder(s)    
    MakeDir "c:\temp\dir1\dir2\dir3", FSO
End Sub

Sub MakeDir(ByVal DirectoryPath As String, ByVal FSO As Object)
    ' Recursive routine to create a folder and any needed parent folde(s)
    If Not FSO.FolderExists(DirectoryPath) Then
        MakeDir FSO.GetParentFolderName(DirectoryPath), FSO
        FSO.CreateFolder DirectoryPath
    End If
End Sub

Open in new window

»bp

Fordraiders

3/10/2020

ASKER

tHANKS !!

All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat

William Peck

Bill Prew

3/10/2020

Welcome, glad that helped.

»bp