Results 1 to 6 of 6
  1. #1
    Join Date
    Feb 2013
    Posts
    99

    Unanswered: Creating a folder through VBA in Access 2010

    I am trying to create a folder if it does not exist in VBA in access.

    I am trying to create the following

    "c:\test" & "\" & Format(Date, "yyyy") & "\" & Form!Campus & "\" & Form!RptType & "\" & Format(Date, "mmm") & "\"

    I can copy to the folder is already created but not create it through VBA

    I have tried the MKdir method and the FSO method but neither will create the folders.

    I have tried breaking it down creating the "c:\test" First then the Format date folder, but when it gets to the making of the campus folder it fails.

    Anyone got any ideas

    Thank you

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    My guess would be that the campus attribute contains one or more characters which are invalid in a folder name. The obvious culprits would be a colon or a backslash.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  3. #3
    Join Date
    Feb 2013
    Posts
    99
    Have a look and cannot see an issue this is the basic script

    Dim Mypath

    Mypath = "c:\test" & "\" & Format(Date, "yyyy") & "\" & Form!Campus & "\" & Form!RptType & "\" & Format(Date, "mmm") & "\"

    Mkdir Mypath

    Get the run tome error 76 path not found

    When looking at Mypath the following is shown c:\test\2014\incident report\feb\

    So I would have thought it should be created

    Thank you

  4. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    You need to embed quotation marks around your pathname because the pathname contains a space. I'd use:
    Code:
    Mypath = Chr(34) & "c:\test" & "\" & Format(Date, "yyyy") & "\" ;
    &  Form!Campus & "\" & Form!RptType & "\" ;
    &  Format(Date, "mmm") & "\" & Chr(34)
    Note that this WILL cause problems when you append to the path (like with a file name) because the quotes won't match.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  5. #5
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    id suggest removing the space in incident report and see if that makes a difference. I don'#t know that is the problem but Access itself dates back to 1990's and its possible that it doens't like spaces in file names
    I'd rather be riding on the Tiger 800 or the Norton

  6. #6
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Spaces in the folder name does not matter, you must create every subfolder step by step, like this:
    Code:
    Private Sub Command_CreateFolder_Click()
        
        Const c_Path As String = "c:\test\@Y\@C\@R\@M"
        
        Dim strFolderName As String
        Dim strPath As String
        Dim var As Variant
        Dim i As Long
        
        If Len(Nz(Me.Campus.Value, "")) = 0 Then
            MsgBox "Campus cannot be empty!", vbInformation, "Missing value"
        ElseIf Len(Nz(Me.RptType.Value, "")) = 0 Then
            MsgBox "Report type cannot be empty!", vbInformation, "Missing value"
        Else
            strFolderName = Replace(Replace(Replace(Replace(c_Path, "@Y", Year(Date)), _
                                                                    "@C", Me.Campus.Value), _
                                                                    "@R", Me.RptType.Value), _
                                                                    "@M", Format(Date, "mmm"))
            If InStr(strFolderName, ":") > 2 Then
                MsgBox "Invalid character in the folder name!", vbInformation, "Invalid character"
            Else
                var = Split(strFolderName, "\")
                For i = 0 To UBound(var)
                    strPath = strPath & var(i)
                    strPath = strPath & "\"
                    If Len(Dir(strPath, vbDirectory)) = 0 Then MkDir strPath
                Next i
            End If
        End If
        
    End Sub
    Have a nice day!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •