Make new folder & save to it

[krabople]

Hi, I was wondering if anybody knows a way, through VBA, to create a new folder in the same directory and then save the active workbook to that new folder?

Thanks in advance

[Martin Fishlock]

Dear Krabople:

Here are a couple of routines that may help. Modofy as necessary.

Sub MakeFolderAndSaveActiveBook()
Dim foldername As String
Dim filename As String
Dim s As String
Dim wb As Workbook
foldername = "ggg" ' modify for folder name
filename = "filename.xls"
s = CreateFolder(foldername)
If Len(s) > 0 Then
MsgBox s, vbOKOnly, "Created"
s = s & "\" & filename
ActiveWorkbook.SaveAs s
MsgBox s, vbOKOnly, "Created"
Else
MsgBox "Cannot Create Folder " & foldername, vbOKOnly, "Cannot
create folder"
End If
End Sub

Function CreateFolder(folder As String) As String
Dim p As Variant
On Error GoTo Err
CreateFolder = ""
p = CurDir
If Len(p) > 0 Then
If Right(p, 1) <> "\" Then p = p & "\"
p = p & folder
MkDir p
CreateFolder = p
End If
Err:
End Function

--
HTHs Martin


"krabople" wrote:

>
> Hi, I was wondering if anybody knows a way, through VBA, to create a new
> folder in the same directory and then save the active workbook to that
> new folder?
>
> Thanks in advance
>
>
> --
> krabople
> ------------------------------------------------------------------------
> krabople's Profile: http://www.excelforum.com/member.php...o&userid=29774
> View this thread: http://www.excelforum.com/showthread...hreadid=504845
>
>

[krabople]

That's perfect, thanks very much for your help!

[krabople]

Hi, thanks again for the code, it works great. The only problem is that it creates the folder in My Documents for some reason whereas I need the folder to be in the same directory as the active workbook. How do I do this?

[Martin Fishlock]

Try this:

Function CreateFolder(folder As String) As String
Dim p As Variant
On Error GoTo Err
CreateFolder = ""
p = ActiveWorkbook.Path ' CurDir of active workbook<<<<<<<<<<<<
If Len(p) > 0 Then
If Right(p, 1) <> "\" Then p = p & "\"
p = p & folder
MkDir p
CreateFolder = p
End If
Err:
End Function


--
HTHs Martin


"krabople" wrote:

>
> Hi, thanks again for the code, it works great. The only problem is that
> it creates the folder in My Documents for some reason whereas I need
> the folder to be in the same directory as the active workbook. How do I
> do this?
>
>
> --
> krabople
> ------------------------------------------------------------------------
> krabople's Profile: http://www.excelforum.com/member.php...o&userid=29774
> View this thread: http://www.excelforum.com/showthread...hreadid=504845
>
>

[krabople]

Brilliant that's done the job, thanks again