creating or deleting folders within excel VBA

need to create a folders structure within excel VBA
Lau_ForenAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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 PrewIT / Software Engineering ConsultantCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Bill PrewIT / Software Engineering ConsultantCommented:
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
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook 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 PrewIT / Software Engineering ConsultantCommented:
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 PrewIT / Software Engineering ConsultantCommented:
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 PrewIT / Software Engineering ConsultantCommented:
See if this helps, if not let me know.



»bp
1
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
VBA

From novice to tech pro — start learning today.