We help IT Professionals succeed at work.

Try to create a Dir  if it does not exist

Fordraiders
Fordraiders asked
on
High Priority
35 Views
Last Modified: 2020-03-10
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
Comment
Watch Question

Test your restores, not your backups...
CERTIFIED EXPERT
Expert of the Year 2019
Top Expert 2016
Commented:
The USERPROFILE environment variable already includes the username, like:

C:\Users\user1

So when you are doing this:

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

You are trying to create a folder path of

C:\Users\user1user1\Access_Export\

and since MKDIR only creates the last directory in the path, since C:\Users\user1user1\ doesn't already exist (becasue of the user id duplication) the MKDIR fails.

I think you want to change that to:

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


»bp
Fabrice LambertConsulting
CERTIFIED EXPERT
Distinguished Expert 2017

Commented:
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
Bill PrewTest your restores, not your backups...
CERTIFIED EXPERT
Expert of the Year 2019
Top Expert 2016

Commented:
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

Author

Commented:
tHANKS !!
Bill PrewTest your restores, not your backups...
CERTIFIED EXPERT
Expert of the Year 2019
Top Expert 2016

Commented:
Welcome, glad that helped.


»bp