creating or deleting folders within excel VBA

need to create a folders structure within excel VBA
Lau_ForenAsked:
Who is Participating?
 
Bill PrewCommented:
Here's a small function I use for creating folders in VBA, including any needed parent folders.  Including a small test procedure to show it's usage.

Sub Test()
    MakePath ("c:\d1\d2\d3")
End Sub

Function MakePath(p)
    Dim fso As Object
    Set fso = CreateObject("Scripting.FileSystemObject")
    If fso.FolderExists(p) Then
        MakePath = True
    Else
        If MakePath(fso.GetParentFolderName(p)) Then
            fso.CreateFolder (p)
            MakePath = True
        Else
            MakePath = False
        End If
    End If
End Function

Open in new window


»bp
1
 
ITguy565Commented:
This should do what you are looking to accomplish.

'requires reference to Microsoft Scripting Runtime
Sub MakeFolder()

Dim strComp As String, strPart As String, strPath As String

strComp = Range("A1") ' assumes company name in A1
strPart = CleanName(Range("C1")) ' assumes part in C1
strPath = "C:\Images\"

If Not FolderExists(strPath & strComp) Then 
'company doesn't exist, so create full path
    FolderCreate strPath & strComp & "\" & strPart
Else
'company does exist, but does part folder
    If Not FolderExists(strPath & strComp & "\" & strPart) Then
        FolderCreate strPath & strComp & "\" & strPart
    End If
End If

End Sub

Function FolderCreate(ByVal path As String) As Boolean

FolderCreate = True
Dim fso As New FileSystemObject

If Functions.FolderExists(path) Then
    Exit Function
Else
    On Error GoTo DeadInTheWater
    fso.CreateFolder path ' could there be any error with this, like if the path is really screwed up?
    Exit Function
End If

DeadInTheWater:
    MsgBox "A folder could not be created for the following path: " & path & ". Check the path name and try again."
    FolderCreate = False
    Exit Function

End Function

Function FolderExists(ByVal path As String) As Boolean

FolderExists = False
Dim fso As New FileSystemObject

If fso.FolderExists(path) Then FolderExists = True

End Function

Function CleanName(strName as String) as String
'will clean part # name so it can be made into valid folder name
'may need to add more lines to get rid of other characters

    CleanName = Replace(strName, "/","")
    CleanName = Replace(CleanName, "*","")
    etc...

End Function

Open in new window



original source : https://stackoverflow.com/questions/10803834/is-there-a-way-to-create-a-folder-and-sub-folders-in-excel-vba
0
 
Bill PrewCommented:
Deleting is a lot easier, you can just delete the folder, and all sub files and folders are removed at once.

Sub DeleteAFolder(filespec)
   Dim fso
   Set fso = CreateObject("Scripting.FileSystemObject")
   fso.DeleteFolder(filespec)
End Sub

Open in new window


»bp
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
Lau_ForenAuthor Commented:
Thank you!
My problem is how to reference the object you are using ... it does not appear in my version of excel (I am using excel 2016)
0
 
Bill PrewCommented:
My script uses late binding, so no need to add a reference.

An earlier example used early binding, so you would need to add "Windows Script Host Object Model".


»bp
0
 
Lau_ForenAuthor Commented:
Thanks. Now is solved.
0
 
Bill PrewCommented:
Okay, if you don't need any more help on this question could you please accept solution(s) and close it.


»bp
0
 
Lau_ForenAuthor Commented:
I tried, but couldn't find where o how do it...
0
 
Bill PrewCommented:
See if this helps, if not let me know.



»bp
1
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.