VBA Create folder and sub folder

[jdluke6545]

I am using this code, which is working perfectly:

Sub MakeFolderAndSave()
    Dim Path As String
    Dim fldr As String
    Dim filename As String
    Path = "C:\Users\JD\Documents\Completed Time Sheets\" & Format(Range("AD5"), "mm-yyyy")
    fldr = Dir(Path, vbDirectory)
        If fldr = "" Then MkDir Path
    filename = "LE " & Range("AK10").Value & " " & Format(Range("AD5").Value, "mm-dd-yyyy") & " - " & Range("A4").Value & " - " & Range("A6").Value
    ThisWorkbook.SaveAs filename:=Path & "\" & filename
End Sub

Which looks to see if the folder "mm-yyyy" exists in "C:\Users\JD\Documents\Completed Time Sheets\" and if not it creates the folder.
Cell AD5 is a date

Now, what I am wanting to do is go a step further and create a folder for the year "yyyy" and then the sub folder "mm-yyyy" for keeping things better organized.
So, I thought I could just edit the PATH line to:

Path = "C:\Users\JD\Documents\Completed Time Sheets\" & Format(Range("AD5"), "yyyy") \ Format(Range("AD5"), "mm-yyyy")

But this results in an error: Run-time error '13': Type mismatch
I want the code to create the folder named for the year "yyyy" if it is not already created, and then create the sub-folder "mm-yyyy" if it is not already created also.
If the folder for year "yyyy" is already created then just create the sub-folder "mm-yyyy".
If both folders, "yyyy" and "mm-yyyy" are already there, then just go to FILENAME line and save the file in the corresponding folder
What am I forgetting here? Or what am i doing wrong?

 

[Jack George]

The backslash character "\" when used without quotes is an operator that is meant for integer division, not as path seperator as one would expect. When dividing a number by another, all its operands will be converted to integers, implicitly.

For example,

Dim result As Variant
result = "123.456" \ 10

The string "123.456" will be implicitly converted to 123 of data type Integer, before divided by 10. If during this conversion (or more technically, "type coercion") VBA fails to convert from one type to another, you get that type mismatch error. For example the following line:

result = "hello" \ 10

will not run fine at runtime.

In your specific case, the backslash should be used as a string, not operator. It should be surrounded with double quotes and concatenated with other strings with ampersand("&") character.

Hope you don't think I'm talking too much.

 

[jdluke6545]

I actually tried that before, putting the backslash in quotes...."\" but still have an error...

 

[jdluke6545]

When i put the backslash in double quotes, "\" I get the error:
Compile error: Expected: end of statement
I get this right after typing the code..

 

[jdluke6545]

I have tried:

Path = "C:\Users\JD\Documents\Completed Time Sheets\" & Format(Range("AD5"), "yyyy") & "\" & Format(Range("AD5"), "mm-yyyy")

and when the code is run I get an error that says "path not found"

 

[saboh12617]

Hello,

Did you make sure the year folder and month folder are created in two steps ? (1 step/folder) You can not create a folder and a sub folder in one step.

As indicated by Jack, your instruction should be like this

Path = "C:\Users\JD\Documents\Completed Time Sheets\" & Format(Range("AD5"), "yyyy") & "\" & Format(Range("AD5"), "mm-yyyy")

 

[jdluke6545]

Hello,

Did you make sure the year folder and month folder are created in two steps ? (1 step/folder) You can not create a folder and a sub folder in one step.

As indicated by Jack, your instruction should be like this

Path = "C:\Users\JD\Documents\Completed Time Sheets\" & Format(Range("AD5"), "yyyy") & "\" & Format(Range("AD5"), "mm-yyyy")

Oh ok,,, so i need a line of code to first create the year folder, and then if the year folder exist move to the next line and create the mm-yyyy folder?
Let me see if i can do that...
Thanks!!

 

[saboh12617]

Exactly, 1 folder at a time. Reusing the code you posted, i think the proposition below should work.

Sub MakeFolderAndSave()
    Dim Path As String
    Dim fldr As String
    Dim filename As String
    ' making the year folder
    Path = "C:\Users\JD\Documents\Completed Time Sheets\" & Format(Range("AD5"), "yyyy")
    fldr = Dir(Path, vbDirectory)
        If fldr = "" Then MkDir Path
    ' making the month sub-folder
    Path = Path & "\" & Format(Range("AD5"), "mm-yyyy")
    fldr = Dir(Path, vbDirectory)
        If fldr = "" Then MkDir Path
   
    filename = "LE " & Range("AK10").Value & " " & Format(Range("AD5").Value, "mm-dd-yyyy") & " - " & Range("A4").Value & " - " & Range("A6").Value
    ThisWorkbook.SaveAs filename:=Path & "\" & filename
End Sub

 

[jdluke6545]

Ok thanks Saboh!
I will give that a try!