MkDir Variable Failing

Question

I am trying to build a workbook that functions as a tool for different users to input client data and I have a function that checks to see if a path exists, and if it doesnt, it will create it. However, when it gets to the MkDir command in the code it give me a run time '76' error.

I think this might have to do with me incorrectly using the Environ variable. Could someone look at this and tell me what i've done to break this? - This is solved below - but the Mkdir part is still broken

    Dim a As Range
    Dim StartingWS As Worksheet
    Dim ClientFolder As String
    Dim ClientCusip
    Dim ExportFile As String
    Dim PreparedDate As String
    Dim Exports As String
    Dim AccountNumber As String
    Dim LR As Long
    Dim NumOfBars As Integer
    Dim PresentStatus As Integer
    Dim PercetageCompleted As Integer
    Dim k As Long
    Dim sFolderPath As String
    Dim oFSO As Object
    Dim FindFolder As Object
    Dim FindCAFolder As Object
    Dim SCAFolderPath As String
    Dim UserName As String
        
  UserName = Environ("username")

     
        Set StartingWS = ThisWorkbook.Sheets("Starting Page")
    
    '******************* This code Creates the Class Action Folder ************
    
     
    Set FindCAFolder = CreateObject("Scripting.FileSystemObject")
    SCAFolderPath = "C:\Users\" & UserName & "\Desktop\Class Actions\"
    If FindCAFolder.FolderExists(SCAFolderPath) Then
    Else
       MkDir SCAFolderPath
    End If
    
    
    
    '************* This code creates the folder and sets the export path for the individual spreadsheets**********
    
    ClientCusip = ActiveWorkbook.Worksheets("Starting Page").Range("I11").Value
    ClientFolder = ActiveWorkbook.Worksheets("Starting Page").Range("I10").Value
    PreparedDate = Format(Now, "mm.yyyy")
    Set FindFolder = CreateObject("Scripting.FileSystemObject")
    sFolderPath = "C:\Users\" & UserName & "\Desktop\Class Actions\" & ClientFolder & " - " & PreparedDate & "\"
    If FindFolder.FolderExists(sFolderPath) Then
    Else
       MkDir sFolderPath
    End If

Answer 1

I ended up finding a different path that solved this for me:

Dim oWSHShell As Object

Set oWSHShell = CreateObject("WScript.Shell")
GetDesktop = oWSHShell.SpecialFolders("Desktop")
Set oWSHShell = Nothing



Set FindFolder = CreateObject("Scripting.FileSystemObject")
sFolderPath = GetDesktop & "\Class Actions\"
If FindFolder.FolderExists(sFolderPath) Then
Else
   MkDir sFolderPath
End If

To be fair, I think Red Hare is correct as well, but there is some kind of corruption in my user profile that made this not work for me. If you have this problem I hope one of these solutions works.

Answer 2

You're missing the base path that should include the drive letter and user folder

Try this line instead, but change the drive letter and folder accordingly to your system.

sFolderPath = "C:\Users\" & UserName & "\Desktop\Class Actions\" & ClientFolder & " - " & PreparedDate & "\"

Answer 3

Instead of MKDir you should anyway use MakeSureDirectoryPathExists This will create the directory if it not exists

Option Explicit
Private Declare PtrSafe Function MakeSureDirectoryPathExists Lib "imagehlp.dll" (ByVal lpPath As String) As Long
Sub TestIt()
Dim ok As Long
ok = MakeSureDirectoryPathExists("E:\zTemp\") ' Do your folder path here
If ok <> 0 Then
MsgBox "Hurray"
End If
End Sub