Create a folder structure with VBA

[VbaHell]

Hello all

This piece of code checks to see if a folder with the value "MyContact" exists and if not creates a folder on my S Drive with the folder with the value of "MyContact"
It then saves the active sheet in this folder with the value from "MyFileName and "Mydate"

sDir = "S:\Records\test\Customers\" & MyContact
If Len(Dir(sDir, vbDirectory)) = 0 Then
    MkDir sDir
End If
    ActiveSheet.SaveAs Filename:=sDir & "\" & MyFileName & " " & MyDate & ".xlsx"

What I want is another two levels of folders that the code can check to see if they are there and if not then create them.

IE

Folder one = MyContact
Inside this folder = MyFileName
Inside this folder = AssetName

All values of the above folders

Could any person please help me on this as I have tried various versions on the above code but can't get it to work correctly

 

[VbaHell]

Create Folder Bugging out

Hello all

Can an excel guru please see if there is an error in my coding

I am trying to check for a folder path and if not there then create the folders.

It's keeps bugging out on "

MkDir sDir"​

Sub SaveGRV()
Dim MyCustomer As String
Dim MyDate As String
Dim MySerialName As String
Dim sDir As String
ActiveSheet.Move
ActiveSheet.Select
Range("A13").Select

On Error GoTo IssueSaving
MyCustomer = ActiveSheet.Range("D21").Text
MySerialName = ActiveSheet.Range("H31").Text
MyDate = ActiveSheet.Range("D20").Date



sDir = "S:\Records\Processing Records\Calibration - GRV\Customers\" & MyCustomer & "\" & MySerialName & "\" & MySerialName & "-" & MyDate


If Len(Dir(sDir, vbDirectory)) = 0 Then
    MkDir sDir
End If
    ActiveSheet.SaveAs Filename:=sDir & "\" & MySerialName & ".xlsx"
Exit Sub


IssueSaving:
        MsgBox "GRV Form failed to save."
        Exit Sub

        Application.DisplayAlerts = True


End Sub

 

[Norie]

Re: Create Folder Bugging out

How is it 'bugging out'?

 

[dmt32]

Re: Create Folder Bugging out

Hi,
If the path you want to create is a complex directory structure, the high-level directories must already exist or the MKDIR statement will raise an error.

Dave

 

[Fluff]

Re: Create Folder Bugging out

Please do not post the same question multiple times. All clarifications, follow-ups, and bumps should be posted back to the original thread.
Per forum rules, posts of a duplicate nature will be locked or deleted (rule 12 here: Forum Rules).

I have merged both threads

 

[VbaHell]

Re: Create Folder Bugging out

Thanks for the replies and sorry about this duplicate posting (Dam i got caught)

dmt32 I took your advice and changed the code to create the three folder structures separately before saving and this now works.

I still have a random bug with Excel where it says "There isn't enough memory to complete this action" and then closes Excel down, but I will search the internet to see if I can find out what this issue is because it's a small file and would not use up memory.

Thanks again

 

[VbaHell]

Re: Create Folder Bugging out

Just in case other members have the same issue with the above errors there are various fixes on the web to resolve this.
My issue was because I had a small Object on the sheet to activate a macro, I removed this and created a form button instead and it now works.