Problems Creating Folders With VBA

Ark68

Well-known Member
Joined
Mar 23, 2004
Messages
3,812
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
I have this code...
Rich (BB 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?
 

Some videos you may like

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.

Yongle

Well-known Member
Joined
Mar 11, 2015
Messages
6,977
Office Version
  1. 365
Platform
  1. Windows
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
Rich (BB code):
    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
VBA Code:
ChDrive "S"

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

Ark68

Well-known Member
Joined
Mar 23, 2004
Messages
3,812
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
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.

Rich (BB code):
            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

Well-known Member
Joined
Mar 11, 2015
Messages
6,977
Office Version
  1. 365
Platform
  1. Windows
Are you usung the wrong path separator n your strings ?
eg
D:/wsop 2020/Distributables/
should be
D:\wsop 2020\Distributables\"
 

Ark68

Well-known Member
Joined
Mar 23, 2004
Messages
3,812
Office Version
  1. 365
  2. 2016
Platform
  1. Windows

ADVERTISEMENT

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

Rich (BB code):
            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

Well-known Member
Joined
Mar 23, 2004
Messages
3,812
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
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

Well-known Member
Joined
Mar 23, 2004
Messages
3,812
Office Version
  1. 365
  2. 2016
Platform
  1. Windows

ADVERTISEMENT

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.

Rich (BB code):
            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.

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

Well-known Member
Joined
Mar 23, 2004
Messages
3,812
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
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

Well-known Member
Joined
Mar 11, 2015
Messages
6,977
Office Version
  1. 365
Platform
  1. Windows
Below is a function to test for the existence of a FOLDER and it works regardless of levels

VBA Code:
Function FolderExists(aPath) As Boolean
    FolderExists = (Len(Dir(aPath, vbDirectory)) > 0)
End Function
call in the usual way ...
VBA Code:
Sub TestFunc()
    MsgBox FolderExists("C:\Test\Folder\SubFolder")
End Sub
 

Yongle

Well-known Member
Joined
Mar 11, 2015
Messages
6,977
Office Version
  1. 365
Platform
  1. Windows
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

VBA Code:
Function FileExists(fullPath As String) As Boolean
    FileExists = (Len(Dir(fullPath)) > 0)
End Function
call in the usual way ...
Rich (BB code):
Sub callFunc()
    MsgBox FileExists("C:\Test\Folder\SubFolder\2020-07-20\Sample1 2020-07-21.xlsx")
End Sub
 

Watch MrExcel Video

Forum statistics

Threads
1,122,518
Messages
5,596,621
Members
414,082
Latest member
sasmita

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top