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

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

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


--
krabople
------------------------------------------------------------------------
krabople's Profile: http://www.excelforum.com/member.php...o&userid=29774
View this thread: http://www.excelforum.com/showthread...hreadid=504845

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

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

Brilliant that's done the job, thanks again


--
krabople
------------------------------------------------------------------------
krabople's Profile: http://www.excelforum.com/member.php...o&userid=29774
View this thread: http://www.excelforum.com/showthread...hreadid=504845