Problems Creating Folders With VBA

[Ark68]

I have this code...

            strFile = distpath & mntxt
            If FileExists(strFile) = False Then
                MkDir strFile
            End If
            subpath2 = Format(Day(inq_date), "00") & " " & UCase(daytext)
            MkDir subpath1 & subpath2

mntxt = "August"
distpath = "D:/wsop 2020/Distributables/"
strFile = "D:/wsop 2020/Distributables/August"
subpath2 = "06 THU"

I use a function (FileExists) to check if this path exists, and if it doesn't, then create it. 'distpath' always exists, but if the month folder doesn't exist in it, then create the month (mntxt) folder.

Problem 1:
When 'August" exists in 'distpath', the FileExists function comes back = false and proceeds to create the folder "August" as if it didn't exist. The line in red results in a "Path/File access error." Looking for help in what is causing this error. Is it because the path exists already? If so, what would be causing the function to report the path being nonexistent when it truly exists. If the function returned true, it wouldn't try to recreate the folder.

Problem 2:
I am trying to add a new folder (subpath2) to the month folder. What I have in blue isn't the right approach. Can someone share how to create a new folder in an existing folder?

 

[Yongle]

see View menu in vba editor and display Immediate Window to see output from debug.print
- make the amendments below to your code and run to see what it tells you

    strFile = distpath & mntxt
    Debug.Print "File Path", strFile
    With CreateObject("WScript.Shell")
        Debug.Print "Current Folder", .CurrentDirectory
    End With
    If FileExists(strFile) = False Then
        MkDir strFile
    End If
    subpath2 = Format(Day(inq_date), "00") & " " & UCase(daytext)
    Debug.Print "path2", subpath1 & subpath2
    MkDir subpath1 & subpath2

the current drive must be the SAME drive as the folder being created
- if not, then use this syntax to change the current drive before trying to create the first folder

ChDrive "S"

is subpath2 correct? does it have the a path separator between subpath1 and subpath2?

 

[Ark68]

Hey thanks Yongle ... I still plugging through this but still getting the same error with the line in red.
With your diagnostic hints, I discovered I had to change the drive to the drive that that path needs to be created ... "D"
So now that I have the right drive activated, I'm still unable to create the folder.

            strFile = distpath & mntxt
            Debug.Print "File path ", strFile  'File path D:/WSOP 2020/Distributables/August
            ChDrive "D"
            With CreateObject("WScript.Shell")
                Debug.Print "Current Folder", .CurrentDirectory
            End With
            If FileExists(strFile) = False Then subfolder August does not exist in D:/WSOP 2020/Distributables so create it
                MkDir strFile ' "Path/File access error." 
            End If
            subpath2 = Format(Day(inq_date), "00") & " " & UCase(daytext)
            Debug.Print "path2", subpath1 & subpath2
            MkDir subpath1 & subpath2

Still unsure what I'm doing wrong. My guess is I'm not understanding of the concept enough.

 

[Yongle]

Are you usung the wrong path separator n your strings ?
eg
D:/wsop 2020/Distributables/
should be
D:\wsop 2020\Distributables\"

 

[Ark68]

Hi Yongle and others,
I'm still fighting this issue, even with the changes Yongle you pointed out.

            strFile = distpath & mntxt
            Debug.Print "File path ", strFile
            ChDrive "D"
            With CreateObject("WScript.Shell")
                Debug.Print "Current Folder", .CurrentDirectory
            End With
            If FileExists(strFile) = False Then
                MkDir strFile
            End If
            subpath2 = Format(Day(inq_date), "00") & " " & UCase(daytext)
            Debug.Print "path2", subpath1 & subpath2
            MkDir subpath1 & subpath2

"Path/File access error".

distpath = "D:\WSOP 2020\Distributables\"
mntxt = "August"
strFile = "D:\WSOP 2020\Distributables\August"

I'm sure I'm missing something very basic. I really thought my backwards path separators were the culprit, but doesn't appear the sole culprit.

 

[Ark68]

I've isolated the problem and have been able to get things to work!

The problem was with my functionthat checked whether the file existed or not. The function filename reference varaibale was "FilePath", not "strFile". With changing strFile to FilePath resulted in the correct result for ther function. The error I had been getting was caused by trying to create a directory that already existed. The function failed to identify it already existed.

All good now.

 

[Ark68]

I spoke too soon. The issue is with my function I use to determine if a particular path exists. Note ... I changed some variable names from my OP.

            filePath = distpath & mntxt
            Debug.Print "File path ", filePath
            ChDrive "D"
            'With CreateObject("WScript.Shell")
            '    Debug.Print "Current Folder", .CurrentDirectory
            'End With
            If FileExists(filePath) = False Then
                MkDir filePath
            Else
                MsgBox "folder " & mntxt & " exists."
            End If
            destfold = filePath & "\" & Format(Day(inq_date), "00") & " " & UCase(daytext)
            Debug.Print "destfold", destfold
            MkDir destfold

Whether the directory (filePath) exists or not, the function always seems to be returning 'False' and therefor wants to create the new directory. When that directory already exists, I get the File/Path error. The function is supposed tocheck if the path exists. If it does, there is no need to create the directory.

Function FileExists(filePath As String) As Boolean
    Dim TestStr As String
    TestStr = ""
    On Error Resume Next
    TestStr = Dir(filePath)
    On Error GoTo 0
    If TestStr = "" Then
        FileExists = False
    Else
        FileExists = True
    End If
End Function

 

[Ark68]

I did some more testing by writing a module that I could test different values of strFile for testing the FileExists function.

With strFile = "D:\WSOP 2020\", FileExists = True (path exists!)
With strFile = "D:\WSOP 2020\Distributables\" , FileExists = True (path exists!)
With strFile = "D:\WSOP 2020\Distributables\August" , FileExists = False (path does not exist!) But it really does!
With strFile = "D:\WSOP 2020\Distributables\July" , FileExists = False (path does not exist!) But it really does!
With strFile = "D:\WSOP 2020\Distributables\MrExcel" , FileExists = False (path does not exist!) But it really does!
With strFile = "D:\WSOP 2020\Distributables\August\" , FileExists = False (path does not exist!) But it really does!
With strFile = "D:\WSOP 2020\Distributables\FIELD_COLLECTION.xlsx", FileExists = True (path exists!)

Can I post a new forum topic for this new problem? Or is there a solution as to why it won't identify a folder structure greater than 3 tiers?

 

[Yongle]

Below is a function to test for the existence of a FOLDER and it works regardless of levels

Function FolderExists(aPath) As Boolean
    FolderExists = (Len(Dir(aPath, vbDirectory)) > 0)
End Function

call in the usual way ...

Sub TestFunc()
    MsgBox FolderExists("C:\Test\Folder\SubFolder")
End Sub

 

[Yongle]

Below is a functon to test for the existence of a FILE
fullPath string required: folder path followed by path separator followed by the name of the file including extension

Function FileExists(fullPath As String) As Boolean
    FileExists = (Len(Dir(fullPath)) > 0)
End Function

call in the usual way ...

Sub callFunc()
    MsgBox FileExists("C:\Test\Folder\SubFolder\2020-07-20\Sample1 2020-07-21.xlsx")
End Sub