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

Thanks,

Peter

Check VBA's help for mkdir

Peter wrote:

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

Thanks,

Peter

--

Dave Peterson

On Mar 20, 8:51 am, "Peter" wrote:
Are there any excel wizards out there who could tell me how to get VBA
to create folders?

Thanks,

Peter

Is the file path fixed or will it change frequently? Creating the
directory isn't so hard:
If Dir(Path, vbDirectory) = "" Then MkDir (Path)

However, if you need to build the path, you could probably modify the
following code to do what you want:

Private Sub MakePath()
Dim Drive As Long
Dim Path As String, Per As String, Bus As String
Dim Store As String, NewFile As String

' these lines define the many variable portions of the folder path and/
or filename
Path = "\\Server\Drive\Folder"
Store = Format(Sheets("Sheet1").Range("E13").Value, "000")
' change the 000 to whatever format you want
Bus = Sheets("Sheet1").Range("J15").Value
Per = Sheets("Sheet1").Range("i5").Value
NewFile = Path & "pd" & Per & "\" & "Store Forecast_" & Bus &
Store & "_" & "pd" & Per & ".xls"

' check to see if Path exists, and creates it if it doesn't...
If Dir(Path & Per, vbDirectory) = "" Then MkDir (Path & Per)

'save the file to the created directory
ActiveWorkbook.SaveAs Filename:=NewFile, FileFormat:=xlNormal

hope this helps!
Ray

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 :)

On 20 Mar, 13:55, 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 :)

Thanks guys, that's great!

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 :)