Create Folders From Excel
 

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.

Create Folders From Excel
 

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


--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"WBTKbeezy" wrote in message ...
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.

Create Folders From Excel
 

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)



"WBTKbeezy" wrote in message
...
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.

Create Folders From Excel
 

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" wrote in message
...
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.

Create Folders From Excel
 

Thanks Ron, simple and effective!

"Ron de Bruin" wrote:

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


--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"WBTKbeezy" wrote in message ...
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.