Create Folders From Excel

[WBTKbeezy]

I have some code that saves several copies of a spreadsheet to a specific
folder and it works great. However, if the folder that is specified in the
code doesn't exist, it errors out. Is there a way to have the folders be
created via VB code to circumvent this? I have to create folders on a daily
basis, and this would save some annoyance.

 

[Ron de Bruin]

You can play with this

Sub MakeDirectory()
Dim Dirname As String
Dim fs As Object
Set fs = CreateObject("Scripting.FileSystemObject")
Dirname = "C:\MyDir"
If Not fs.FolderExists(Dirname) Then
fs.CreateFolder Dirname
Else
' do nothing
End If
End Sub

 

[Bob Phillips]

Dim mpDir As String
Dim i As Long
Dim mpDirParts As Variant

mpDir = "C:\test\subdir\subsubdir\bottom"
mpDirParts = Split(mpDir, "\")
mpDir = mpDirParts(LBound(mpDirParts))
For i = LBound(mpDirParts) + 1 To UBound(mpDirParts)

mpDir = mpDir & "\" & mpDirParts(i)
On Error Resume Next
MkDir mpDir
On Error GoTo 0
Next i


--
---
HTH

Bob

__________________________________________
UK Cambridge XL Users Conference 29-30 Nov
http://www.exceluserconference.com/UKEUC.html

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Chip Pearson]

The following code will create the specified folder(s) as required starting
from the root down to the deepest nested folder. You must pass it a fully
qualified path specification (including drive letter).

Sub MakeMultiDir(PathSpec As String)
Dim Arr As Variant
Dim N As Long
Dim S As String
If InStr(1, PathSpec, ":", vbBinaryCompare) = 0 Then
MsgBox "You must use a fully qualified path."
Exit Sub
End If
Arr = Split(PathSpec, "\")
For N = LBound(Arr) To UBound(Arr)
S = S & Arr(N) & "\"
On Error Resume Next
MkDir S
Next N
End Sub

For example,

MakeMultiDir "C:\Test1\Test2\Test3\Test4"

will create
C:\Test1
C:\Test1\Test2
C:\Test1\Test2\Test3
C:\Test1\Test2\Test3\Test4

It does nothing if a folder already exists.

This is a simplification of the code at
http://www.cpearson.com/Excel/MakeDirMulti.htm.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel, 10 Years
Pearson Software Consulting
www.cpearson.com
(email on the web site)

 

[WBTKbeezy]

Thanks Ron, simple and effective!

You can play with this

Sub MakeDirectory()
Dim Dirname As String
Dim fs As Object
Set fs = CreateObject("Scripting.FileSystemObject")
Dirname = "C:\MyDir"
If Not fs.FolderExists(Dirname) Then
fs.CreateFolder Dirname
Else
' do nothing
End If
End Sub