Here is another way to do that:

Sub CreateReportsFolder()
Dim RptPath As String
RptPath = Worksheets("SystemVariables").Range("ReportsFolder ").Text
On Error Resume Next
mkdir(rptpath)
On error goto 0
End sub

This assumes that only the right most directory might not exist. - a
limitation on the scripting approach as well.

--
Regards,
Tom Ogilvy


"Damien McBain" wrote:

Peter wrote:

Are there any excel wizards out there who could tell me how to get VBA
to create folders?

This checks to see if a folder exists already, then creates it if it
doesn't. The range called "ReportsFolder" is a cell on a worksheet with a
concatenation formula based on data the user has entered:

Sub CreateReportsFolder()

Dim NewFolder As Object
Dim RptPath As String
RptPath = Worksheets("SystemVariables").Range("ReportsFolder ").Text
Set NewFolder = CreateObject("Scripting.FileSystemObject")

If Not CBool(Len(Dir(RptPath))) Then
NewFolder.CreateFolder (RptPath)

Else

End If

End Sub

I "stole" the logic from VBA help in excel :)